{"id":1472,"date":"2026-10-29T18:00:00","date_gmt":"2026-10-29T18:00:00","guid":{"rendered":"https:\/\/wade.one\/blog\/?p=1472"},"modified":"2026-10-29T18:00:00","modified_gmt":"2026-10-29T18:00:00","slug":"a-database-constraint-can-close-the-gap-between-checking-and-writing","status":"publish","type":"post","link":"https:\/\/wade.one\/blog\/2026\/10\/29\/a-database-constraint-can-close-the-gap-between-checking-and-writing\/","title":{"rendered":"A Database Constraint Can Close the Gap Between Checking and Writing"},"content":{"rendered":"

Checking whether a record exists before inserting another one does not guarantee uniqueness. Two requests can both see the gap and both attempt the write. If the rule really is “only one of these may exist”, I want the database to enforce it as well as the application explaining it.<\/p>\n

PostgreSQL’s constraint documentation<\/a> covers unique constraints, foreign keys and checks on stored values. Each expresses a different kind of rule. Choose one that matches the actual invariant; a unique constraint on the wrong combination of columns can enforce something quite different from what the product intended.<\/p>\n

The application still needs to handle rejection properly. A constraint violation should become a useful result where the operation expects that possibility, rather than an unexplained server error. Existing data also needs checking before introducing a new constraint. An accurate rule can still fail to install because older records do not satisfy it.<\/p>\n

I like this division of work: the interface helps people submit a sensible request, and the database protects the stored truth when requests overlap or another writer arrives. It gives the rule a reliable home. The important part is choosing a rule you actually need, rather than adding restrictions simply because the database supports them.<\/p>\n","protected":false},"excerpt":{"rendered":"

Checking whether a record exists before inserting another one does not guarantee uniqueness.<\/p>\n","protected":false},"author":0,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_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":"","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},"categories":[13],"tags":[298,299,253],"class_list":["post-1472","post","type-post","status-publish","format-standard","hentry","category-programming","tag-constraints","tag-data-integrity","tag-postgresql"],"yoast_head":"\nA Database Constraint Can Close the Gap Between Checking and Writing - wade.one<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/wade.one\/blog\/2026\/10\/29\/a-database-constraint-can-close-the-gap-between-checking-and-writing\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A Database Constraint Can Close the Gap Between Checking and Writing - wade.one\" \/>\n<meta property=\"og:description\" content=\"Checking whether a record exists before inserting another one does not guarantee uniqueness.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/wade.one\/blog\/2026\/10\/29\/a-database-constraint-can-close-the-gap-between-checking-and-writing\/\" \/>\n<meta property=\"og:site_name\" content=\"wade.one\" \/>\n<meta property=\"article:published_time\" content=\"2026-10-29T18:00:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/wade.one\/blog\/wp-content\/uploads\/2015\/02\/Wade-Logo-cropped.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1518\" \/>\n\t<meta property=\"og:image:height\" content=\"1506\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@wadewomersley\" \/>\n<meta name=\"twitter:site\" content=\"@wadewomersley\" \/>\n<meta name=\"twitter:label1\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2026\\\/10\\\/29\\\/a-database-constraint-can-close-the-gap-between-checking-and-writing\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2026\\\/10\\\/29\\\/a-database-constraint-can-close-the-gap-between-checking-and-writing\\\/\"},\"author\":{\"name\":\"\",\"@id\":\"\"},\"headline\":\"A Database Constraint Can Close the Gap Between Checking and Writing\",\"datePublished\":\"2026-10-29T18:00:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2026\\\/10\\\/29\\\/a-database-constraint-can-close-the-gap-between-checking-and-writing\\\/\"},\"wordCount\":216,\"publisher\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/#\\\/schema\\\/person\\\/8b4739f8f8bb2cff5d792d4b8779fcc3\"},\"keywords\":[\"constraints\",\"data-integrity\",\"postgresql\"],\"articleSection\":[\"Programming\"],\"inLanguage\":\"en-GB\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2026\\\/10\\\/29\\\/a-database-constraint-can-close-the-gap-between-checking-and-writing\\\/\",\"url\":\"https:\\\/\\\/wade.one\\\/blog\\\/2026\\\/10\\\/29\\\/a-database-constraint-can-close-the-gap-between-checking-and-writing\\\/\",\"name\":\"A Database Constraint Can Close the Gap Between Checking and Writing - wade.one\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/#website\"},\"datePublished\":\"2026-10-29T18:00:00+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2026\\\/10\\\/29\\\/a-database-constraint-can-close-the-gap-between-checking-and-writing\\\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/wade.one\\\/blog\\\/2026\\\/10\\\/29\\\/a-database-constraint-can-close-the-gap-between-checking-and-writing\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2026\\\/10\\\/29\\\/a-database-constraint-can-close-the-gap-between-checking-and-writing\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/wade.one\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A Database Constraint Can Close the Gap Between Checking and Writing\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/wade.one\\\/blog\\\/\",\"name\":\"wade.one\",\"description\":\"wade womersley - york based software engineer\",\"publisher\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/#\\\/schema\\\/person\\\/8b4739f8f8bb2cff5d792d4b8779fcc3\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/wade.one\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/#\\\/schema\\\/person\\\/8b4739f8f8bb2cff5d792d4b8779fcc3\",\"name\":\"Wade Womersley\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/wp-content\\\/uploads\\\/2015\\\/02\\\/200px.png\",\"url\":\"https:\\\/\\\/wade.one\\\/blog\\\/wp-content\\\/uploads\\\/2015\\\/02\\\/200px.png\",\"contentUrl\":\"https:\\\/\\\/wade.one\\\/blog\\\/wp-content\\\/uploads\\\/2015\\\/02\\\/200px.png\",\"width\":202,\"height\":200,\"caption\":\"Wade Womersley\"},\"logo\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/wp-content\\\/uploads\\\/2015\\\/02\\\/200px.png\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"A Database Constraint Can Close the Gap Between Checking and Writing - wade.one","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/wade.one\/blog\/2026\/10\/29\/a-database-constraint-can-close-the-gap-between-checking-and-writing\/","og_locale":"en_GB","og_type":"article","og_title":"A Database Constraint Can Close the Gap Between Checking and Writing - wade.one","og_description":"Checking whether a record exists before inserting another one does not guarantee uniqueness.","og_url":"https:\/\/wade.one\/blog\/2026\/10\/29\/a-database-constraint-can-close-the-gap-between-checking-and-writing\/","og_site_name":"wade.one","article_published_time":"2026-10-29T18:00:00+00:00","og_image":[{"width":1518,"height":1506,"url":"https:\/\/wade.one\/blog\/wp-content\/uploads\/2015\/02\/Wade-Logo-cropped.png","type":"image\/png"}],"twitter_card":"summary_large_image","twitter_creator":"@wadewomersley","twitter_site":"@wadewomersley","twitter_misc":{"Estimated reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/wade.one\/blog\/2026\/10\/29\/a-database-constraint-can-close-the-gap-between-checking-and-writing\/#article","isPartOf":{"@id":"https:\/\/wade.one\/blog\/2026\/10\/29\/a-database-constraint-can-close-the-gap-between-checking-and-writing\/"},"author":{"name":"","@id":""},"headline":"A Database Constraint Can Close the Gap Between Checking and Writing","datePublished":"2026-10-29T18:00:00+00:00","mainEntityOfPage":{"@id":"https:\/\/wade.one\/blog\/2026\/10\/29\/a-database-constraint-can-close-the-gap-between-checking-and-writing\/"},"wordCount":216,"publisher":{"@id":"https:\/\/wade.one\/blog\/#\/schema\/person\/8b4739f8f8bb2cff5d792d4b8779fcc3"},"keywords":["constraints","data-integrity","postgresql"],"articleSection":["Programming"],"inLanguage":"en-GB"},{"@type":"WebPage","@id":"https:\/\/wade.one\/blog\/2026\/10\/29\/a-database-constraint-can-close-the-gap-between-checking-and-writing\/","url":"https:\/\/wade.one\/blog\/2026\/10\/29\/a-database-constraint-can-close-the-gap-between-checking-and-writing\/","name":"A Database Constraint Can Close the Gap Between Checking and Writing - wade.one","isPartOf":{"@id":"https:\/\/wade.one\/blog\/#website"},"datePublished":"2026-10-29T18:00:00+00:00","breadcrumb":{"@id":"https:\/\/wade.one\/blog\/2026\/10\/29\/a-database-constraint-can-close-the-gap-between-checking-and-writing\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/wade.one\/blog\/2026\/10\/29\/a-database-constraint-can-close-the-gap-between-checking-and-writing\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/wade.one\/blog\/2026\/10\/29\/a-database-constraint-can-close-the-gap-between-checking-and-writing\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/wade.one\/blog\/"},{"@type":"ListItem","position":2,"name":"A Database Constraint Can Close the Gap Between Checking and Writing"}]},{"@type":"WebSite","@id":"https:\/\/wade.one\/blog\/#website","url":"https:\/\/wade.one\/blog\/","name":"wade.one","description":"wade womersley - york based software engineer","publisher":{"@id":"https:\/\/wade.one\/blog\/#\/schema\/person\/8b4739f8f8bb2cff5d792d4b8779fcc3"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/wade.one\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":["Person","Organization"],"@id":"https:\/\/wade.one\/blog\/#\/schema\/person\/8b4739f8f8bb2cff5d792d4b8779fcc3","name":"Wade Womersley","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/wade.one\/blog\/wp-content\/uploads\/2015\/02\/200px.png","url":"https:\/\/wade.one\/blog\/wp-content\/uploads\/2015\/02\/200px.png","contentUrl":"https:\/\/wade.one\/blog\/wp-content\/uploads\/2015\/02\/200px.png","width":202,"height":200,"caption":"Wade Womersley"},"logo":{"@id":"https:\/\/wade.one\/blog\/wp-content\/uploads\/2015\/02\/200px.png"}}]}},"jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":1431,"url":"https:\/\/wade.one\/blog\/2026\/10\/12\/postgresql-14-support-ends-in-november-plan-the-upgrade-now\/","url_meta":{"origin":1472,"position":0},"title":"PostgreSQL 14 Support Ends in November: Plan the Upgrade Now","author":"","date":"October 12, 2026","format":false,"excerpt":"PostgreSQL 14 reaches the end of community support on 12 November 2026.","rel":"","context":"In "Programming"","block_context":{"text":"Programming","link":"https:\/\/wade.one\/blog\/category\/programming\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1434,"url":"https:\/\/wade.one\/blog\/2026\/10\/15\/read-the-postgresql-query-plan-before-adding-another-index\/","url_meta":{"origin":1472,"position":1},"title":"Read the PostgreSQL Query Plan Before Adding Another Index","author":"","date":"October 15, 2026","format":false,"excerpt":"When a database query gets slow, adding an index is an appealingly concrete thing to do.","rel":"","context":"In "Programming"","block_context":{"text":"Programming","link":"https:\/\/wade.one\/blog\/category\/programming\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1161,"url":"https:\/\/wade.one\/blog\/2026\/03\/29\/why-senior-engineers-are-really-paid-for-judgment\/","url_meta":{"origin":1472,"position":2},"title":"Why Senior Engineers Are Really Paid for Judgment","author":"Wade","date":"March 29, 2026","format":false,"excerpt":"Senior engineers are not mainly paid for writing more code. They are paid for making better decisions under constraints, risk, and ambiguity.","rel":"","context":"In "Software Engineer"","block_context":{"text":"Software Engineer","link":"https:\/\/wade.one\/blog\/category\/software-engineer\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1336,"url":"https:\/\/wade.one\/blog\/2026\/06\/04\/overtasked-coding-agents-need-lead-dev-boundaries\/","url_meta":{"origin":1472,"position":3},"title":"Overtasked Coding Agents Need Lead Dev Boundaries","author":"Wade","date":"June 4, 2026","format":false,"excerpt":"Coding agents need bounded tasks, tests, and review because out-of-scope actions are exactly the kind of thing a lead developer has to catch.","rel":"","context":"In "AI"","block_context":{"text":"AI","link":"https:\/\/wade.one\/blog\/category\/ai\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1259,"url":"https:\/\/wade.one\/blog\/2026\/05\/07\/the-next-developer-skill-is-agent-direction\/","url_meta":{"origin":1472,"position":4},"title":"The Next Developer Skill Is Agent Direction","author":"Wade","date":"May 7, 2026","format":false,"excerpt":"As coding agents become more capable, the valuable skill shifts toward direction: defining the task, setting boundaries, reviewing output, and owning the decision.","rel":"","context":"In "AI"","block_context":{"text":"AI","link":"https:\/\/wade.one\/blog\/category\/ai\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1335,"url":"https:\/\/wade.one\/blog\/2026\/06\/03\/claude-code-routines-make-agents-feel-more-like-ci-jobs\/","url_meta":{"origin":1472,"position":5},"title":"Claude Code Routines Make Agents Feel More Like CI Jobs","author":"Wade","date":"June 3, 2026","format":false,"excerpt":"Claude Code routines are interesting because they move coding agents toward scheduled, event-driven, reviewable work.","rel":"","context":"In "AI"","block_context":{"text":"AI","link":"https:\/\/wade.one\/blog\/category\/ai\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/posts\/1472","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/types\/post"}],"replies":[{"embeddable":true,"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/comments?post=1472"}],"version-history":[{"count":1,"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/posts\/1472\/revisions"}],"predecessor-version":[{"id":1486,"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/posts\/1472\/revisions\/1486"}],"wp:attachment":[{"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/media?parent=1472"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/categories?post=1472"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/tags?post=1472"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}