Example for Django #3615

circular.sql

DROP TABLE IF EXISTS circular;

CREATE TABLE circular (
    id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
    parent_id INTEGER NULL,
    CONSTRAINT `circular_ibfk_1` FOREIGN KEY (parent_id) REFERENCES circular(id)
) ENGINE=INNODB;


-- Should fail:
INSERT INTO circular (id, parent_id) values (1, 2);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`circular`, CONSTRAINT `circular_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `circular` (`id`))

ALTER TABLE circular DROP FOREIGN KEY `circular_ibfk_1`;

INSERT INTO circular (id, parent_id) values (1, 2);

-- Should fail:
ALTER TABLE circular ADD CONSTRAINT `circular_ibfk_1` FOREIGN KEY (parent_id) REFERENCES circular(id);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.<result 2 when explaining filename '#sql-7b9a_10c1'>, CONSTRAINT `circular_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `circular` (`id`))

DELETE FROM circular;
ALTER TABLE circular ADD CONSTRAINT `circular_ibfk_1` FOREIGN KEY (parent_id) REFERENCES circular(id);

-- Should fail:
INSERT INTO circular (id, parent_id) values (1, 2);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`circular`, CONSTRAINT `circular_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `circular` (`id`))