Do you still need to define foreign key constraint if you have already added a reference constraint-CodePudding

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:

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

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?

CodePudding user response：

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

CodePudding user response：

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.