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.
Why Should a Key Be Made Explicit?
primary-keyunique-constraint
Related Question
- Is It Reasonable to Mark All Columns But One as Primary Key in PostgreSQL?
- MS Access – How to Allow Part of a Key to Be Null and Enforce Uniqueness
- Sql-server – Difference between a unique clustered index and a clustered primary key
- PostgreSQL – Why Composite Foreign Keys Need a Separate Unique Constraint
- Database Design – When Should a Primary Key Be Meaningful?
- SQL Server Index Tuning – Index Uniqueness Overhead
- Mysql – Does innodb guarantee unique data in unique indexes even with UNIQUE_CHECKS=0
- SQL Server Index – When to Use IGNORE_DUP_KEY Option
Best Answer
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
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:
The reason that
KEYs (eitherPRIMARY,FOREIGN,UNIQUEor just ordinaryINDEXes) 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.