Relational Database Design - double primary key in one table ?

Question

I have a table that keeps the user ratings for items. I want to allow each user to rate an item only once, is there any way to force the database not to allow duplicate for itemId and userId?

I don't want each individual field to be a primary key. I want the primary key to be based on both of them at the same time. for example there is a row:

itemId= 1 & userId = 1

following should be allowed:

itemId= 2 & userId = 1
itemId= 1 & userId = 2

following should NOT be allowed:

itemId= 1 & userId = 1

Answer 1

Yes. Create a primary key on both itemId and userId.

To do this in T-SQL (SQL Server) you would use something like:

CREATE TABLE rating (
  itemId int NOT NULL
    CONSTRAINT fk_rating_item FOREIGN KEY REFERENCES item ( itemId ),
  userId int NOT NULL
    CONSTRAINT fk_rating_user FOREIGN KEY REFERENCES [user] ( userId ),
  thumbsUp int,
  thumbsDown int,
  CONSTRAINT pk_rating PRIMARY KEY ( itemId, userId )
)

(This assumes your items table is 'item' and your users table is 'user'.)

I'm not sure why you have a value for both thumbs up and thumbs down? If this is a boolean value, you might only need one: if thumbs up is 0, then that's effectively thumbs down anyway.

Edit: Definition of composite keys

When you create a primary key on two columns in one table, that means it is only required to be unique for both values, i.e. it will allow for any number of rows with the same itemId as long as each userId is different, and vice-versa.

It's the combination of the two that must be unique, not each part of the key individually.

Answer 2

Most databases will allow for composite primary keys made from multiple columns. Which database are you using?

An interesting, related discussion: What are the down sides of using a composite/compound primary key?

mySQL type example:

createuserratings.sql
CREATE TABLE USERRATINGS
( 
    itemId     INT NOT NULL,
    userId     INT NOT NULL,
    thumbsUp   INT NOT NULL,
    thumbsDown INT NOT NULL,
    PRIMARY KEY (itemID, userID)
);

Do a search for composite/compound primary keys in the mySQL documentation and you should get a lot of info (I don't use mySQL).

Answer 3

Use a composite primary key (primary key consisting of 2 columns).

CREATE TABLE Rating 
(
 itemID     INT NOT NULL
,userID     INT NOT NULL
,thumbsUP   INT NOT NULL
,thumbsDown INT NOT NULL
,PRIMARY KEY (itemID, userID)
);