Editing 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