在MySQL外键吗?Foreign keys in MySQL?

- 此内容更新于:2014-12-30
主题:

原文:

I have been slowly learning SQL the last few weeks. I've picked up all of the relational algebra and the basics of how relational databases work. What I'm trying to do now is learn how it's implemented.

A stumbling block I've come across in this, is foreign keys in MySQL. I can't seem to find much about the other than that they exist in the InnoDB storage schema that MySQL has.

What is a simple example of foreign keys implemented in MySQL?

Here's part of a schema I wrote that doesn't seem to be working if you would rather point out my flaw than show me a working example.

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,
Foreign Key(`uID`) references users
) ENGINE=InnoDB;
解决方案:
假设你的类别和用户表已经存在和包含cID和uID分别是主键,这应该工作: 列名中需要引用条款。
原文:

Assuming your categories and users table already exist and contain cID and uID respectively 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 name is required in the references clause.

icco的回复:太棒了,谢谢。

(原文:Awesome, thanks.)

解决方案:
原文:

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

One idiosyncrasy I've encountered in MySQL is that foreign key declaration 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 gives no message that it has failed to create the foreign key constraint. It simply ignores the request, and creates the table without the foreign key (if you SHOW CREATE TABLE posts, you may see no foreign key declaration). I've always thought this is a bad feature of MySQL!

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

Robert Gamble的回复:你能提供一个参照你的要求关于外键列的名字吗?根据语法(dev.mysql.com/doc/refman/5.0/en/…)这不是一个可选参数,并# 39;t为我工作,除非我指定它。

(原文:Can you provide a reference to your claim regarding columns names in foreign key? According to the syntax at (dev.mysql.com/doc/refman/5.0/en/…) this is not an optional parameter and it doesn't work for me unless I specify it.)

Bill Karwin的回复:是的,你是对的,现在我更仔细地测试它观察同样的事情。标准的SQL应该允许隐式引用的列名称如果这# 39;主要表中的相同,但InnoDB并# 39;支持这个,不幸的是。我的过失!

(原文:Yes, you are right, now that I test it more carefully I do observe the same thing. Standard SQL should permit the referenced column name to be implicit if it's the same in the primary table, but InnoDB doesn't support this, unfortunately. Mea culpa!)

Bill Karwin的回复:我第一段编辑更准确。

(原文:I have edited my first paragraph to be more accurate.)

MBCook的回复:这取决于您的MySQL版本。在5,它无法创建表与有用的消息“错误150,这意味着“未能创建table"或其他令人难以置信的有用的东西。

(原文:It depends on your version of MySQL. In 5, it fails to create the table with the helpful message "Error 150" which means "failed to create table" or some other incredibly useful thing.)

解决方案:
这有一些代码显示如何创建外键,并在创建表。 这里有一个简单的例子从:
原文:

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

Here's 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;
解决方案:
我同意罗伯特。你是失踪的名字列在引用条款(150年,你应该错误)。我将添加,你可以看看现实中创建的表有:
原文:

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

SHOW CREATE TABLE posts;
解决方案:
原文:

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

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

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

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

icco的回复:你认为你可以解释多一点这是做什么?只是一个连接吗?

(原文:do you think you could explain what this is doing a little more? Is it just a join?)