CODEWITHSUNDEEP
sqlpostgresql
kiran6
kiran6

Reputation: 1295

Best way to define composite primary key

I would a create a users table with id primary key, name, password. I want all the names within the users table to be unique. Which is the best way to do it.

  1. Define (id, name) as a composite primary key.

  2. Define (id) -> primary key and name -> unique key.

Why would you choose one over other?

Upvotes: 1

Views: 1296

Answers (1)

user5886152
user5886152

Reputation:

There is no "best" way, although a lot of people will try to tell you there is.

You can use an artificial key, like your id field, which is usually an integer, or a natural key, which would be your name field.

Which one you choose is mostly personal preference. There are pros and cons to each, but successful applications have been built with both. Do a search for "artificial vs natural key" to find countless debates about which is best.

However, there would be no point to creating an ID field, and then making a composite primary key - use either the ID field alone, and create a unique index on the name, or use the name field and don't bother with an id.

Upvotes: 2

Related Questions