in my RIM I got the 1215 MySql error.
I know the meaning of the error, that my constraints are wrong. But I can't seem to fix it.
the error is at line 33, the creation of table Poker_event
create table Poker_event
(date_time datetime not null,
min_players int not null,
max_players int not null,
house_number int not null,
postal_code varchar(6) not null,
primary key(date_time),
foreign key(house_number, postal_code) references Location(house_number, postal_code) on delete set null on update cascade);
my code is:
create database FullHouseGr1;
use FullHouseGr1;
create table Player
(player_id int not null,
first_name varchar(20) not null,
surname varchar(20) not null,
addres varchar(40) not null,
postal_code varchar(6) not null,
place varchar(40) not null,
phone_number varchar(20) not null,
email_addres varchar(255) not null,
points int not null,
primary key(player_id));
create table Location
(house_number int not null,
postal_code varchar(6) not null,
capacity int not null,
place varchar(40) not null,
street varchar(40) not null,
primary key(house_number, postal_code));
create table Poker_event
(date_time datetime not null,
min_players int not null,
max_players int not null,
house_number int not null,
postal_code varchar(6) not null,
primary key(date_time),
foreign key(house_number, postal_code) references Location(house_number, postal_code) on delete cascade on update cascade);
create table Tournament
(date_time datetime not null,
prize int not null,
primary key(date_time),
foreign key(date_time) references Poker_event(date_time) on delete no action on update cascade);
create table Tournament_round
(round_nr int not null,
date_time datetime not null,
primary key(date_time, round_nr),
foreign key(date_time) references Tournament(date_time) on delete no action on update cascade);
create table Tournament_table
(winner int not null,
date_time datetime not null,
round_nr int not null,
primary key(winner, date_time, round_nr),
foreign key(date_time) references Tournament(date_time) on delete no action on update cascade,
foreign key(round_nr) references Tournament(round_nr) on delete no action on update cascade);
create table Professional
(p_name varchar(40) not null,
primary key(name));
create table Masterclass
(date_time datetime not null,
min_rating int not null,
name varchar(40) not null,
primary key(date_time),
foreign key(p_name) references Professional(p_name) on delete no action on update cascade,
foreign key(date_time) references Poker_event(date_time) on delete no action on update cascade);
create table Poker_event_player
(date_time datetime not null,
has_payed boolean not null,
player_id int not null,
primary key(date_time, player_id),
foreign key(date_time) references Poker_event(date_time) on delete no action on update cascade,
foreign key(player_id) references Player on delete no action on update cascade);
create table Player_tournament_table
(winner int not null,
date_time datetime not null,
round_nr int not null,
player_id int not null,
primary key(winner, date_time, round_nr, player_id),
foreign key(winner) references Tournament_table on delete no action on update cascade,
foreign key(round_nr) references Tournament_round on delete no action on update cascade,
foreign key(date_time) references Tournament on delete no action on update cascade,
foreign key(player_id) references Player on delete no action on update cascade);
1215 - cannot add foreign key. @blipman I posted a wrong answer you don't need the referencing columns to be indexed (but you should do it tho!), @mopo922 is right.house_number, postal_codeis unique in The Netherlands