test.sql

text/plain

Download

CREATE FUNCTION check_primary_key ()
        RETURNS trigger
        AS '$libdir/refint.so'
        LANGUAGE C;

CREATE FUNCTION check_foreign_key ()
	RETURNS trigger
	AS '$libdir/refint.so'
	LANGUAGE C;

create table pkeys (pkey1 int4 not null, pkey2 text not null);
create table fkeys (fkey1 int4, fkey2 text);

create index fkeys_i on fkeys (fkey1, fkey2);
insert into pkeys values (10, '1');
insert into pkeys values (20, '2');
create unique index pkeys_i on pkeys (pkey1, pkey2);

create trigger check_fkeys_pkey_exist
	before insert or update on fkeys
	for each row
	execute function
	check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');

create trigger check_pkeys_fkey_cascade
	before delete or update on pkeys
	for each row
	execute procedure
	check_foreign_key (1, 'cascade', 'pkey1', 'pkey2', 'fkeys', 'fkey1', 'fkey2');

insert into fkeys values (10, '1');
insert into fkeys values (20, '2');

select * from fkeys;

delete from pkeys where pkey1 = 10 and pkey2 = '1';
-- cascade drop. Good. -- 
select * from fkeys;

update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 20 and pkey2 = '2';

-- cascade drop. Bad. -- 
select * from fkeys;