“This keeps giving me a primary key violation!” complained one of Nancy‘s co-workers. “Screw it, I’m dropping the primary key constraint!”
That was a terrifying thing to hear someone say out loud. Nancy decided to take a look at the table before anyone did anything they’d regret.
<span class="hljs-keyword">CREATE</span> TYPE record_enum <span class="hljs-keyword">AS</span> ENUM(<span class="hljs-string">'parts'</span>);
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> IF <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">EXISTS</span> parts (
part_uuid <span class="hljs-type">VARCHAR</span>(<span class="hljs-number">40</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span>,
record record_enum <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span>,
...
...
...
<span class="hljs-keyword">PRIMARY</span> KEY (part_uuid, record)
);
This table has a composite primary key. The first is a UUID, and the second is an enum with only one option in it- the name of the table. The latter column seems, well, useless, and certainly isn’t going to make the primary key any more unique. But the UUID column should be unique. Universally unique, even.
Nancy writes:
Was the UUID not unique enough, or perhaps it was too unique?! They weren’t able to explain why they had designed the table this way.
Nor were they able to explain why they kept violating the primary key constraint. It kept happening to them, for some reason until eventually it stopped happening, also for some reason.
[Advertisement]Keep all your packages and Docker containers in one place, scan for vulnerabilities, and control who can access different feeds. ProGet installs in minutes and has a powerful free version with a lot of great features that you can upgrade when ready.Learn more.
Source: Read MoreÂ
