Composite Primary key vs additional “ID” column?

by

Say that {Author, Title, Edition} uniquely identifies a book, then the following holds:

  1. It is a superkey — uniquely identifies a tuple (row).

  2. It is irreducible — removing any of the columns does not make it a key any more.

  3. It is a candidate key — an irreducible superkey is a candidate key.

Now let’s consider the ID (integer)

I can reason that the Book table key will show up in few other tables as a foreign key and also in few indexes. So, it will take quite a bit of space — say three columns x 40 characters (or whatever…) — in each of these tables plus in matching indexes.

In order to make these “other” tables and indexes smaller, I can add a unique-integer-column to the Book table to be used as a key which will be referenced as a foreign key. Say something like:

alter table Book add BookID integer not null identity;

With BookID being (must be) unique too, the Book table now has two candidate keys.

Now I can select the BookID as a primary key.

alter table Book add constraint pk_Book primary key (BookID);

However, the {Author,Title,Edition} must stay a key (unique) in order to prevent something like this:

BookID  Author      Title           Edition
-----------------------------------------------
  1      C.J.Date  Database Design     1
  2      C.J.Date  Database Design     1

To sum it up, adding the BookID — and choosing it as the primary — did not stop {Author, Title, Edition} being a (candidate) key. It still must have its own unique constraint and usually the matching index.

Also note that from the design point, this decision was done on the “physical level”. In general, on the logical level of design, this ID does not exists — it got introduced during the consideration of column sizes and indexes. So, the physical schema was derived from the logical one. Depending on the DB size, RDBMS and hardware used, none of that size-reasoning may have measurable effect — so using {Author, Title, Edition} as a PK may be perfectly good design — until proven differently.

More Related Contents:

  1. How to create multiple one to one’s
  2. What does ON [PRIMARY] mean?
  3. Single or multiple databases
  4. SQL – How to store and navigate hierarchies?
  5. T-SQL Cast versus Convert
  6. A beginner’s guide to SQL database design [closed]
  7. Physical vs. logical (hard vs. soft) delete of database record? [closed]
  8. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  9. single fixed table with multiple columns vs flexible abstract tables
  10. How to design a database for User Defined Fields?
  11. Splitting the string in sql server
  12. Should I design a table with a primary key of varchar or int?
  13. Why do you create a View in a database?
  14. Need to list all triggers in SQL Server database with table name and table’s schema
  15. Logging ALL Queries on a SQL Server 2008 Express Database?
  16. How can I do a BEFORE UPDATED trigger with sql server?
  17. Deferrable Constraints in SQL Server
  18. MySQL – how to front pad zip code with “0”?
  19. Know relationships between all the tables of database in SQL Server
  20. Can SQL Server Express LocalDB be connected to remotely?
  21. How do I create and query linked database servers in SQL Server?
  22. The object ‘DF__*’ is dependent on column ‘*’ – Changing int to double
  23. SQL: Using NULL values vs. default values
  24. Suggestions for implementing audit tables in SQL Server?
  25. SQL Server search using like while ignoring blank spaces
  26. How do I change db schema to dbo
  27. What is the best way to implement soft deletion?
  28. Is there a severe performance hit for using Foreign Keys in SQL Server?
  29. Space used by nulls in database
  30. Historical / auditable database

Leave a Comment