Create Foreign Key Relationships

Relational databases are at their most useful when data in one table relates to other data in other tables.

The mechanism we use to enforce that something in cave_id column actually refers back to a row in a cave table is foreign keys.

• Create a hominid table.

  This should have the standard id, created_at, updated_at, and the trigger to keep updated_at updated.

  In addition, we'll say that a hominid has a single cave_id where they reside and a name.

  -- // create_hominid_table
  CREATE TABLE prehistoric.hominid (
      id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
      created_at timestamptz NOT NULL DEFAULT now(),
      updated_at timestamptz NOT NULL DEFAULT now(),
      "name" text,
      cave_id uuid
  );
  
  CREATE TRIGGER set_prehistoric_hominid_updated_at
  BEFORE UPDATE ON prehistoric.hominid
  FOR EACH ROW
  EXECUTE PROCEDURE prehistoric.set_current_timestamp_updated_at();
  
  
  -- //@UNDO
  DROP TABLE prehistoric.hominid;

• Have cave_id be a foreign key

  You want to write out the on update restrict on delete restrict. It's not the default behavior but it's the least potentially problem causing.

  -- // create_hominid_table
  CREATE TABLE prehistoric.hominid (
      id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
      created_at timestamptz NOT NULL DEFAULT now(),
      updated_at timestamptz NOT NULL DEFAULT now(),
      "name" text,
      cave_id uuid REFERENCES prehistoric.cave (id)
          ON UPDATE RESTRICT
          ON DELETE RESTRICT
  );
  
  CREATE TRIGGER set_prehistoric_hominid_updated_at
  BEFORE UPDATE ON prehistoric.hominid
  FOR EACH ROW
  EXECUTE PROCEDURE prehistoric.set_current_timestamp_updated_at();
  
  -- //@UNDO
  DROP TABLE prehistoric.hominid;

• Make indexes on your foreign keys

  I don't have hard data on this, but every time I make a foreign key relationship and don't make indexes that cover the related columns I come to regret it.

  -- // create_hominid_table
  CREATE TABLE prehistoric.hominid (
      id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
      created_at timestamptz NOT NULL DEFAULT now(),
      updated_at timestamptz NOT NULL DEFAULT now(),
      "name" text,
      cave_id uuid REFERENCES prehistoric.cave (id)
          ON UPDATE RESTRICT
          ON DELETE RESTRICT
  );
  
  CREATE TRIGGER set_prehistoric_hominid_updated_at
  BEFORE UPDATE ON prehistoric.hominid
  FOR EACH ROW
  EXECUTE PROCEDURE prehistoric.set_current_timestamp_updated_at();
  
  CREATE INDEX prehistoric_hominid_cave_id_idx
  ON prehistoric.hominid
  USING btree (cave_id);
  
  
  -- //@UNDO
  DROP TABLE prehistoric.hominid;