Primary Keys

[esport]

Hi Guys,

 

I have been told to have an ID field in most tables I create, that is auto increment and is set to the primary key. However, if I was to say to have a table called user_rating, and the I only want 1 entry of the task_id, user_id and from_user_id combination,  I would set each of these fields as a primary key. However, if I have an ID field added to this table as auto inc and as a primary key, then this would allow duplicates of this combination in the table. So if it necessary to have the ID field in these types of tables, or would I just not set the ID field as a primary key, just a unique key, and only set  task_id, user_id and from_user_id combination as PK?

 

Thanks

 

Daniel

 

[fenway]

You are correct in this special case -- if you're going to be using the combination of the two FKs as a UNIQUE key contraints (i.e. for REPLACE, or dupe control), then having another aspect of the record that's _always_ unique doesn't help much.

 

In this case, I would suspect that adding a UID field would just mess things up... but I haven't tried this in a while.

[esport]

Thanks for your prompt reply. The reason why I would have a field ID as auto increment, is that it would be eaiser to reference especially if the database gets big. So you think its not necessary to have the ID field at all, and just have the combination of the 3 foreign keys as the primary?

[fenway]

Thanks for your prompt reply. The reason why I would have a field ID as auto increment, is that it would be eaiser to reference especially if the database gets big. So you think its not necessary to have the ID field at all, and just have the combination of the 3 foreign keys as the primary?

Hmmm... upon second thought, I guess mysql must respect all unique indexes... so I suppose you should have a UID field (PK) and the other two fields (unique across both).  Sorry, my bad.