Need to create table with two primary keys (SQL)

Question

i need to make a table with two primary keys in SQL, im new to coding and really need help im so confused!

anyway here are my tables that i have.

mysql> create table participant(
    -> participantid int not null primary key,
    -> participant_fname varchar(20) not null,
    -> participant_lname varchar(20) not null);


    mysql> create table event(
    -> event_id int not null primary key,
    -> event_name varchar(35) not null,
    -> event_date int not null);

so those are my two tables, i need to merge them somehow and make a table called eventparticipant with (event_id and participantid) as primary keys? any idea how please?

thank you!

I removed `sql-server` tag, as this question is about `mysql`. Please don't use unrelated tags. — Stefano Zanini, Apr 14 '17 at 15:19
You are looking for a composite key. Here is a question that may help: http://stackoverflow.com/q/5835978/3901060 — FamousJameous, Apr 14 '17 at 17:12

Olivier Jacot-Descombes · Answer 1 · 2017-04-14T15:50:35.027

I assume that you are trying to create a m : n relationship between the two tables and that the eventparticipant table will serve as junction table.

This new table will have only one primary key consisting of the two columns participant_id and event_id. Note that a table can have only one primary key, but this primary key can be made of several columns. Each combination of the values of these columns must be unique.

CREATE TABLE eventparticipant(
    participant_id int not null,
    event_id int not null,
    PRIMARY KEY ( participant_id, event_id )
);

ALTER TABLE participant
ADD CONSTRAINT fk_participant_eventpart
FOREIGN KEY(participant_id)
REFERENCES eventparticipant(participant_id)
ON DELETE CASCADE;

ALTER TABLE event
ADD CONSTRAINT fk_event_eventpart
FOREIGN KEY(event_id)
REFERENCES eventparticipant(evet_id)
ON DELETE CASCADE;

The ON DELETE CASCADE clause is optional. It means that if you delete either a participant or an event, then the junction between the two will automatically be deleted. On the other hand, if you don't add this clause, then you will not be able to delete participants or events, unless you first delete all related eventparticipant records.

If you didn't create these foreign key constraints, it would be possible to add records in table eventparticipant with id's not existing in participant or event and you could also delete participants or events and leave ghosted records in eventparticipant behind.

If you really want to merge these two tables, don't do this physically, instead create a merged view or just a select query on these three tables

SELECT
    p.participant_fname,
    p.participant_lname,
    e.event_name,
    e.event_date
FROM
    participants p
    INNER JOIN eventparticipant ep
        ON p.participant_id = ep.participant_id
    INNER JOIN event e
        ON ep.event_id = e.event_id;

Note: creating a merged table would mean to have non-normalized data. This is bad, because you must keep several copies of the same data. For each participant of an event, you would have to enter the event names and dates again. This makes it difficult to maintain the data and to keep it consistent. If the event date changes, for instance, then it can happen that you forget to update it for all participants or that you mistype it somewhere.

score 1 · Answer 2 · answered Apr 14 '17 at 15:22

What you are looking for is Composite key which is a key made from two or more columns. So, your table is defined by:

create table eventparticipant(
    event_id int not null,
    participant_id int not null,
    PRIMARY KEY (participant_id, event_id)
);

score 0 · Answer 3 · answered Apr 14 '17 at 15:17

You can't have two primary keys in a table. Also, you don't really want to combine those tables do you? Good design would have separate tables for participants and events. The participant table might have a column with a foreign key to the event, or if a participant can have multiple events, you would create another table to link events and participants. IOW, a many to many relationship.

So, from a design perspective, you need to determine if participants can have more than one event.

Need to create table with two primary keys (SQL)

3 Answers3