mysql - Need to create table with two primary keys (SQL) -

i need make table 2 primary keys in sql, im new coding , need im confused!

anyway here tables 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 2 tables, need merge them somehow , make table called eventparticipant (event_id , participantid) primary keys? idea how please?

thank you!

i assume trying create m : n relationship between 2 tables , eventparticipant table serve junction table.

this new table have 1 primary key consisting of 2 columns participant_id , event_id. note table can have 1 primary key, primary key can made of several columns. each combination of values of these columns must 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 optional. means if delete either participant or event, junction between 2 automatically deleted. on other hand, if don't add clause, not able delete participants or events, unless first delete related eventparticipant records.

if didn't create these foreign key constraints, possible add records in table eventparticipant id's not existing in participant or event , delete participants or events , leave ghosted records in eventparticipant behind.

---

if want merge these 2 tables, don't physically, instead create merged view or select query on these 3 tables

select     p.participant_fname,     p.participant_lname,     e.event_name,     e.event_date     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 merged table mean have non-normalized data. bad, because must keep several copies of same data. each participant of event, have enter event names , dates again. makes difficult maintain data , keep consistent. if event date changes, instance, can happen forget update participants or mistype somewhere.