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

Lombardi

MySQL server or SQL server? -- #5836478 – Tactic

When you say not working, define what you mean. Does it throw any errors? – Drida

@oliver for better analysis report....still i can create two with same set of values of(course+subject+year) – Lombardi

@ChrisCooney...no..still i can create two rows with same combination of course,subject,year – Lombardi

Okay.. And can you post the rest of the SQL statement? – Drida

rest means???..the insertion command or the create table command?? – Lombardi

Post the whole CREATE TABLE statement, and some sample statements that you say created duplicate rows. – Consolata

Can you post the whole CREATE TABLE statement? – Pepe

I tried to reproduce this at sqlfiddle, I got the error Schema Creation Failed: Duplicate entry 'English-Composition-101' for key 'PRIMARY': – Consolata

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)

Lombardi

Answer 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);

Archaeopteryx

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 – Lombardi

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

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