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?
Solution
Run these two SQL commands that start with the words "CREATE TABLE":
CREATE TABLE table1 (id integer,
name varchar(40),
PRIMARY KEY(id)
);
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.