Redshift Foreign Key Constraint and Syntax

  • Post author:
  • Post last modified:September 19, 2022
  • Post category:Redshift
  • Reading time:3 mins read

Redshift foreign key constraint is informational only; they are not enforced by Amazon Redshift. Amazon data warehouse appliance supports referential integrity constraints such as Redshift primary key, foreign key, and unique keys as part of SQL-92 standard requirement.

You can create foreign key constraint while creating tables in Redshift database but it will not be enforced while loading Redshift tables. Redshift query planner uses these constraints to create better query execution plan.

Read:

Redshift Foreign Key Constraint Syntax

You can mention the foreign key constraint when creating table either on column level or on table level:

create table ForeignKey_demo (
 col1 int NOT NULL [PRIMARY KEY]
 ,col2 date
 ,col3 varchar(60 ) 
, foreign key(col1) references PrimaryKey_demo(col1)
) distkey(col1)
compound sortkey(col1, col2);

Now let us test if the Redshift foreign key is enforced. In Redshift, foreign key constraint is not enforced. You can insert values in Redshift table that are not present in the parent table or reference table.

The parent table contains:

COL1 COL2 COL3 
1 2016-10-22 test1 
1 2016-10-22 test1

Below statement will execute perfectly;

insert into ForeignKey_demo values (3,123,'test2'); -- This data is not present in parent table

Alter Redshift Table to add Foreign Key Constraint Syntax

You can add the Redshift foreign key constraint by altering the tables using ALTER TABLE command.

Below is the syntax and example:

ALTER TABLE ForeignKey_demo ADD CONSTRAINT FK_1 FOREIGN KEY (col1) references PrimaryKey_demo(col1);

Related Articles,

Tags: