SQL Relationships

When we talk about relationships between tables, we use two important terms:

• Primary key: A column or group of columns that uniquely identifies a row. Every table should have a primary key; a table cannot have more than one primary key.

• Foreign key: A column or group of columns in one table whose values must have matching values in the primary key of another (or the same) table. A foreign key is said to reference its primary key. Foreign keys are a mechanism for maintaining data integrity.

For example, we have two tables:

Owners

CREATE TABLE owners (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  address TEXT
);

Pets

CREATE TABLE pets (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  owner_id INT,
  CONSTRAINT fk_pets_owners
    FOREIGN KEY(owner_id)
    REFERENCES owners(id)

);

The id is the primary key in the owners table. The foreign key (owner_id) in the pets table points to owner's primary key.