MySQL中外键的定义方式
我看到有两种完成方法：
方法 1：
CREATE TABLE IF NOT EXISTS `sample` (
`sample_id` tinyint(2) NOT NULL AUTO_INCREMENT,
`description` varchar(32) NOT NULL,
`parent_id` int(10) NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`sample_id`)
) ENGINE=InnoDB;
ALTER TABLE sample ADD CONSTRAINT parent_id FOREIGN KEY (parent_id) REFERENCES parent_tbl(parent_id);
方法 2：
CREATE TABLE IF NOT EXISTS `sample` (
`sample_id` tinyint(2) NOT NULL AUTO_INCREMENT,
`description` varchar(32) NOT NULL,
`parent_id` int(10) NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`sample_id`),
Foreign Key (parent_id) references parent_tbl(parent_id)
) ENGINE=InnoDB;
哪种方法更好，或者何时使用其中一种方法？
I see two ways it is done:
Method 1:
CREATE TABLE IF NOT EXISTS `sample` (
`sample_id` tinyint(2) NOT NULL AUTO_INCREMENT,
`description` varchar(32) NOT NULL,
`parent_id` int(10) NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`sample_id`)
) ENGINE=InnoDB;
ALTER TABLE sample ADD CONSTRAINT parent_id FOREIGN KEY (parent_id) REFERENCES parent_tbl(parent_id);
Method 2:
CREATE TABLE IF NOT EXISTS `sample` (
`sample_id` tinyint(2) NOT NULL AUTO_INCREMENT,
`description` varchar(32) NOT NULL,
`parent_id` int(10) NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`sample_id`),
Foreign Key (parent_id) references parent_tbl(parent_id)
) ENGINE=InnoDB;
Which way is better or when to use one over the other?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果您需要向现有表添加外键，请使用方法 1，如果您要从头开始创建架构，请使用方法 2。
没有最好的方法，他们做同样的事情。
If you need to add a foreign key to an existing table, use method 1, if you are creating the schema from scratch use method 2.
There isn't a best way, they do the same thing.
第一个为您提供了更大的灵活性。
1) 如果您按照顺序创建表，使得在引用表之后创建引用表，则需要使用第一种方法。如果您的引用中有循环，那么可能无法避免这种情况。如果没有循环，则存在一个顺序，其中所有引用的表都在其引用的表之前创建，但您可能不想花时间弄清楚该顺序是什么并重新排列脚本。
2) 创建表时，您并不总是确切地知道需要哪些索引。创建索引时，通常最好测量一些实际数据的性能增益，并且也许尝试多个不同的索引以查看哪个效果更好。为了使此策略发挥作用，您需要首先创建表，插入一些数据，然后需要能够修改索引以进行测试。在这种情况下，删除并重新创建表并不像 ALTER TABLE 那样实用。
除此之外，实际上没有任何区别，如果您从零开始，就没有特别的理由偏爱其中之一。无论哪种方式，生成的索引都是相同的。
The first gives you more flexibility.
1) You are required to use the first method if you create the tables in an order such that a referenced table is created after its referencing table. If you have loops in your references then there may not be a way to avoid this. If there are no loops then there exists an order where all referenced tables are created before their referenced tables, but you may not want to spend time figuring out what that order is and rearranging your scripts.
2) It's not always the case that you know exactly what indexes you will need when you create the table. When you create indexes it is usually a good idea to measure the performance gain on some real data, and perhaps try multiple different indexes to see which works better. For this strategy to work you need to first create the table, insert some data and then you need to be able to modify the indexes for testing. Dropping and recreating the table is not as practical as ALTER TABLE in this situation.
Other than that there isn't really any difference and if you are starting from nothing there is no particular reason to favour one over the other. The resulting index is the same either way.
最终产品是无法区分的。
为了清楚起见（很高兴看到约束明确地独立存在），我可能主张第一个。
为了简洁起见（在 1 条陈述与 2 条陈述中说同样的事情），我可能会提倡第二种。
The end products are indistinguishable.
For clarity (it's nice to see the constraint explictly stand on it's own), I might advocate for the first.
For succinctness (saying the same thing in 1 statement vs 2), I'd might advocate for the second.