在MySQL外键吗?Foreign keys in MySQL?
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;
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
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.
(原文：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;
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.
(原文：do you think you could explain what this is doing a little more? Is it just a join?)
- 我怎么能有MySQL写输出文件作为不同的用户? - How can I have MySQL write outfiles as a different user?
- SQL查询字段不包含$ x的地方SQL Query Where Field DOES NOT Contain $x
- MySQL更新错误当使用特殊字符MySQL update error when special characters are used
- 的最佳实践是什么命名数据库表提供自然组织?What is the best practice for naming database tables to provide natural organization?
- 在相同的表一对多关系One-to-many relationship in the same table
- 在MySQL外键吗?Foreign keys in MySQL?
- 帮助编写一个查询:混乱的顺序操作的GROUP BY和秩序Help write a query: Confusion over order of operations of GROUP BY and ORDER BY
- MySQL加密列MySQL encrypted columns
- 全文搜索相关性测量在吗? - Full-text search relevance is measured in?
- 如何编写一个mysql查询,将读取数据从一个表和写信给另一个?How do I write a mysql query that will read data from one table and write to another?
- 如何使用外键为新表how to use foreign key into new table