15

I am very new to the subject of databases so this may sound ignorant, but I am curious why a key should be made explicit within a table. Is this primarily to tell the user that the given column value is (hopefully) guaranteed to be unique within each row? The uniqueness should still be there even if it isn't mentioned.

Improve this question
4
  • Do you mean that if you have a UNIQUE key, why bother having a PRIMARY one?
    – Vérace
    Jul 10, 2015 at 4:28
  • 1
    Just why are they declared at all? It seems very helpful, but is it actually necessary to have a database that functions?
    – dsaxton
    Jul 10, 2015 at 4:31
  • 1
    They are not needed for your database to work but they are needed for your data to "work" i.e. be consistent, because that is exactly how you are telling your database server to keep the information consistent.
    – Andriy M
    Jul 10, 2015 at 5:46
  • If the database knows that a given field is a key, a side effect is that it can help you locate the row containing the key much much faster than if it needs to look through all the rows in the tables. Indexes are a very important part of why databases are useful.
    – Thorbjørn Ravn Andersen
    Jul 11, 2015 at 9:48

4 Answers 4

Reset to default
32

You are obviously suggesting that CONSTRAINTs in a database should be enforced by the application(s) that/which access that database?

There are many reasons why this is a bad (bad, bad...) idea.

1) If you are building a "roll-your-own" constraint "engine" (i.e. within your application code), then you are merely emulating what Oracle/SQL Server/MySQL/PostgreSQL/<.whoever...> have spent years writing. Their CONSTRAINT code has been tested over those years by literally millions of end-users.

2) With all due respect to you and your team, you are not going to get it right even in a matter of years - from here, MySQL code alone cost 40 Million dollars. And MySQL is the cheapest of the 3 servers above, and they don't even implement CHECK CONSTRAINTs. Obviously, getting R.I. (Referential Integrity) completely right is difficult.

I used to frequent the Oracle forums and I can't tell you the number of times that some poor manager/programmer has had a project thrust upon him where the genius who had his job before had the "bright" idea of doing what you suggest.

Jonathan Lewis (he wrote a 550 page book on the fundamentals of the Oracle optimiser) gives as no. 2 of his Design Disasters in another book ("Tales of the Oak Table" - the Oak Table is a group of Oracle experts) is

  1. We will check data integrity at the application level instead of taking advantage of Oracle's constraint checking abilities.

3) Even if by some miracle you can properly implement RI, you will have to completely reimplement it time and again for every application that touches that database - and if your data is important, then new applications will. Choosing this as a paradigm will lead to you and your fellow programmers (not to mention support staff and sales) to a life of constant fire-fighting and misery.

You can read more about why implementing data CONSTRAINTs at the application level is nothing short of madness here, here and here.

To specifically answer your question:

Just why are they declared at all? It seems very helpful, but is it actually necessary to have a database that functions

The reason that KEYs (either PRIMARY, FOREIGN, UNIQUE or just ordinary INDEXes) are declared is that, while it is not strictly necessary for a database to have them for it function, it is absolutely necessary for them to be declared for it to function well.

Improve this answer
3
  • 1
    Thanks for your answer. I'll probably need to learn more to fully understand it. (I don't actually belong to a team, I'm just learning about databases out of curiosity.)
    – dsaxton
    Jul 10, 2015 at 5:36
  • 2
    Read a few books (Date, Garcia-Molina...) and come back to us if you have specific questions (questions that are overly broad are considered off-topic here). p.s. Welcome to the forum :-)
    – Vérace
    Jul 10, 2015 at 6:24
  • While I would never, ever suggest that you put no constraints in the database (You should always have a primary key and foreign keys at a bare minimum), you could avoid #3 by having all apps consume from a shared service (service oriented architecture). (That's probably something you should consider for multiple consumers, anyway, as doing every last integrity check you need in the database can get nightmarish, too. Think triggers everywhere making checks across tables and rows all the time.)
    – jpmc26
    Jul 11, 2015 at 3:16
10

When you create a key in a database the DBMS engine enforces a uniqueness constraint on the key attributes. This serves at least three related purposes:

  • Data integrity: duplicate data cannot be entered into key attributes. Any dependencies on the keys are therefore guaranteed.
  • Identification: users are able to rely on keys as a means of identifying and updating data accurately.
  • Optimisation: the information (metadata) about which attributes are unique is available to the DBMS query optimiser. This information allows the optimiser to simplify query execution in certain ways so that queries will execute faster.
Improve this answer
8

I'll add one aspect to the existing excellent answers: Documentation. Often it is important to see what kinds of keys you can use to identify an entity. Any combination of unique columns is a candidate key.

The primary key tends to be an especially useful concept in practice.

Whether you enforce a key or not (you probably should) the documentation is valuable in its own right.

Improve this answer
3
  • 1
    Database diagrams! The first thing I always do when asked to say something meaningful about software I'm not familiar with is see if it uses a relational database, and if it does, try to create a database diagram. That will give me an excellent idea of the information the application works with. Unfortunately, 90% of the databases I've seen don't declare foreign keys, so the diagrams are just sets of tables. Deducing implicit application-level foreign keys requires guesswork and tweaking.
    – reinierpost
    Jul 10, 2015 at 9:44
  • 1
    @reinierpost I fully agree. The data is the most valuable object to document and keep clean because it persists forever. Code can change; it tends to be more transient.
    – boot4life
    Jul 11, 2015 at 9:11
  • @reinierpost - Consulted for a company which supplied software for the entire rail infrastructure of a large European country (large - think billions of widgets) and I said, "Hum, I'll just run a query to check out the FOREIGN KEY definitions to get a feel for the system". My query returned zip!!! Sure that my SQL must have been wrong, I mentioned this to one of the senior programmers. With pride (no less) he announced (as if he were presenting a new-born son) that the system didn't have any FKs because "all searches are on PRIMARY KEYs" - (irrelevant). <Doh...> a la Homer Simpson!
    – Vérace
    May 10, 2016 at 21:21
5

Another reason why you should use CONSTRAINTs instead of some inside-application-code:

What happens if a developer / dba use an insert / update / delete statement to modify the data direct in the DB? In this case all your nice application based referential integrity will be useless. I know, some devs like the possibility to modify data direct without having to bother with RI because they know what they do - at least the most time (but not always)

PS: Of course you could create triggers, but they are usually terrible slow (compared to CONSTRAINTS).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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