CODEWITHSUNDEEP
mysqlsql
pippo
pippo

Reputation: 35

SQL difference between primary key(x, y) and primary key(x), unique(y)

In SQL what is the difference between this

create table marriage (
    person1 int not null references human on delete cascade,
    person2 int not null references human on delete cascade,
    primary key (person1, person2)
);

and this

create table marriage (
    person1 int not null references human on delete cascade,
    person2 int not null references human on delete cascade,
    primary key (person1),
    unique (person2)
);

And does the table with two primary keys prevent this?

marriage:    

person1 | person2
   1         2
   2         1

Upvotes: 0

Views: 382

Answers (1)

Patrick Hofman
Patrick Hofman

Reputation: 157108

The first index is a unique index on two columns: that means that the combination of both should be unique. The rows 1,2 and 2,1 are not violating the index since the index treats them as a set. 1,2 and 1,3 don't violate the index either.

The second sample contains two indexes and both need to be unique. That means that rows with 1,2 and 1,3 violate the index constraints.

Upvotes: 1

Related Questions