Skip to content

Instantly share code, notes, and snippets.

@acdha

acdha/circular.sql

Created Mar 16, 2011
Embed
What would you like to do?
Example for Django #3615
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`))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment