How to create composite key?

Question

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?

Answer 1

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.