mysql table with multiple primary key compound constraint

Question

I'm sorry if the title isn't exactly.. useful, but I wasn't sure how to explain my issue in a title.

So basically, I want to create a table like that :

reservation

   day
   room
   id_client
   [other_stuff]

For a given day+room, you can get the id_client + everything else. And also for a given id_client + day you can get the room + other stuff.

I don't exactly understand how am I supposed to say that the compound day+room must be unique AND the compound day+id_client must also be unique. I really need both of those constraint in my database.

Anyone has an idea ?

Thanks.

Answer 1

Define one combination an PRIMARY KEY and the other as UNIQUE key:

CREATE TABLE reservation
(   day
,   room
,   id_client
,   [other_stuff]
, PRIMARY KEY (day, room)
, UNIQUE KEY (id_client, day)
) ;

or the other way around:

CREATE TABLE reservation
(   day
,   room
,   id_client
,   [other_stuff]
, PRIMARY KEY (id_client, day) 
, UNIQUE KEY (day, room)
) ;

---

Or, if you already have another Primary Key, make them both unique:

CREATE TABLE reservation
(   reservation_id
,   day
,   room
,   id_client
,   [other_stuff]
, PRIMARY KEY (reservation_id)
, UNIQUE KEY (id_client, day) 
, UNIQUE KEY (day, room)
) ;

Answer 2

-- in MySQL

drop database if exists mydatabase;
create database mydatabase;

use mydatabase;

drop table if exists client;
create table client
(
    id int unsigned not null auto_increment,
    name varchar(45) not null,
    primary key (id)
)engine=InnoDB default charset=utf8;


drop table if exists room;
create table room
(
    id int unsigned not null auto_increment,
    label varchar(45) not null,
    primary key (id)
)engine=InnoDB default charset=utf8;



drop table if exists reservation;
create table reservation
(
    id int unsigned not null auto_increment,
    id_room int unsigned,
    id_client int unsigned,
    day date,
    unique(day, id_room),
    unique(day, id_client),
    foreign key (id_room) references room(id),
    foreign key (id_client) references client(id),
    primary key (id)
)engine=InnoDB default charset=utf8;