how to create a composite key in MySQL database

Question

i am working on mysql server.where i have created a table, named question . column/attributes of this table are (course,subject,year,question) i want to create a primary key(or composite key) consists of (course+subject+year). i.e. for a particular course+subject+year combination there can be only one question.there will be only one row with the combination of (course+subject+year),creation of another row won't be possible. i have done it by :

primary key(course,subject,year);

but it's not working.still i can create two rows with same combination of course,subject,year.

can anyone tell me how can i create a composite key propery????

Answer 1

the syntax is CONSTRAINT constraint_name PRIMARY KEY(col1,col2,col3) for example ::

CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

the above example will work if you are writting it while you are creating the table for example ::

CREATE TABLE person (
   P_Id int ,
   ............,
   ............,
   CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
);

to add this constraint to an existing table you need to follow the following syntax

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (P_Id,LastName)