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

3 Answers 3

up vote 5 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

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

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

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.