How to use Redshift Foreign key Constraint? Its Syntax

In my other Redshift related article, we have discussed how to define a primary key NOT NULL and Unique key constraints in AWS Redshift. In this article, we will check how to use Redshift FOREIGN KEY constraint with its syntax and examples.

Redshift Foreign Key Constraint

Amazon AWS Redshift is an analytical columnar cloud database. It is mainly used for analyzing your data to create a key KPI to make critical business decisions.

The Redshift cloud database is based on PostgreSQL 8.x and many transactional database features such as table constraints are not supported. You can define a foreign key, but it is informational only; They are not enforced by Amazon Redshift. The Amazon Redshift data warehouse appliance supports referential integrity constraints such as primary key, foreign key, and unique keys as part of SQL-92 standard requirement.

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

Read:

Redshift Foreign Key Constraint Syntax

There are different methods that you can use to add foreign key constraint to the table.

Column level Foreign key – Add foreign key constraint columns while creating table.
Alter Table to Add Foreign key Column – You can also use alter table command to add foreign key constraint after table is created.

You can add a FOREIGN KEY constraint while creating a 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 check if AWS Redshift foreign key is enforced. You can insert values in Redshift table that are not present in the parent table or reference table.

For examples, the parent table contains:

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

The following statement will execute perfectly fine on Redshift;

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

Above insert statement will successfully insert the data into a table. Hence the foreign key is not enforced on the Redshift table. Amazon Redshift supports defining and maintaining constraints, but does not enforce them, except for NOT NULL constraints, which are always enforced.

Alter Redshift Table to add Foreign Key Constraint

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

Following is the syntax and example:

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