0

I have a table that uses 2 foreign key fields and a date field. Is it common to have a table use 3 or more fields as a primary key? And are there any disadvantages to doing this?

--

My 3 tables are employees, training, and emp_training. The employees table holds employee data. Training table holds different training courses. And I am designing the emp_training table to be the fields EmployeeID (FK), TrainingID (FK), OnDate.

An employee can do multiple training courses, and can do the same training course multiple times. But they cannot to the same training course more than once on the same day. Which is better to implement:

Option A - Make all 3 fields a primary key

Option B - Add an autonumber PK field, and use a query to find any potential duplicates.

I've created many tables before using 2 fields as a primary key, but never 3, so I'm curious if there is any disadvantage to proceeding with option A

Drew
  • 24,851
  • 10
  • 43
  • 78
smally
  • 453
  • 1
  • 4
  • 14
  • There, I modified your title. I think that is what you meant to say. – Drew Jul 15 '16 at 15:40
  • 1
    One more point (besides the answer below): Your *But they cannot to the same training course more than once on the same day* is a **good reason against a PK**: This is no absolut rule, just kind of business logic. What would you do, if there comes a situation, where the same course can be taken twice a day? What would you do then? – Shnugo Jul 15 '16 at 16:21

4 Answers4

4

It's worth to mention, that with SQL Server the PK by default is the one and only clustered key, but you are allowed to create a non-clustered PK as well.

You may define a new clustered index which is not the PK. "Primary Key" is just a name actually...

The most important question is: Which columns participate in a clustered key and (this is the very most important question): Do they have an implicit sorting? And (very important too): Are there many update operations which change the content of participating columns?

You must be aware, that a clustered key defines the physical order on your hard disc. In other words: The clustered key is the table itself. You can think of an index with all columns included. If your leading column (worst case) is a GUID, each insert to your table will not be in order. This leads to a 99.99% fragmentation.

If a clustered index is bound to the time of insert or a running number (best case), it will never go into fragmentation!

What makes things worse: If there is a clustered key (whether it's called PK or not), it will be used as lookup key for other indexes.

So: in many cases it is best to use a running number as clustered key and a non-clustered multi-column index which is much faster to re-build than as if it was the clustered one.

All indexes will profit from this!

My advise for you:

  • Option C: a running number as PK and additionally a unique multi-column-key to ensure data integrity. No need to use own logic here...
Shnugo
  • 66,100
  • 9
  • 53
  • 114
2

Yes, you can have a poor strategy for choosing too many columns for your composite Primary Key (PK) if a better strategy could be employeed for uniqueness via secondary indexes.

Remember that the PK is special. There is only 1 physical / clustered ordering of your data. Changes to the data via Inserts and Updates (and incumbent shuffling) has overhead there that would not exist if maintained in a secondary index.

So the following can have not-so-insignificant differences:

  1. A primary key with 5 composite columns

vs.

  1. A primary key with 1 or 2 columns plus
    • Secondary indexes that maintain uniqueness if thought through well

The former mandates movement of data between data pages to maintain the clustered index (the PK). Which might suggest why so often one sees:

(
id int auto_increment primary key,
...
)

in table designs.

Performance with Index Width:

The width of the PK in 1. above is narrow. The width of 2. can be quite wide. Wider keys propagating to child relationships will slow performance and concurrency.

Cases of FK compositions:

Special cases of compositions of foreign keys simply cannot be achieved without the use of a single column index, preferably the PK, as seen in this recent Answer of mine.

Community
  • 1
  • 1
Drew
  • 24,851
  • 10
  • 43
  • 78
1

I dont think that there is any problem of creating a table with a composed PK ,such tables are needed in larger db .There is not a real problem in creating a table with 2FK whose with the OnDate field form the PK . Both ways are vailable. Good luck!

Enio Cangonji
  • 9
  • 2
  • 1
    More important then the question *May a PK consist of many columns?* is the question *Is this index clustered and are the participating columns implicitly sorted?*. A bad clustered index will slow down all other indexes as well, as it serves as lookup key for them... – Shnugo Jul 15 '16 at 16:07
1

If you assign primary key on more than one column it will be composite primary key. For example,

CREATE TABLE employee(
  training VARCHAR(10),
  emp_training VARCHAR (20),
  OnDate INTEGER,
  PRIMARY KEY (training, emp_training, OnDate)
)

there will be unique records in training, emp_training, OnDate together and can not be null together.

As already stated you can have a single primary key which consists of multiple columns.

If the question was how to make the columns primary keys separately, that's not possible. However, you can create 1 primary key and add two unique keys

R.K123
  • 159
  • 2
  • 9
  • You should **never** create a PK (or any other constraint) without a name! Use `CONSTRAINT PK_employee PRIMARY KEY(training,emp_training,OnDate)`. – Shnugo Jul 15 '16 at 16:05