Keys

PRIMARY KEY

The PRIMARY KEY constraint, which marks the column that uniquely identifies each row, is a combination of UNIQUE and NOT NULL constraints. > CREATE TABLE primarytest (col INTEGER PRIMARY KEY); > \d primarytest Table "primarytest" Attribute | Type | Modifier -----------+---------+---------- col | integer | not null Index: primarytest_pkey Notice: An index is created automatically, and the column is defined as NOT NULL.

As with UNIQUE, a multicolumn PRIMARY KEY constraint must be specified on a separate line:

> CREATE TABLE primarytest2 ( col1 INTEGER, col2 INTEGER, PRIMARY KEY(col1, col2) ); A table cannot have more than one PRIMARY KEY specification.

Foreign Key: REFERENCES

Foreign key constraints are created by using REFERENCES to refer to the primary key of another table. Foreign keys link the tables together and prevent the insertion or updating of invalid data.
This is called maintaining the referential integrity of your data.

Example: You want to make sure that no one can insert rows in the weather table that do not have a matching entry in the cities table:

CREATE TABLE cities ( city varchar(80) primary key, location point ); CREATE TABLE weather ( city varchar(80) references cities, temp_lo int, temp_hi int, date date ); Now you cannot insert an invalid record, i.e, a city not in the cities table: INSERT INTO weather VALUES ('Berkeley', 45, 53, '1994-11-28'); ERROR: <unnamed> referential integrity violation - key referenced from weather not found in cities In relations connecting two tables you need a multi-attribute foreign key, that references two different tables: CREATE TABLE assign ( project INT REFERENCES projects, stud INT REFERENCES stud, percentage INT);