Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

5 Answers 5

up vote 6 down vote accepted

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.

share|improve this answer
1  
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 at 21:20

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

share|improve this answer
    
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 at 21:23

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.