[ACCEPTED]-Foreign keys in MySQL?-foreign-keys

Assuming your categories and users table 3 already exist and contain cID and uID respectively 2 as primary keys, this should work:

CREATE TABLE `posts` (
`pID` bigint(20) NOT NULL auto_increment,
`content` text NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`uID` bigint(20) NOT NULL,
`wikiptr` bigint(20) default NULL,
`cID` bigint(20) NOT NULL,
PRIMARY KEY  (`pID`),
Foreign Key(`cID`) references categories(`cID`),
Foreign Key(`uID`) references users(`uID`)
) ENGINE=InnoDB;

The column 1 name is required in the references clause.

Edited: Robert and Vinko state that you need to 22 declare the name of the referenced column 21 in the foreign key constraint. This is 20 necessary in InnoDB, although in standard 19 SQL you're permitted to omit the referenced 18 column name if it's the same name in the 17 parent table.

One idiosyncrasy I've encountered 16 in MySQL is that foreign key declaration 15 will fail silently in several circumstances:

• Your MySQL installation doesn't include the innodb engine
• Your MySQL config file doesn't enable the innodb engine
• You don't declare your table with the ENGINE=InnoDB table modifier
• The foreign key column isn't exactly the same data type as the primary key column in the referenced table

Unfortunately, MySQL 14 gives no message that it has failed to create 13 the foreign key constraint. It simply ignores 12 the request, and creates the table without 11 the foreign key (if you SHOW CREATE TABLE 10 posts, you may see no foreign key declaration). I've 9 always thought this is a bad feature of 8 MySQL!

Tip: the integer argument for integer 7 data types (e.g. BIGINT(20)) is not necessary. It 6 has nothing to do with the storage size 5 or range of the column. BIGINT is always 4 the same size regardless of the argument 3 you give it. The number refers to how many 2 digits MySQL will pad the column if you 1 use the ZEROFILL column modifier.

This has some code showing how to create foreign 2 keys by themselves, and in CREATE TABLE.

Here's 1 one of the simpler examples from that:

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
    id INT, 
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id) REFERENCES parent(id)
    ON DELETE CASCADE
) ENGINE=INNODB;

I agree with Robert. You are missing the 4 name of the column in the references clause 3 (and you should be getting the error 150). I'll 2 add that you can check how the tables got 1 created in reality with:

SHOW CREATE TABLE posts;

The previous answers deal with the foreign 9 key constraint. While the foreign key constraint 8 is definitely useful to maintain referential 7 integrity, the concept of "foreign 6 key" itself is fundamental to the relational 5 model of data, regardless of whether you 4 use the constraint or not.

Whenever you do 3 an equijoin, you are equating a foreign key to 2 something, usually the key it references. Example:

select *
from 
   Students
inner join
   StudentCourses
on Students.StudentId = StudentCourses.StudentId

StudentCourses.StudentId 1 is a foreign key referencing Students.StudentId.