Multiple Primary Keys

[mjstehn]

I want to have a table that has 2 fields that are in other tables, but in this table I want the 2 keys combined to be unique. Someone told me that in SQL that you can have 2 primary keys that are clustered. Is this possible in MySql and if so, how do I go about creating the table.

Thanks,

[statrat]

yes, it is a composite key.

CREATE TABLE test
(field1 INTEGER NOT NULL,
field2 VARCHAR(20) NOT NULL,
PRIMARY KEY (field1,field2)
)

[sfullman]

Just for your information, a table that has two primary keys which are actually fields from two SEPARATE tables is a joiner table. For example, students and classes. You can have one student in many classes, and a single class with many students. If primary for Classes is CID, and for Students is SID, we'd have a joiner table called Classes_Students as follows

CID SID
1 2
1 1
2 2
3 2
4 1
5 2
6 2

So student 1 has classes 1 and 4, 2 has 1, 2, 3, 5, and 6. Technically these are also FOREIGN keys, so you want to keep them transparent from any rational meaning within the company or organization.

Sam Fullman
Compass Point Media