Sample Header Ad - 728x90

What are the consequences, positive or negative, to having a surrogate primary key for a table which already has a guaranteed unique column?

1 vote
1 answer
1003 views
I recently made a table that looks like the one below CREATE TABLE example( id SERIAL PRIMARY KEY, subindusty_id INT UNIQUE NOT NULL, name TEXT NOT NULL -- other fields... ) I realized that in this table I could just remove the column called id and make subindustry_id the primary key. Thinking back, the only reason I can think of for why I didn't make subindustry_id the primary key is for the sake of consistency, considering that I have a lot of other tables with a column called id. Except for things like consistency*, are there any tangible benefits/downsides to having this additional id column as the primary key when there's already another column that'd be a good primary key? \*Consistency as in "that's the way the rest of the tables look". The kind where you're enforcing a certain type of style. I guess the point of this question is to question the practice of giving every single table an id column regardless of the fact that they might already have a more suitable candidate for a primary key.
Asked by m0meni (147 rep)
Jan 19, 2017, 06:26 PM
Last activity: Jan 20, 2017, 05:53 AM