Pivotal Greenplum: 4.3.x
OS: RHEL 6.x
A user is trying to create an external table after dropping it, but it fails and returns the following error:
20170808:12:52:45:047869 gptransfer:mdw:gpadmin-[DEBUG]:-[worker0] finished cmd: transfer of analyticsdb.heuristics.executions cmdStr='None' had result: cmd had rc=1 completed=False halted=False
stdout='error 'ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "document_tags" references "executions".
According to the doc: https://gpdb.docs.pivotal.io/4330/ref_guide/sql_commands/CREATE_TABLE.html
The REFERENCES and FOREIGN KEY clauses specify referential integrity constraints (foreign key constraints).
Greenplum accepts referential integrity constraints as specified in PostgreSQL syntax but does not enforce them.
This is the reason when creating a table with the reference key would get the message: the
"WARNING: Referential integrity (FOREIGN KEY) constraints are not supported in Greenplum Database, will not be enforced."
The reference key would be created but it won't check the integrity from the reference table. Meanwhile, it would check the FK constraint when you try to truncate/drop the table and got the message
"ERROR: cannot truncate a table referenced in a foreign key constraint".
Step1. Create sample table and insert data, then you can see the data has been inserted successfully and no referential integrity check:
gpadmin=# CREATE TABLE cities (
gpadmin(# city varchar(80) primary key,
gpadmin(# location point
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "cities_pkey" for table "cities"
gpadmin=# CREATE TABLE weather (
gpadmin(# city varchar(80) references cities(city),
gpadmin(# temp_lo int,
gpadmin(# temp_hi int,
gpadmin(# prcp real,
gpadmin(# date date
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'city' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
WARNING: Referential integrity (FOREIGN KEY) constraints are not supported in Greenplum Database, will not be enforced.
gpadmin=# INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
INSERT 0 1
gpadmin=# select * from cities;
city | location
gpadmin=# select * from weather;
city | temp_lo | temp_hi | prcp | date
Berkeley | 45 | 53 | 0 | 1994-11-28
Step2. Truncate table "cities" and got below error message:
gpadmin=# truncate table cities;
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "weather" references "cities".
HINT: Truncate table "weather" at the same time, or use TRUNCATE ... CASCADE.
Step3. Alter table to drop the constraint then the table "cities" can be truncated:
gpadmin=# \d+ cities
Column | Type | Modifiers | Storage | Description
city | character varying(80) | not null | extended |
location | point | | plain |
"cities_pkey" PRIMARY KEY, btree (city)
TABLE "weather" CONSTRAINT "weather_city_fkey" FOREIGN KEY (city) REFERENCES cities(city)
Has OIDs: no
Distributed by: (city)
gpadmin=# alter table weather drop constraint weather_city_fkey;
gpadmin=# truncate table cities;
An alternative solution is that you can follow the HINT to use "TRUNCATE ... CASCADE" to truncate table:
gpadmin=# truncate table cities cascade;
NOTICE: truncate cascades to table "weather"
NOTICE: truncate cascades to table "weather" (seg1 gpdb-sandbox.localdomain:40001 pid=87916)
NOTICE: truncate cascades to table "weather" (seg0 gpdb-sandbox.localdomain:40000 pid=87914)
For user's gptransfer case, the resolution is to drop the constraint key by below command:
alter table <table_name> drop constraint <constraint_key_name>;
Generally, the referential integrity should be support in the RDBMS. But this is a big difference in GPDB compare to Postgres and referential integrity (FOREIGN KEY) constraints are supported in Postgres. Sample as below:
fzou=# CREATE TABLE cities ( fzou(# city varchar(80) primary key, fzou(# location point fzou(# ); CREATE TABLE fzou=# fzou=# CREATE TABLE weather ( fzou(# city varchar(80) references cities(city), fzou(# temp_lo int, fzou(# temp_hi int, fzou(# prcp real, fzou(# date date fzou(# ); CREATE TABLE fzou=# INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28'); ERROR: insert or update on table "weather" violates foreign key constraint "weather_city_fkey" DETAIL: Key (city)=(Berkeley) is not present in table "cities".