CODEWITHSUNDEEP
mysqlsqlforeign-keyskey
Matt Goodrich
Matt Goodrich

Reputation: 5095

Foreign Key Referencing Same Table

I've searched well and can't find an answer explaining the problem well. Here is a simplified example of my issue. Let's say I have a table test with two fields first and second. I make first the primary key and want second to reference first and cascade on update. This will allow me to make a list of rows, knowing which row comes after any given row, unless the 'head'.

Creating the table works fine, however when I try to add a row it complains that the foreign key constraint with cascading update failed. This makes sense, as I left second as null. Therefore, I thought I could insert two rows and then add the foreign key:

  1. first: a second: b
  2. first: b second: a

This was successful. I then tried updating row 1 to have a first value of c. However, this didn't work, stating the foreign key failed. Why? Shouldn't it just become the following?

  1. first: c second: b
  2. first: b second: c

My only hunch is the existence of circular updates with the update to row 1 cascading to row 2, cascading back to row 1, but this shouldn't happen! There is nothing referencing second, so the update should just cascade to row 2 and stop. Please help, this is driving me crazy.

Edit: As requested, here's my show create table test.

CREATE TABLE `test` (
 `first` varchar(32) NOT NULL,
 `second` varchar(32) NOT NULL,
PRIMARY KEY (`first`),
KEY `fk` (`second`),
CONSTRAINT `fk` FOREIGN KEY (`second`) REFERENCES `test` (`first`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Upvotes: 5

Views: 3578

Answers (1)

George G
George G

Reputation: 7695

I think problem is in the structure of columns:

  • primary key can't be null
  • and your referencing column has allowed null value, (not allowed when referenced column is not null)

So make sure you have both columns with same data type, same charset, not null both.

Update: problem is that foreign key and referencing key is in the same table, I've tested with different table and it worked.

Upvotes: 1

Related Questions