Return to 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

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

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

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

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

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

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

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

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

2. 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 it 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 are declared is that, while it is not strictly necessary for a database to have them to function, it is absolutely necessary for them to be declared for it to function well.

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

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