Postgresql: Primary key for table with one column

Question

Sometimes, there are certain tables in an application with only one column in each of them. Data of records within the respective columns are unique. Examples are: a table for country names, a table for product names (up to 60 characters long, say), a table for company codes (3 characters long and determined by the user), a table for address types (say, billing, delivery), etc.

For tables like these, as the records are unique and not null, the only column can be used as the primary key, technically speaking.

So my question is, is it good enough to use that column as the primary key for the table? Or, is it still desirable to add another column (country_id, product_id, company_id, addresstype_id) as the primary key for the table? Why?

Thanks in advance for any advice.

If a product name ever changes, that's much easier to do if everyone else refers to the product by ID, not name. — melpomene
– melpomene, Commented Feb 16, 2019 at 12:06
A country name can be quite long, using a country_code greatly reduces space usage (and there's an international standard, too) — dnoeth
– dnoeth, Commented Feb 16, 2019 at 12:12
There could be homonyms(Paris, Texas <--> Paris, France), and there can be (temporal) renamings (Leningrad <--> st Petersburg). A surrogate key can reduce the cost of updating these. — wildplasser
– wildplasser, Commented Feb 16, 2019 at 12:30
I will most like have to change some tables, like the ones for country, product. Thanks all for the help. — kennethw
– kennethw, Commented Feb 16, 2019 at 16:44

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-02-16 12:18:31Z

2

there is always a debate between using surrogate keys and composite keys as primary key. using composite primary keys always introduces some complexity to your database design so to your application.

think that you have another table which is needed to have direct relationship between your resulting table (billing table). For the composite key scenario you need to have 4 columns in your related table in order to connect with the billing table. On the other hand, if you use surrogate keys, you will have one identity column (simplicity) and you can create unique constraint on (country_id, product_id, company_id, addresstype_id)

but it is hard to say this approach is better then the other one because they both have Pros and Cons.

You can check This for more information

answered Feb 16, 2019 at 12:18

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kennethw Over a year ago

Thanks. It looks that I will modfify the setup of the tables. Surogate keys appear to be a better choice for certain scenarios. I am trying to buid a database for reording customers' orders, our purchase orders, sales invoices, packing lists.

Derviş Kayımbaşıoğlu Over a year ago

Good luck mate. If you feel that this answer is helpful for you, please consider to upvote and/or mark it as answer so the community can also benefit from it.

philipxy Over a year ago

@kennethw & Simonare It's not surrogate vs composite here, it's surrogate vs natural. Of course, composite is a case of natural.

Collectives™ on Stack Overflow

Postgresql: Primary key for table with one column

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related