1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
|
CREATE EXTENSION refint;
create table pkeys (pkey1 int4 not null, pkey2 text not null);
create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
create index fkeys_i on fkeys (fkey1, fkey2);
create index fkeys2_i on fkeys2 (fkey21, fkey22);
create index fkeys2p_i on fkeys2 (pkey23);
insert into pkeys values (10, '1');
insert into pkeys values (20, '2');
insert into pkeys values (30, '3');
insert into pkeys values (40, '4');
insert into pkeys values (50, '5');
insert into pkeys values (60, '6');
create unique index pkeys_i on pkeys (pkey1, pkey2);
--
-- For fkeys:
-- (fkey1, fkey2) --> pkeys (pkey1, pkey2)
-- (fkey3) --> fkeys2 (pkey23)
--
create trigger check_fkeys_pkey_exist
after insert or update on fkeys
for each row
execute function
check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
create trigger check_fkeys_pkey2_exist
after insert or update on fkeys
for each row
execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23');
--
-- For fkeys2:
-- (fkey21, fkey22) --> pkeys (pkey1, pkey2)
--
create trigger check_fkeys2_pkey_exist
after insert or update on fkeys2
for each row
execute procedure
check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
--
-- For pkeys:
-- ON DELETE/UPDATE (pkey1, pkey2) CASCADE:
-- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22)
--
create trigger check_pkeys_fkey_cascade
after delete or update on pkeys
for each row
execute procedure
check_foreign_key (2, 'cascade', 'pkey1', 'pkey2',
'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22');
--
-- For fkeys2:
-- ON DELETE/UPDATE (pkey23) RESTRICT:
-- fkeys (fkey3)
--
create trigger check_fkeys2_fkey_restrict
after delete or update on fkeys2
for each row
execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3');
insert into fkeys2 values (10, '1', 1);
insert into fkeys2 values (30, '3', 2);
insert into fkeys2 values (40, '4', 5);
insert into fkeys2 values (50, '5', 3);
-- no key in pkeys
insert into fkeys2 values (70, '5', 3);
ERROR: tuple references non-existent key
DETAIL: Trigger "check_fkeys2_pkey_exist" found tuple referencing non-existent key in "pkeys".
insert into fkeys values (10, '1', 2);
insert into fkeys values (30, '3', 3);
insert into fkeys values (40, '4', 2);
insert into fkeys values (50, '5', 2);
-- no key in pkeys
insert into fkeys values (70, '5', 1);
ERROR: tuple references non-existent key
DETAIL: Trigger "check_fkeys_pkey_exist" found tuple referencing non-existent key in "pkeys".
-- no key in fkeys2
insert into fkeys values (60, '6', 4);
ERROR: tuple references non-existent key
DETAIL: Trigger "check_fkeys_pkey2_exist" found tuple referencing non-existent key in "fkeys2".
delete from pkeys where pkey1 = 30 and pkey2 = '3';
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys"
CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 "
delete from pkeys where pkey1 = 40 and pkey2 = '4';
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are updated
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are updated
update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
ERROR: duplicate key value violates unique constraint "pkeys_i"
DETAIL: Key (pkey1, pkey2)=(7, 70) already exists.
SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
action_order, action_condition, action_orientation, action_timing,
action_reference_old_table, action_reference_new_table
FROM information_schema.triggers
WHERE event_object_table in ('pkeys', 'fkeys', 'fkeys2')
ORDER BY trigger_name COLLATE "C", 2;
trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table
----------------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+----------------------------
check_fkeys2_fkey_restrict | DELETE | public | fkeys2 | 1 | | ROW | AFTER | |
check_fkeys2_fkey_restrict | UPDATE | public | fkeys2 | 1 | | ROW | AFTER | |
check_fkeys2_pkey_exist | INSERT | public | fkeys2 | 1 | | ROW | AFTER | |
check_fkeys2_pkey_exist | UPDATE | public | fkeys2 | 2 | | ROW | AFTER | |
check_fkeys_pkey2_exist | INSERT | public | fkeys | 1 | | ROW | AFTER | |
check_fkeys_pkey2_exist | UPDATE | public | fkeys | 1 | | ROW | AFTER | |
check_fkeys_pkey_exist | INSERT | public | fkeys | 2 | | ROW | AFTER | |
check_fkeys_pkey_exist | UPDATE | public | fkeys | 2 | | ROW | AFTER | |
check_pkeys_fkey_cascade | DELETE | public | pkeys | 1 | | ROW | AFTER | |
check_pkeys_fkey_cascade | UPDATE | public | pkeys | 1 | | ROW | AFTER | |
(10 rows)
DROP TABLE pkeys;
DROP TABLE fkeys;
DROP TABLE fkeys2;
|
