Question 113138 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Do you still need to define foreign key constraint if you have already added a reference constraint to the column definition?

I am trying to understand the syntaxes for defining tables and I noticed that the column definitions include an option to indiciate that the column references a column from another table.

If I can already define this here, do I still need to explicitly define a FOREIGN KEY constraint specifying that column again? Why?

Because I imagine the REFERENCE definition added as a column constraint should already take care of the fact that the column is a foreign key (since it is referencing another table).

Example code for clarity:

a)

create table SAMPLE (
sample_id INT PRIMARY KEY,
client_id INT REFERENCES CLIENT (client_id)
);

b)

create table SAMPLE (
sample_id INT PRIMARY KEY,
client_id INT NOT NULL,
CONSTRAINT fk_sample_client
   FOREIGN KEY (client_id) REFERENCES CLIENT (client_id)
);

Does definition (a) ensure that the clientId is identified as the foreign key, the same way definition (b) does?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    As Simon shared , inline REFERENCES in column definitions are ignored by mySQL and this link gives further explanation as to why.

    Defining a column to use a REFERENCES tbl_name(col_name) clause has no actual effect and serves only as a memo or comment to you that the column which you are currently defining is intended to refer to a column in another table.

    Simply put, the syntax will still only create the column; it will not specify it as a foreign key or carry out any checks on it.


  2. 0

    REFERENCES as part of the column definition is ignored.

    Important

    For users familiar with the ANSI/ISO SQL Standard, please
    note that no storage engine, including InnoDB, recognizes or enforces
    the MATCH clause used in referential integrity constraint definitions.
    Use of an explicit MATCH clause does not have the specified effect,
    and also causes ON DELETE and ON UPDATE clauses to be ignored. For
    these reasons, specifying MATCH should be avoided.

    The MATCH clause in the SQL standard controls how NULL values in a
    composite (multiple-column) foreign key are handled when comparing to
    a primary key. InnoDB essentially implements the semantics defined by
    MATCH SIMPLE, which permit a foreign key to be all or partially NULL.
    In that case, the (child table) row containing such a foreign key is
    permitted to be inserted, and does not match any row in the referenced
    (parent) table. It is possible to implement other semantics using
    triggers.

    Additionally, MySQL requires that the referenced columns be indexed
    for performance. However, InnoDB does not enforce any requirement that
    the referenced columns be declared UNIQUE or NOT NULL. The handling of
    foreign key references to nonunique keys or keys that contain NULL
    values is not well defined for operations such as UPDATE or DELETE
    CASCADE. You are advised to use foreign keys that reference only keys
    that are both UNIQUE (or PRIMARY) and NOT NULL.

    MySQL parses but ignores “inline REFERENCES specifications” (as
    defined in the SQL standard) where the references are defined as part
    of the column specification. MySQL accepts REFERENCES clauses only
    when specified as part of a separate FOREIGN KEY specification. For
    more information, see Section 1.7.2.3, “FOREIGN KEY Constraint
    Differences”.

    Source: https://dev.mysql.com/doc/refman/8.0/en/create-table.html

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search