CODEWITHSUNDEEP
mysqlreferenceforeign-keysmany-to-manyforeign-key-relationship
Pankaj Dwivedi
Pankaj Dwivedi

Reputation: 463

Use of REFERENCES keyword in MySQL

I have the following table definition in MySQL. It represents M:N relationship between the tables Licenses and License Server.

create table if not exists  served_license
(
    served_license_id       smallint            not null,
    version                 varchar(16)         not null,
    quantity                smallint            not null,
    expiry_date             char(16)            not null,
    license_server_id       tinyint             not null    references  license_server(license_server_id),
    license_id              varchar(64)         not null    references  license(feature, product),
    primary key (served_license_id)
);

Is this the correct usage of the keyword REFERENCES? If I change the table definition to the one below, will the columns behave differently?

create table if not exists  served_license
(
    served_license_id       smallint            not null,
    version                 varchar(16)         not null,
    quantity                smallint            not null,
    expiry_date             char(16)            not null,
    license_server_id       tinyint             not null,
    license_id              varchar(64)         not null,
    primary key (served_license_id),
    foreign key (license_server_id) references  license_server(license_server_id),
    foreign key (license_id) references license(feature, product)
);

Upvotes: 2

Views: 1507

Answers (1)

Barmar
Barmar

Reputation: 781726

MySQL only processes REFERENCES clauses when they're part of a FOREIGN KEY specification. Here's the quote from the manual:

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.

So the first version will not enforce the foreign key relationships, the second version will (assuming you're using the InnoDB storage engine).

Upvotes: 4

Related Questions