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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
|
-- Test basic TRUNCATE functionality.
CREATE TABLE truncate_a (col1 integer primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "truncate_a_pkey" for table "truncate_a"
INSERT INTO truncate_a VALUES (1);
INSERT INTO truncate_a VALUES (2);
SELECT * FROM truncate_a;
col1
------
1
2
(2 rows)
-- Roll truncate back
BEGIN;
TRUNCATE truncate_a;
ROLLBACK;
SELECT * FROM truncate_a;
col1
------
1
2
(2 rows)
-- Commit the truncate this time
BEGIN;
TRUNCATE truncate_a;
COMMIT;
SELECT * FROM truncate_a;
col1
------
(0 rows)
-- Test foreign-key checks
CREATE TABLE trunc_b (a int REFERENCES truncate_a);
CREATE TABLE trunc_c (a serial PRIMARY KEY);
NOTICE: CREATE TABLE will create implicit sequence "trunc_c_a_seq" for serial column "trunc_c.a"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "trunc_c_pkey" for table "trunc_c"
CREATE TABLE trunc_d (a int REFERENCES trunc_c);
CREATE TABLE trunc_e (a int REFERENCES truncate_a, b int REFERENCES trunc_c);
TRUNCATE TABLE truncate_a; -- fail
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "trunc_b" references "truncate_a".
HINT: Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
TRUNCATE TABLE truncate_a,trunc_b; -- fail
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "trunc_e" references "truncate_a".
HINT: Truncate table "trunc_e" at the same time, or use TRUNCATE ... CASCADE.
TRUNCATE TABLE truncate_a,trunc_b,trunc_e; -- ok
TRUNCATE TABLE truncate_a,trunc_e; -- fail
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "trunc_b" references "truncate_a".
HINT: Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
TRUNCATE TABLE trunc_c; -- fail
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "trunc_d" references "trunc_c".
HINT: Truncate table "trunc_d" at the same time, or use TRUNCATE ... CASCADE.
TRUNCATE TABLE trunc_c,trunc_d; -- fail
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "trunc_e" references "trunc_c".
HINT: Truncate table "trunc_e" at the same time, or use TRUNCATE ... CASCADE.
TRUNCATE TABLE trunc_c,trunc_d,trunc_e; -- ok
TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a; -- fail
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "trunc_b" references "truncate_a".
HINT: Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a,trunc_b; -- ok
TRUNCATE TABLE truncate_a RESTRICT; -- fail
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "trunc_b" references "truncate_a".
HINT: Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
TRUNCATE TABLE truncate_a CASCADE; -- ok
NOTICE: truncate cascades to table "trunc_b"
NOTICE: truncate cascades to table "trunc_e"
-- circular references
ALTER TABLE truncate_a ADD FOREIGN KEY (col1) REFERENCES trunc_c;
-- Add some data to verify that truncating actually works ...
INSERT INTO trunc_c VALUES (1);
INSERT INTO truncate_a VALUES (1);
INSERT INTO trunc_b VALUES (1);
INSERT INTO trunc_d VALUES (1);
INSERT INTO trunc_e VALUES (1,1);
TRUNCATE TABLE trunc_c;
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "truncate_a" references "trunc_c".
HINT: Truncate table "truncate_a" at the same time, or use TRUNCATE ... CASCADE.
TRUNCATE TABLE trunc_c,truncate_a;
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "trunc_d" references "trunc_c".
HINT: Truncate table "trunc_d" at the same time, or use TRUNCATE ... CASCADE.
TRUNCATE TABLE trunc_c,truncate_a,trunc_d;
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "trunc_e" references "trunc_c".
HINT: Truncate table "trunc_e" at the same time, or use TRUNCATE ... CASCADE.
TRUNCATE TABLE trunc_c,truncate_a,trunc_d,trunc_e;
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "trunc_b" references "truncate_a".
HINT: Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
TRUNCATE TABLE trunc_c,truncate_a,trunc_d,trunc_e,trunc_b;
-- Verify that truncating did actually work
SELECT * FROM truncate_a
UNION ALL
SELECT * FROM trunc_c
UNION ALL
SELECT * FROM trunc_b
UNION ALL
SELECT * FROM trunc_d;
col1
------
(0 rows)
SELECT * FROM trunc_e;
a | b
---+---
(0 rows)
-- Add data again to test TRUNCATE ... CASCADE
INSERT INTO trunc_c VALUES (1);
INSERT INTO truncate_a VALUES (1);
INSERT INTO trunc_b VALUES (1);
INSERT INTO trunc_d VALUES (1);
INSERT INTO trunc_e VALUES (1,1);
TRUNCATE TABLE trunc_c CASCADE; -- ok
NOTICE: truncate cascades to table "truncate_a"
NOTICE: truncate cascades to table "trunc_d"
NOTICE: truncate cascades to table "trunc_e"
NOTICE: truncate cascades to table "trunc_b"
SELECT * FROM truncate_a
UNION ALL
SELECT * FROM trunc_c
UNION ALL
SELECT * FROM trunc_b
UNION ALL
SELECT * FROM trunc_d;
col1
------
(0 rows)
SELECT * FROM trunc_e;
a | b
---+---
(0 rows)
DROP TABLE truncate_a,trunc_c,trunc_b,trunc_d,trunc_e CASCADE;
NOTICE: drop cascades to constraint trunc_e_a_fkey on table trunc_e
NOTICE: drop cascades to constraint trunc_b_a_fkey on table trunc_b
NOTICE: drop cascades to constraint trunc_e_b_fkey on table trunc_e
NOTICE: drop cascades to constraint trunc_d_a_fkey on table trunc_d
|
