Mysql Create Index Set As No Duplicates

Question

I have a table p_places that has two columns that are both foriegn keys that references parent tables:

mysql> create table p_places(
    -> user_id int not null,
    -> place_id int not null,
    -> FOREIGN KEY(user_id) references people(user_id) ON DELETE CASCADE,
    -> FOREIGN KEY(place_id) references places(place_id) ON DELETE CASCADE
    -> )engine=innodb;

I am trying to create a index for both of the columns so there can not be duplicate rows(there can be duplicate foriegn keys however...just not rows).

I tried:

alter table p_places add index(user_id+place_id no duplicate);

alter table p_places add unique index(user_id+place_id);

alter table p_places add unique index both_id(user_id+place_id);

But had no success. I would like try to do this with the alter table command for learning purposes rather than creating the table.

score 1 · Accepted Answer · edited May 23 '17 at 10:25

1

You are close to what you want.

ALTER TABLE p_places ADD CONSTRAINT tb_unique UNIQUE(user_id, place_id)

Purpose of Naming Constraint

edited May 23 '17 at 10:25

Community

1
1

answered Apr 05 '13 at 03:00

John Woo

258,903
69
498
492

Purpose of naming constraint was my second question...thanks! – dman Apr 05 '13 at 03:20

Mysql Create Index Set As No Duplicates

1 Answers1