# What normal form does a surrogate key violate?

I have following question:

"What normal form does a surrogate key violate?"

My thought was the 3rd normal form, but I'm not quite sure it's just an assumption i am making. Could someone explain that to me?

-
You accepted an incorrect answer. – JacquesB Sep 8 at 8:06

Arguably a surrogate key is not the natural key of the table, so it could be said to violate the 'nothing but the key' principle of 3NF. In practice a surrogate key is just a place holder for the natural key, so this argument is academic at best.

Some obscure normal forms require composite keys to become relevant. 5NF comes to mind in this case as it requires multiple overlapping composite keys on a M:M relationship for a 5NF violation to be possible.

-
5NF is "obscure"?! More to the point, the "nothing but the key" cliché isn't a very precise description of 3NF. 3NF is concerned equally with all the candidate keys of a relation, not just one. A surrogate key won't violate 3NF unless it gives rise to some partial key dependency. Most people will understand "surrogate" to mean a simple (non-composite) key consisting only of arbitrary values, so a partial key dependency involving a surrogate is extremely unlikely. – sqlvogel Apr 18 '12 at 11:38
It's the only context I can imagine someone possibly making this argument. As said, it's an academic argument at best (and spurious at worst). – ConcernedOfTunbridgeWells Apr 18 '12 at 11:54
@sqlvogel Arguments against surrogates are not about partial dependencies (a 2nf concern) but about transitive dependencies (a 3fn concern) since every column is dependent on the surrogate through (transitively) the natural key. – user1598390 Aug 21 '14 at 21:20
@user1598390 X transitively determines Z when there is a Y where X->Y and Y->Z and not Y->X. Columns cannot be transitively dependent on a first key "through" a second key because the keys functionally determine each other, ie the second must determine the first. Also, violating 2NF also violates 3NF, ie the 3NF intransitivity requirement is in addition to being in 2NF, ie having no partial key dependencies. – philipxy Jul 15 at 3:49

The accepted answer is incorrect; the answers given by @sqlvogel and @gbn are correct.

Surrogate keys are non-domain-driven keys that stand-in for natural keys (those with functional dependencies that derive from the domain).

For instance, we might have a table with independent, non-overlapping keys (a table named `People` with `id`, `ssn`, and `email` as keys). Both `ssn` and `email` are natural keys (we have decided that given just a social security number or just an email that we can uniquely identify a person). `id` is a surrogate key--a key that we've added for the express purpose of uniquely identifying a person. People don't tend to have `ids`, but relations commonly have a surrogate key named `id`. So the `id` key does not derive from the domain of Personhood.

That said, `id`, `email`, and `ssn` each functionally determine all other attributes on the `Person` table. They are all candidate keys (and thereby superkeys).

BCNF violations occur when either non-key attributes functionally determine other attributes or when only part of a candidate key determines other attributes. Since each attribute is itself a candidate key, no BCNF violations exist.

### Doesn't A Key Have To Be Minimal?

What if a surrogate key stands in for a composite natural key? For instance, a `Films` table where `title` and `original_release_date` combine to form a natural key, and an `id` field acts as a surrogate key. Doesn't the {`title`, `original_release_date`} key violate the requirement that a key be minimal?

This is a misconception about the definition of minimality. Just because the surrogate key consists of fewer attributes than the natural key does not mean that it is the singular minimal key. A candidate key is minimal if no proper subset of the key exists that is also a candidate key. `title` does not uniquely identify a `Film`, and neither does `original_release_date`. Therefore, even in the case where a surrogate key stands in for a composite natural key, there is no normal form violation.

-

They violate 3NF.

That's OK when you have a normal model and decide to denormalize here and there judiciously. But not when you model with surrogates to begin with.

• How do you know you are violating 3NF ?

A quote from the book "Using SQLite", Jay A. Kreibich, O'Reilly

The Third Normal Form, or 3NF, extends the 2NF to eliminate transitive key depend- encies. A transitive dependency is when A depends on B, and B depends on C, and therefore A depends on C. 3NF requires that each nonprimary key column has a direct (nontransitive) dependency on the primary key.

...

A good way to recognize columns that may break 3NF is to look for pairs or sets of unrelated columns that need to be kept in sync with each other.

An autoincremental PK in an entity where no natural key exist is not a surrogate. A surrogate is a autoincremental PK added to a table where a natural key existed. That said, both keys, the surrogate PK and the business key (the key users search) have to be syncronized because if the are not syncronized, the data is lost for the user. That's how you know you are violating 3NF.

One case of that is a problem the bitcoin plataform has where and attacker de-synced the surrogate key and the business key resulting users lost their bitcoins.

A quote from Greg Maxwell, core-developer or Bitcoin:

...to database experts: the real problem is that Mt Gox (and other exchanges) are using a surrogate transaction id rather than a natural key in their databases: "The flaw isn't so much in Bitcoin as it is in exchange-systems. Many exchanges use the tx-id to uniquely identify transactions, but as it turns out, an attacker can change the tx-id without changing the actual transaction, rebroadcast the changed transaction (effectively creating a double-spend) and if his altered transaction gets accepted into a block instead of the legit transaction, the attacker receives his coins and can complain with the exchange that he didn't. The exchange will then check their db, fetch the tx-id from it, look it up in the blockchain and not find it. So they could conclude that the transaction indeed failed and credit the account with the coins. ... A simple workaround is to not use the tx-id to identify transactions on the exchange side, but the (amount, address, timestamp) instead."

Quote from here and here

• does this table has single non PK attribute that if changed arbitrarily would prevent the user from retrieving the data ?

If that's so, you are violating 3NF.

But... sometimes one has to denormalize ( violate normal forms knowingly ) for performance or simplicity's sake. For example when a composite natural key is too long ( exceeds three columns according to PDMs design principles ) and you need to create a FK to that table.

-
Your interpretation of 3NF is not correct. 3NF is violated where there is a dependency on a non-prime attribute (i.e. a "transitive" dependency). Non-prime means an attribute that is not part of any candidate key. Dependence on several alternative keys therefore is not a violation of 3NF and such dependencies are implicit and required whenever a table has more than one key. By definition every attribute of a relation is functionally determined by every candidate key of that relation. – sqlvogel Apr 5 at 18:39

It doesn't. Keys of any kind don't in themselves violate any normal form. It is the set of dependencies you expect the table to represent that defines whether any NF is being satisfied or not.

It's true that adding a surrogate key does imply an extra set of dependencies on that key. By definition those extra dependencies are join dependencies implied by superkeys, which means 5NF and DKNF for example are not violated. The only possible exception is if some proper subset of the attributes (partial key) of the surrogate is a determinant in its own right. Given that "surrogate" normally means a single attribute key whose values are arbitrary such a partial key dependency is unlikely.

6NF could be violated by the addition of the surrogate key attribute but if so then that's due simply to adding an attribute - it's not a problem specifically with surrogate keys.

-

Arguably, it doesn't.

Adding a surrogate key is an implementation decision (to respect how the RDBMS works) taken at implementation time. During modelling and normalisation, you should end up with BCNF (slightly stricter and more correct 3NF) without surrogate keys

That is, introducing surrogate keys at the start of the design process is wrong. Even though we all do it...

-
Isn't every physical column created in any specific RDBM an implementation decision ? For example, creating a table with columns phone1, phone2 and phone3 is an implementation decision, yet it violates 1NF. Please produce a source supporting that normal forms only hold in the conceptual realm. – user1598390 Aug 21 '14 at 21:23
@user1598390: Having columns phone1, phone2, phone3 does not violate 1NF. You only violate 1NF if you have multiple phone number in the same column. – JacquesB Sep 8 at 8:13