Re: novice: table design FOREIGN key
| From: | Michael Satterwhite | Date: | Thu, 19 May 2005 15:58:05 +0000 |
| Subject: | Re: novice: table design FOREIGN key | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-215607@lists.php.net to get a copy of this message | ||
tony yau wrote:
| Hi All
|
| Thanks for all your comments on my previous mail, very muchappreciated. | I'm stuck again!
| I've created the following lookup table for m <-> m relationshipbetween | a Group and a Contact table. | | CREATE TABLE Group_Contact( | GroupID INT NOT NULL, | ContactID INT NOT NULL, | | Index (GroupID), | FOREIGN KEY (GroupID) REFERENCES Group (PKey) ON DELETE CASCADE, | Index (ContactID), | FOREIGN KEY (ContactID) REFERENCES Contact (PKey) ON DELETE CASCADE | ) TYPE=InnoDB COMMENT='Group to Contact lookup'; | | what do I need to do to ensure only unique (GroupID,ContactID) pair can be | inserted into the table? This question is really a MySQL question. You might want to join their mailing list as you'll get better answers for this type of question there. That said: The easiest way would be to make GroupId, ContactID a unique index. In this case, primary key would be OK. Add the statement Primary Key(GroupID, ContactId), just before the first "Index" statement.