What normal form does a surrogate key violate?

Question

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?

Answer 1

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.

Answer 2

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...

Answer 3

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.

Answer 4

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.

Answer 5

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

So ask yourself this:

• 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.