Recognises a surrogate key without natural-key uniqueness
A system-generated primary key added and the natural key not made a unique constraint. Nothing then stops the same thing being recorded twice. The physical form of logical row 18.
Can AI do this?
AI does this partly Some of the tested AI models master this; others do not show it. 6 of 10 models master it.
| Model | Tier | Probability of mastery | Status |
|---|---|---|---|
| gemini-4-argon | Flagship | 99% | Mastered |
| gemini-3.1-pro-preview | Flagship | 83% | Not shown |
| gpt-6-astra | Flagship | 100% | Mastered |
| claude-fable-5-1 | Flagship | 100% | Mastered |
| gemini-3.8-flash | Mid tier | 100% | Mastered |
| gpt-6.1-sol | Mid tier | 0% | Not shown |
| claude-sonnet-5-5 | Mid tier | 99% | Mastered |
| gemini-3.5-flash-lite | Low cost | 20% | Not shown |
| gpt-6-luna | Low cost | 11% | Not shown |
| claude-haiku-5-5 | Low cost | 100% | Mastered |
Mastered means a probability of at least 90% that the model has this knobit, estimated from its graded answers. “Not shown” means the answers do not settle it either way. How the models are tested.
What it looks like in practice
Expected result: The natural key to make unique
How it is assessed: Flags the deficient case and passes the acceptable one. Deficient: party with party_id as identity primary key and registry_code with no unique constraint; a reload registers every company again Acceptable: uk_party_registry_code (with the identifier type, where the logical model has one); PHY-020 adds the surrogate, the unique constraint is added by hand (ANO-012 when a uniqueness rule claims schema enforcement, PHY-016 for type-2 dimensions)
Also known as: Surrogate key without natural-key uniqueness (physical model)
Related knobits
Builds on
- K-518 Recognises an entity identified only by a system-assigned key
- K-568 Explains natural keys, surrogate keys and key stability
All 40 knobits of Design the physical data model.
Source: Review checklist 06-physical, row 10; Karwin, ID Required; S&W 6.3; Codd 1979 §4