Foreign Key Syntax

Question

I have created Teacher table first and its description is

   mysql> desc teacher;
   +--------------+-------------+------+-----+---------+-------+
   | Field        | Type        | Null | Key | Default | Extra |
   +--------------+-------------+------+-----+---------+-------+
   | TEACHER_ID   | varchar(3)  | NO   | PRI | NULL    |       |
   | TEACHER_NAME | varchar(40) | YES  |     | NULL    |       |
   +--------------+-------------+------+-----+---------+-------+

After i have created a new table as following .My need is that in the following table i want to use TEACHER_ID as foreign key.I have written the following.I dnt know whether it is wrong means syntactically.But i didn't get any error...

 mysql> CREATE TABLE SUB_MCA_TY
->  (SUB_ID VARCHAR(4) PRIMARY KEY,
->  SUB_NAME VARCHAR(30),
-> TEACHER_ID VARCHAR(3) REFERENCES TEACHER(TEACHER_ID));

Answer 1

"Inline" foreign keys are always silently ignored by MySQL, even with InnoDB (welcome to the wonderful consistent world of MySQL...)

You need to change it into an explicit constraint after the columns:

CREATE TABLE SUB_MCA_TY
(
   SUB_ID VARCHAR(4) PRIMARY KEY,
   SUB_NAME VARCHAR(30),
   TEACHER_ID VARCHAR(3),
   foreign key (TEACHER_ID) REFERENCES TEACHER(TEACHER_ID)
);

Quote from the manual:

MySQL does not recognize or support “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification