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????

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

Solution 2

if it is mysql you are looking at, you should do something similar to

ALTER TABLE table_name ADD PRIMARY KEY (a, b, c);

Comments

[Ritabrata Gautam]

i have set a primary key same way u have written above while creating the table.but it's still allowing 2 or more rows with same combination of a,b,c

[bizzehdee]

same combo in same order? ie a=1, b=2, c=3 on more than 1 row?

[bizzehdee]

run SHOW INDEXS table_name and/or SHOW KEYS and see if the key actually set