Is ID column required in SQL?

Question

Traditionally I have always used an ID column in SQL (mostly mysql and postgresql).

However I am wondering if it is really necessary if the rest of the columns in each row make in unique. In my latest project I have the "ID" column set as my primary key, however I never call it or use it in any way, as the data in the row makes it unique and is much more useful for me.

So, if every row in a SQL table is unique, does it need a primary key ID table, and are there ant performance changes with or without one?

Thanks!

EDIT/Additional info: The specific example that made me ask this question is a table I am using for a many-to-many-to-many-to-many table (if we still call it that at that point) it has 4 columns (plus ID) each of which represents an ID of an external table, and each row will always be numeric and unique. only one of the columns is allowed to be null.

I understand that for normal tables an ID primary key column is a VERY good thing to have. But I get the feeling on this particular table it just wastes space and slows down adding new rows.

Answer 1

If you really do have some pre-existing column in your data set that already does uniquely identify your row - then no, there's no need for an extra ID column. The primary key however must be unique (in ALL circumstances) and cannot be empty (must be NOT NULL).

In my 20+ years of experience in database design, however, this is almost never truly the case. Most "natural" ID's that appear to be unique aren't - ultimately. US Social Security Numbers aren't guaranteed to be unique, and most other "natural" keys end up being almost unique - and that's just not good enough for a database system.

So if you really do have a proper, unique key in your data already - use it! But most of the time, it's easier and more convenient to have just a single surrogate ID that you can guarantee will be unique over all rows.

Answer 2

Don't confuse the logical model with the implementation.

The logical model shows a candidate key (all columns) which could makes your primary key.

Great. However...

In practice, having a multi column primary key has downsides: it's wide, not good when clustered etc. There is plenty of information out there and in the "related" questions list on the right

So, you'd typically

• add a surrogate key (ID column)
• add a unique constraint to keep the other columns unique
• the ID column will be the clustered key (can be only one per table)
• You can make either key the primary key now

The main exception is link or many-to-many tables that link 2 ID columns: a surrogate isn't needed (unless you have a braindead ORM)

Edit, a link: "What should I choose for my primary key?"

Edit2

For many-many tables: SQL: Do you need an auto-incremental primary key for Many-Many tables?