CODEWITHSUNDEEP
mysqldatabasedatabase-design
philly77
philly77

Reputation: 519

Composite primary key question in MySQL

I am a newbie to databases. I am using MySQL, and I have a table A using three attributes to form a composite primary key:

A: [key1, key2, key3], dateCreated, createdBy, ...

Now I have another table B, which needs to refer to records from the table above. Now, reusing all the three attributes above again to form a key seems to be a pain. Is it okay to use an additional auto incrementing dummy "id" attribute to table A as a primary key, and use that as a reference in table B? Which is the right thing to do?

Upvotes: 0

Views: 396

Answers (2)

Scharrels
Scharrels

Reputation: 3055

It would be better to put a unique constraint on the set (key1, key2, key3) and make the single "dummy" id your primary key, as it will be used as a reference for other tables.

If you want to speed up lookups, you kan put additional indices on the set (key1, key2, key3).

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300529

It certainly is. It's called a surrogate key: a surrogate key is not derived from application data.

[There is a debate surrounding the use of surrogate keys. The wiki article linked to lists advantages/disadvantages.]

As @Scharrels points out, a unique constraint should be applied to your 3 fields, if you use a surrogate key.

Upvotes: 1

Related Questions