Should I use Natural Identity Columns without GUID?

Question

Is it ok to define tables primary key with 2 foreign key column that in combination must be unique? And thus not add a new id column (Guid or int)?

Is there a negative to this?

Answer 1

Yes, it's completely OK. Why not? The downside of composite primary keys is that it can be long and it might be harder to identify a single row uniquely from the application perspective. However, for a couple integer columns (specially in junction tables), it's a good practice.

Answer 2

For some definitions of "ok", yes. As long as you never intend to add additional fields to this intersection table, that'll be fine. However, if you intend to have more fields, it's good practice to have an ID field. It's still fine, come to think of it, but can be more awkward.

Unless, of course, disk space is at a serious premium!

Answer 3

If you look into any database textbook, you will find such tables en masse. This is the default way to define n-to-m relations. For example:

article = (id, title, text)
author = (id, name)
article_author = (article_id, author_id)

Semantically, article_author is not a new entity so you might refrain from defining it as the primary key and instead create it as a normal index with UNIQUE constraint.

Answer 4

Yes, I agree, with "some definition of OK" it is OK. But the moment you decide to reference this composite primary key from somewhere (i.e. move it to a foreign key), it quickly bocomes NG (Not Good).

Answer 5

Natural versus Artificial primary keys is one of those issues that gets widely debated and IMHO the discussion only seems to see the positions harden.

In my opinion both work so long as the developer knows how to avoid the downside of both. A natural primary key (whether composite or single column) more nearly ensures that duplicate rows are not added to the DB. Whereas with artificial primary keys it is necessary to first check the record is unique (as opposed to the primary key, which being artificial will always be unique). One effective way to achieve this is to have a unique or candidate index on the fields that make the record unique (e.g. the fields that make a candidate for a primary key)

Meanwhile an artificial primary key makes for easy joining. Relations can be made with a single field to single field join. With a composite key the writer of the SQL statement must know how many fields to include in the join.