------
(0 rows)
--- Test foreign constraint check
-CREATE TABLE truncate_b(col1 integer references truncate_a);
+-- 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" via foreign key constraint "trunc_b_a_fkey".
+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" via foreign key constraint "trunc_e_a_fkey".
+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" via foreign key constraint "trunc_b_a_fkey".
+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" via foreign key constraint "trunc_d_a_fkey".
+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" via foreign key constraint "trunc_e_b_fkey".
+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" via foreign key constraint "trunc_b_a_fkey".
+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" via foreign key constraint "trunc_b_a_fkey".
+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);
-SELECT * FROM truncate_a;
+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 "trunc_d" references "trunc_c" via foreign key constraint "trunc_d_a_fkey".
+HINT: Truncate table "trunc_d" at the same time, or use TRUNCATE ... CASCADE.
+TRUNCATE TABLE trunc_c,trunc_d;
+ERROR: cannot truncate a table referenced in a foreign key constraint
+DETAIL: Table "trunc_e" references "trunc_c" via foreign key constraint "trunc_e_b_fkey".
+HINT: Truncate table "trunc_e" at the same time, or use TRUNCATE ... CASCADE.
+TRUNCATE TABLE trunc_c,trunc_d,trunc_e;
+ERROR: cannot truncate a table referenced in a foreign key constraint
+DETAIL: Table "truncate_a" references "trunc_c" via foreign key constraint "truncate_a_col1_fkey".
+HINT: Truncate table "truncate_a" at the same time, or use TRUNCATE ... CASCADE.
+TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a;
+ERROR: cannot truncate a table referenced in a foreign key constraint
+DETAIL: Table "trunc_b" references "truncate_a" via foreign key constraint "trunc_b_a_fkey".
+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;
+-- 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
------
- 1
-(1 row)
+(0 rows)
-TRUNCATE truncate_a;
-ERROR: cannot truncate a table referenced in a foreign key constraint
-DETAIL: Table "truncate_b" references "truncate_a" via foreign key constraint "truncate_b_col1_fkey".
-SELECT * FROM truncate_a;
+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 "trunc_d"
+NOTICE: truncate cascades to table "trunc_e"
+NOTICE: truncate cascades to table "truncate_a"
+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
------
- 1
-(1 row)
+(0 rows)
+
+SELECT * FROM trunc_e;
+ a | b
+---+---
+(0 rows)
-DROP TABLE truncate_b;
-DROP TABLE truncate_a;
+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
projects / postgresql / blobdiff