Foreign key

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