Should foreign keys become table primary key?

Question

Hello again,

I have a table (session_comments) with the following fields structure:

student_id (foreign key to students table)
session_id (foreign key to sessions table)
session_subject_ID (foreign key to session_subjects table)
user_id (foreign key to users table)
comment_date_time
comment

Now, the combination of student_id, session_id, and session_subject_id will uniquely identify a comment about that student for that session subject.

Given that combined they are unique, even though they are foreign keys, is there an advantage to me making them the combined primary key for that table?

Thanks again.

Answer 1

I'd really recommend you use a primary key that's generated for you by your database of choice. Mainly because if you alter the structure of that table during any future maintainance then you run the risk of your unique key becoming non-unique. Which can be a really tough problem to sort out. Also having a unique primary key makes querying the table much, much easier.

Unique IDs for postgres: http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL

Unique IDs for Mysql: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

Answer 2

The only reason to make them into a composite primary key would be to enforce one comment per student/Session/Subject. Assuming you don't want to do that, I would not create another key.

Answer 3

No. FOREIGN keys can contain NULLs which are not allowed in PRIMARY keys. The best you can do is create a UNIQUE index from the columns.

Create a PRIMARY key on the table.

Response: My next question is: Is there a possibility of overlap between the keys from the 4 tables?

These two would create the same composite key of 101010101: student: 1010,session: 10,subject: 10,user: 1 student: 10,session: 1010,subject: 10,user: 1

I'm just pointing out that the four columns should have clearly different domains for the overlap to diminish in possibility.

Probably best to go with a true primary key.

Answer 4

1. Making them the primary key will force uniqueness (as opposed to imply it).
2. The primary key will presumably be clustered (depending on the dbms) which will improve performance for some queries.
3. It saves the space of adding a unique constraint which in some DBMS also creates a unique index.

Whether you make these three the primary key or not, you will still need some sort of uniqueness constraint to guarantee that a student cannot be associated with the same session and session_subject_id twice. If that scenario is allowed, then you would need to expand your uniqueness constraint out to include another column.

No matter what choice you make, you should absolutely have some sort of uniqueness constraint on the table.

If you are debating as to whether to create a surrogate primary key + a unique constraint on the three columns, I would say that it depends on whether this table will have child tables. If it will, then referencing the surrogate key will be easier and smaller. If it will not, then IMO, the surrogate key does not really give you much and you might as well use the three columns as the PK.

Answer 5

It depends on the rest of the application.
If you're not going to have foreign keys to the comments table (which seems probable), this is fine.
If you will need to refer to comments from another table, you'd be better to create a unique index with your 3 fields, plus an AutoNumber primary key that will serve in other tables as the foreign key (much simpler and cheaper than the 3 fields).

Answer 6

The debate of natural vs artificial keys is as old as any database implementation. Read about pro's and con's on wikipedia.

Arguments for the surrogate keys are easily disputed on theoretical level (for example argument that with natural keys you run the risk of your PK becoming non-unique can be counter-argumented with answer - good! if I run into that situation it is good that things would break instead of having artificially unique primary keys with duplicate records for actual data).

Another good argument is that artificial keys are either redundant (there is another unique key on the table) or they are allowing you to store essentially non-unique records.

Still, finding good natural keys is sometimes so hard that you must choose something artificial and allow for situation when you will have a person with a same name, born on same date (or with unknown date), with another xy properties that are same in value.

Also, it is not so clear what is artificial and what is natural. You might say for example that SSN is natural for your data. Even though it is really composed number.

As for the performance of multi-key relationships - these are not as bad as you would think, furthermore - it segments the indices in a natural way and with such keys you usually end up with a database that performs really nicely with common queries without any additional indexes.

If you consider these problems seriously and if you are trying to build complex system, please read some good literature (C.J.Date Introduction to Database Systems, currently in 8th edition comes to mind)