SQL Keys in Depth

SQL Keys in Depth is an excellent read if you want to brush up on your knowledge of database keys and how they affect the performance of your application.  For the laziest among you, here are the summary points, based on an extensive research of 60+ articles, StackOverflow questions and IRC discussions:

For each table:

  1. Identify and declare all natural keys.
  2. Create a <table_name>_id surrogate key of type uuid with default value uuid_generate_v1(). You can even mark it as a primary key if you like. Including the table name in this id makes joins simpler. It’s JOIN foo USING (bar_id) vs JOIN foo ON (foo.bar_id = bar.id). Do not expose this key to clients or anywhere outside the database.
  3. For “join tables” declare all foreign key columns as a single composite primary key.
  4. Add an artificial key if desired for use in a URL or anywhere else you want to share a reference to a row. Use a Feistel cipher or pg_hashids to conceal auto-incrementing integers.
  5. Mark foreign keys to surrogate UUIDs as ON UPDATE RESTRICT and to external artificial keys as ON UPDATE CASCADE. Use your own judgement for natural keys.

Leave a Comment