CODEWITHSUNDEEP
mysqlsqlconstraintscompositemysql-workbench
Vlad
Vlad

Reputation: 2773

How to create composite key?

I am new to MySQL WB so I can create foreign key by adding relation between to dependen tables.

But how to create a composite key? For example I have this table sql

CREATE TABLE TASKS
(
 TEST_ID NUMBER(6) CONSTRAINT FK_TASKS_TESTS REFERENCES TESTS(TEST_ID),
 TASK_ID NUMBER(3),
 MAX_POINTS NUMBER,
 CONSTRAINT PK_TASKS PRIMARY KEY (TEST_ID, TASK_ID)
);

Is there an option for this or it should be created otherwise?

Upvotes: 4

Views: 21909

Answers (3)

Usman
Usman

Reputation: 575

Suppose you have already created a table now you can use this query to make composite primary key

alter table employee add primary key(emp_id,emp_name);

Upvotes: 0

Saad Touhbi
Saad Touhbi

Reputation: 345

First of all the Foreign Key constraint, i don't think it should be put there. As far as i can tell all the constraints are declared after variable declaration (it's one of the norms of sql in general) for the composite key you nearly had it, you just one thing wrong and that is writing Contraint. here is the example working

CREATE TABLE IF NOT EXISTS TASK (
  TEST_ID NUMBER(6) ,
  TASK_ID NUMBER(3) ,
  MAX_POINTS NUMBER ,
  PRIMARY KEY (TEST_ID,TASK_ID),

  CONSTRAINT fk_1 FOREIGN KEY (`TEST_ID`) REFERENCES TEST (TEST_ID)
)

Upvotes: 3

davidethell
davidethell

Reputation: 12018

The question comes down to uniqueness. If you know that TASK_ID will be unique then it will be better to have a single column as your primary key. MySQL allows for a multi-column primary key, but if TASK_ID will be unique just make that the primary key and add a another index on TEST_ID. The syntax for the primary key would be:

CREATE TABLE TASKS
(
TEST_ID NUMBER(6),
TASK_ID NUMBER(3),
MAX_POINTS NUMBER,
PRIMARY_KEY(TASK_ID) -- OR PRIMARY_KEY(TASK_ID, TEST_ID) for the composite key
);

See this discussion for more information.

Upvotes: 4

Related Questions