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? -- stackoverflow.com/questions/5835978/… — jcho360
– jcho360, Commented Mar 8, 2013 at 19:44
When you say not working, define what you mean. Does it throw any errors? — christopher
– christopher, Commented Mar 8, 2013 at 19:47
@oliver for better analysis report....still i can create two with same set of values of(course+subject+year) — RbG
– RbG, Commented Mar 8, 2013 at 19:47
@ChrisCooney...no..still i can create two rows with same combination of course,subject,year — RbG
– RbG, Commented Mar 8, 2013 at 19:48
Post the whole CREATE TABLE statement, and some sample statements that you say created duplicate rows. — Barmar
– Barmar, Commented Mar 8, 2013 at 19:59

RbG · Accepted Answer · 2015-07-22 18:33:13Z

6

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)

edited Jul 22, 2015 at 18:33

answered Sep 16, 2013 at 6:33

RbG

3,1633 gold badges35 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bizzehdee · Accepted Answer · 2013-03-08 19:53:55Z

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 8, 2013 at 19:53

bizzehdee

21.2k13 gold badges49 silver badges79 bronze badges

3 Comments

RbG Over a year ago

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 Over a year ago

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

bizzehdee Over a year ago

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

Collectives™ on Stack Overflow

how to create a composite key in MySQL database

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related