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

MySQL server or SQL server? -- http://stackoverflow.com/questions/5835978/how-to-properly-create-composite-primary-keys-mysql — jcho360, Mar 08 '13 at 19:44
When you say not working, define what you mean. Does it throw any errors? — christopher, Mar 08 '13 at 19:47
@oliver for better analysis report....still i can create two with same set of values of(course+subject+year) — RbG, Mar 08 '13 at 19:47
@ChrisCooney...no..still i can create two rows with same combination of course,subject,year — RbG, Mar 08 '13 at 19:48
rest means???..the insertion command or the create table command?? — RbG, Mar 08 '13 at 19:52
Post the whole `CREATE TABLE` statement, and some sample statements that you say created duplicate rows. — Barmar, Mar 08 '13 at 19:59
I tried to reproduce this at sqlfiddle, I got the error `Schema Creation Failed: Duplicate entry 'English-Composition-101' for key 'PRIMARY': ` — Barmar, Mar 08 '13 at 20:03

RbG · Accepted Answer · 2015-07-22T18:33:13.360

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)

score 4 · Answer 2 · answered Mar 08 '13 at 19:53

4

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

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

answered Mar 08 '13 at 19:53

bizzehdee

20,289
11
46
76

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 – RbG Mar 08 '13 at 19:56
same combo in same order? ie a=1, b=2, c=3 on more than 1 row? – bizzehdee Mar 08 '13 at 20:02
run SHOW INDEXS table_name and/or SHOW KEYS and see if the key actually set – bizzehdee Mar 08 '13 at 20:28

how to create a composite key in MySQL database

2 Answers2

Linked