How Do You Create Two Tables with a Shared Column between Them (e.g., Have a Foreign Key Constraint)?

Problem scenario
You want to create two tables in a Postgresql database.  You want the tables to share a column to relate to one another.  You want to implement a foreign key constraint to achieve this goal.  Is there an example of SQL statements to do this?

Run these two SQL commands that start with the words "CREATE TABLE":

CREATE TABLE table1 (id integer,
    name    varchar(40),

CREATE TABLE table2 (id int REFERENCES table1,
    name    varchar(40)

The second DDL command (i.e., "CREATE TABLE table2") works because the datatype of id is int.  The datatype has to be the same as the primary key's datatype of table1 the subject of the references keyword.  You'll notice that the keywords "CONSTRAINT" and "FOREIGN KEY" are absent from the commands above.

Leave a comment

Your email address will not be published. Required fields are marked *