1) NOT ABLE to create a foreign keys ON DELETE SET NULL, or ON DELETE CASCADE 2) Not able ON DELETE CASCADE

0
votes

I am not able to add foreign key constraint ON DELETE CASCADE ON UPDATE CASCADE.

I use two simple tables.

TAB1 has 2 columns: ID int(10) unsigned NOT NULL AUTOINCREMENT, and data int(10) unsigned NOT NULL.

TAB2 has 2 columns: ID int(10) unsigned NOT NULL AUTOINCREMENT, and FK int(10) unsigned NOT NULL.

I can create foreign key fron column FK in table TAB2 on column ID in TAB1 with "ON DELETE RESTRICT".

But i am not able to create such a key with "ON DELETE CASCADE" or "ON DELETE SET NULL". The errors is "#1215 - Cannot add foreign key constraint".

I have tried with signed, NULL, int(4)... ets. Do not unserstand where is the error.

The only FOREIGN KEY(tcfkowner), which i managed to create with CASCADE a month before is in TABLE tcalendar ADD REFERENCES tuser (tcid).

I unserstand that on DELETE CASCADE means that if i delete user, the row in calendar pointing to the user will also be deleted.

Instead the foreign key seems restricts deletion of his parent: i am not able to delete any row in TABLE tuser which is pointed by FOREIGN KEY(tcfkowner) from TABLE tcalendar . The error message:

" MySQL said: Documentation

1451 - Cannot delete or update a parent row: a foreign key constraint fails (gintare_calendar.tcalendar, CONSTRAINT tcalendar_ibfk_1 FOREIGN KEY (tcfkowner) REFERENCES tuser (tcid)) " .

2
mysqlforeign-keyscascade

2 Answers

0
votes

Both fields - in master and in detail tables, must be the same; check it firstly.

Try this script to create tables and add foreign key -

CREATE TABLE tab1 (
  id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  data int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB;

CREATE TABLE tab2 (
  id int(10) UNSIGNED NOT NULL,
  fk int(10) UNSIGNED NOT NULL
)
ENGINE = INNODB;

ALTER TABLE tab2
  ADD CONSTRAINT FK_tab2_tab1_id FOREIGN KEY (fk)
    REFERENCES tab1(id) ON DELETE CASCADE ON UPDATE RESTRICT;

Or just try this one -

CREATE TABLE tab1 (
  id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  data int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB;

CREATE TABLE tab2 (
  id int(10) UNSIGNED NOT NULL,
  fk int(10) UNSIGNED NOT NULL,
  CONSTRAINT FK_tab2_tab1_id FOREIGN KEY (fk)
  REFERENCES tab1 (id) ON DELETE CASCADE ON UPDATE RESTRICT
)
ENGINE = INNODB;
0
votes

You cant add ON DELETE SET NULL because setting field in TAB1 is set not null. You can fix this by either changing your cascade or setting field to allow null value . Hopefully help and sorry for my english