Difference between revisions of "Foreign key"
FrankHeikens (talk | contribs) (Created page with "Aanmaken van foreign keys, zonder actions, en later het wijzigen van de foreign key om wél een action in the stellen: CREATE TABLE foo(id_foo INT PRIMARY KEY, content TEX...") |
|||
Line 40: | Line 40: | ||
SELECT * FROM foobar WHERE id_foo = 1; -- that one is gone as well |
SELECT * FROM foobar WHERE id_foo = 1; -- that one is gone as well |
||
+ | <hr/> |
||
+ | Terug naar: [[Standaard opzetjes]] |
Latest revision as of 09:53, 8 June 2022
Aanmaken van foreign keys, zonder actions, en later het wijzigen van de foreign key om wél een action in the stellen:
CREATE TABLE foo(id_foo INT PRIMARY KEY, content TEXT);
CREATE TABLE bar(id_bar INT PRIMARY KEY, content TEXT);
CREATE TABLE foobar( id_foo INT , id_bar INT , PRIMARY KEY (id_foo,id_bar) , CONSTRAINT fk_id_foo FOREIGN KEY (id_foo) REFERENCES foo(id_foo) -- no action defined, falls back to DEFAULT , CONSTRAINT fk_id_bar FOREIGN KEY (id_bar) REFERENCES bar(id_bar) -- no action defined, falls back to DEFAULT );
-- some data in all tables:
INSERT INTO foo(id_foo, content) VALUES(1, 'foo content'); INSERT INTO bar(id_bar, content) VALUES(1, 'bar content'); INSERT INTO foobar(id_foo, id_bar) VALUES(1,1);
-- now try to delete some data from "foo":
DELETE FROM foo WHERE id_foo = 1; -- fails!
-- And this is why:
SELECT * FROM foobar WHERE id_foo = 1; -- this record is linking to "foo"
BEGIN; -- always within a single transaction to avoid data corruption ALTER TABLE foobar DROP CONSTRAINT fk_id_foo; ALTER TABLE foobar -- what table? ADD CONSTRAINT fk_id_foo -- what constraint name? FOREIGN KEY (id_foo) -- what column for this table? REFERENCES foo(id_foo) -- referencing what table? (in what schema?) ON DELETE CASCADE -- what to do when a delete happens? ON UPDATE CASCADE; -- what to do when an update happens? COMMIT;
DELETE FROM foo WHERE id_foo = 1; -- works!
SELECT * FROM foobar WHERE id_foo = 1; -- that one is gone as well
Terug naar: Standaard opzetjes