Should foreign keys become table primary key?

Question

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.

Solution 1

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.

Solution 2

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).

Solution 3

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)

Solution 4

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.

Solution 5

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

View more solutions

Comments

[Carvell Fenton]

I hear what you're saying I think, but to clarify, in your opinion it would be better to create an artificial auto-increment ID, artifical in the sense that it has no meaning outside just being a key, rather than the composite one?

[Carvell Fenton]

I am preventing NULLs, so if that is the case, would you advocate for the composite key?

[Carvell Fenton]

So just to clarify, since I do only want there to be one comment per student/session/subject, are you saying you would go with the composite and not the auto-increment key? I just got confused by your last sentence.

[Carvell Fenton]

Did you mean: "If that is NOT possible, then you would need to expand your uniqueness constraint out to include another column."?

[Thomas]

Actually, I was correct initially, just phrased poorly. I've corrected. If, in effect, the three columns are not unique, then you would need to obviously expand the uniqueness constraint.

[Carvell Fenton]

+1 This answer helped too, I accepted Thomas' because I thought it was a little more complete. Sometimes I need lots of info for me to get the point! :)

[Carvell Fenton]

I guess I'll be honest here, although inexperienced, the idea of adding the auto-increment key outside of the info provided by Thomas and Patrick seems like the easy way out when designing. I agree that one would have to be careful with future changes, but the combined key seems to convey more information? Maybe that's inexperience talking...

[Dave Swersky]

If you really do want only one comment per student/session/subject, you could create a composite unique key on those three fields. It's not necessary to make them into a composite primary key, though that would have the same effect. You'd still want to validate at the application level, but the unique constraint would enforce the one comment rule.

[Carvell Fenton]

I see what you're getting at, but since each of the foreign keys are primary keys in the referenced tables, and since there can only be one comment per student, per session subject, wouldn't that protect from the overlap? Maybe adding the additional primary key as you suggest is "safest"?

[pranav annam]

It's possibly JUST a measure of safety, but it's safety I would want. If the foreign keys are not as simplistic as my example above, you reduce, but do not eliminate the possibility of mistaken joins.

[Diego Favero]

it depends of what kind of relationship you have between the tables... in my case, I use relation 1:1 and it is not possible to register any fk = null