CODEWITHSUNDEEP
sql
cogumel0
cogumel0

Reputation: 2681

SQL - Must there always be a primary key?

There are a couple of similar questions already out there and the consensus seemed to be that a primary key should always be created.

But what if you have a single row table for storing settings (and let's not turn this into a discussion about why it might be good/bad to create a single row table please)?

Surely having a primary key on a single row table becomes completely useless?

Upvotes: 0

Views: 138

Answers (4)

Chipmonkey
Chipmonkey

Reputation: 865

It may seem completely useless, but it's also completely harmless, and I'd vote for harmless with good design principles vs. useless with no design principles every time.

Other people have commented, rightly, that you don't know how you're going to use the table in a year or five years... what if someone comes along and decides they want to duplicate the configuration -- move it to a distributed environment or add a test environment by using a duplicate configuration string or whatever. Having a field that acts like a primary key means that whenever you query the table, if you use the key, you'll be certain no matter what anyone else may do to your table, that you're getting the correct record.

You're right there are a million other aspects -- surrogate keys vs. intelligent keys, indexing, partitioning (silly on a single row table, I know), whatever... but without getting into that I'd vote add the key rather than not add it. You could have done it by the time you read this thread.

Upvotes: 4

proskor
proskor

Reputation: 1412

Having declared a primary key on a single row table in SQL will ensure that there will be no duplicates. Whether it is useless depends on your requirements. Usually it is a good idea to avoid duplicates.

Upvotes: 0

ilitirit
ilitirit

Reputation: 16352

You could always base your primary key on the name of the setting. Then your table would become a key-value store.

But no, in many RDBMS you are not REQUIRED to have a primary key per table.

Upvotes: 0

Jon Raynor
Jon Raynor

Reputation: 3892

Short answer, no key, duplicate records possible. Your planning a single row now, but what about six months in the future when you single row multiplies. Put a primary key on the table, even for single row.

Upvotes: 2

Related Questions