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