Martin at 09:53, 8 June 2022

2022-06-08T09:53:23Z

← Older revision		Revision as of 09:53, 8 June 2022
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]]

FrankHeikens: 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..."

2022-06-08T07:32:55Z

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..."

New page

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

Foreign key - Revision history

Martin at 09:53, 8 June 2022

FrankHeikens: 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..."