开发者

MySQL child table need to have primary key?

开发者 https://www.devze.com 2023-03-10 00:46 出处:网络
I have (all InnoDB) a parenttable tbl1 with primary key some_id and child (referencing) table tbl2 with another_id referencing tbl1.some_id as foreign key. tbl2.开发者_StackOverflow社区another_id is n

I have (all InnoDB) a parent table tbl1 with primary key some_id and child (referencing) table tbl2 with another_id referencing tbl1.some_id as foreign key. tbl2.开发者_StackOverflow社区another_id is not the primary key of tbl2 as this is a one to many relationship. Should my child table also have a primary key - maybe for indexing purposes?

The thread here almost answered my question but raised more questions about composite keys so I posted another question about that elsewhere...


Yes, you need to have a primary key on tbl2. This will allow you to uniquely identify each row which will be useful for many purposes in the future. You can read up on the importance of keys with a google search.


When i've done this in the past i have always used an auto incrementing primary key along with a foreign key. You can join on the foreign key and have the required one to many relationship. Are you against having a primary and foreign key in a table? I've always found it useful for quickly identifying rows in the second table.

"InnoDB permits a foreign key to reference any index column or group of columns. 
However, in the referenced table, there must be an index where the referenced 
columns are listed as the first columns in the same order."

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

0

精彩评论

暂无评论...
验证码 换一张
取 消