0

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);
10
  • Where is line 33? Also, what is the exact error posted by MySQL? Commented Jan 7, 2015 at 18:41
  • @BuhakeSindi The exact error message is 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. Commented Jan 7, 2015 at 18:52
  • @DanFromGermany that error is partially incomplete. MySQL gives a description that resulted to the cause of the error. Commented Jan 7, 2015 at 18:56
  • @DanFromGermany I may not from my teachers. because... this would result in unused data. house_number, postal_code is unique in The Netherlands Commented Jan 7, 2015 at 19:37
  • Since this question is old and you had already accepted an answer (which you seem to have un-accepted now?), you might want to ask a new question for your new issue. It's hard to sift through what has changed. Commented Jan 7, 2015 at 19:41

1 Answer 1

4

You're saying ON DELETE SET NULL but both of the fields in question are not nullable. Try ON DELETE CASCADE instead.

You can also check to make sure both tables are using the InnoDB engine.

Sign up to request clarification or add additional context in comments.

5 Comments

And what do I need to use to do delete the row on ON DELETE? I'm batteling verry hard with ON DELETE and ON UPDATE
@blipman17 can you just replace on delete set null on update cascade with ON DELETE CASCADE ON UPDATE CASCADE?
That would delete Poker_event's
@blipman17, how bout going a completely different structure, using real ID's.. what if someone changes his/her postal code?
@DanFromGermany, I use player_id for people and use house_number, postal_codefor places, since that's unique in The Netherlands

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.