{"id":28347,"date":"2018-01-22T15:44:03","date_gmt":"2018-01-22T13:44:03","guid":{"rendered":"https:\/\/mamchenkov.net\/wordpress\/?p=28347"},"modified":"2018-01-22T15:44:03","modified_gmt":"2018-01-22T13:44:03","slug":"sql-keys-in-depth","status":"publish","type":"post","link":"https:\/\/mamchenkov.net\/wordpress\/2018\/01\/22\/sql-keys-in-depth\/","title":{"rendered":"SQL Keys in Depth"},"content":{"rendered":"\n

SQL Keys in Depth<\/a> 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.\u00a0 For the laziest among you, here are the summary points, based on an extensive research of 60+ articles, StackOverflow questions and IRC discussions:<\/p>\n

For each table:<\/p>\n

    \n
  1. Identify and declare all natural keys.<\/li>\n
  2. Create a\u00a0<table_name>_id<\/code>\u00a0surrogate key of type\u00a0uuid<\/code>\u00a0with default value\u00a0uuid_generate_v1()<\/code>. You can even mark it as a primary key if you like. Including the table name in this id makes joins simpler. It\u2019s\u00a0JOIN foo USING (bar_id)<\/code>\u00a0vs\u00a0JOIN foo ON (foo.bar_id = bar.id)<\/code>. Do not expose this key to clients or anywhere outside the database.<\/li>\n
  3. For \u201cjoin tables\u201d declare all foreign key columns as a single composite primary key.<\/li>\n
  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.<\/li>\n
  5. Mark foreign keys to surrogate UUIDs as\u00a0ON UPDATE RESTRICT<\/code>\u00a0and to external artificial keys as\u00a0ON UPDATE CASCADE<\/code>. Use your own judgement for natural keys.<\/li>\n<\/ol>\n<\/blockquote>\n\n","protected":false},"excerpt":{"rendered":"\n

    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.\u00a0 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: … Continue reading SQL Keys in Depth<\/span><\/a><\/p>\n\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"SQL Keys in Depth #WebDev #databases #performance","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false,"_links_to":"","_links_to_target":""},"categories":[1,18,62],"tags":[1192,1559,1057,1330],"keyring_services":[],"class_list":["post-28347","post","type-post","status-publish","format-standard","hentry","category-general","category-programming","category-technology","tag-computer-science","tag-databases","tag-performance","tag-web-development"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t