Why foreign key is not created when defined at column level in MySQL?-CodePudding

I created a parent Table named 'Student' having two columns as 'rollno' and 'name' using the following SQL command:

create table Student ( rollno int primary key, name char(30) );

I defined primary constraint at the column level and the primary key got created. However, when I created child table 'Score' containing two columns as 'rollno' and 'marks' using the following SQL command:

create table Score ( rollno int references student(rollno), marks int );

Foreign key was not created (when defined at the column level). I didn't get any error too. If I apply foreign key constraint at the table level then the foreign key gets created.

So, my doubt is why foreign key is not created when defined at the column level while the primary key is created when defined at the column level?

I am using latest version of MySQL.

CodePudding user response：

This is a feature request in MySQL that dates back to 2004, but it has never been implemented. https://bugs.mysql.com/bug.php?id=4919

The workaround is to use table-level foreign key constraint syntax, even if your case would allow a column-level constraint because it's only for a single column.

create table Score ( 
 rollno int, 
 marks int,
 foreign key(rollno) references student(rollno) 
);

The reason for this missing feature may be unsatisfying, but here it is, from the architect of InnoDB:

MySQL does not give a syntax error, because it was the intention in the 1990's that table definitions containing FOREIGN KEY constraints should be importable to MySQL, even though MySQL at that time did not support foreign keys.

For other storage engines that don't support foreign keys at all (e.g. MyISAM), it is also the case that the foreign key syntax is accepted but ignored. No error or warning is returned, but the foreign key constraint is not saved.