Not using foreign key constraints in real practice. Is it OK? [duplicate]

Question

Not using FK constraints is my company's untold rule. FK constraints are used only when designing ERD and not used when creating tables.

According to my senior, in real practice, those are very time consuming obstacles when we are dealing with urgent issues. He says when we need to use INSERT/UPDATE/DELETE statements immediately, the constraints block those statements to be executed and writing statements while keeping the constraints is time consuming too. I even heard many other companies are doing this.

While I kinda understand the struggles, I'm not sure if this is a good way since it's quite opposite to my understanding to DB. This company is my first job too, so I don't know how other companies dealing with this.

In practical way, what is your opinion about this? Can this be justified? Are there better ways? How other companies are doing about this matters?

UPDATE: It seems it is a quite common approach for South Korean companies. I asked a few other seniors who worked for other companies, and most of them are saying they all doing like this. And even one of them worked for a financial company! Interesting...

Answer 1

Nothing is free. Sometime not having something isn't free either. Both having and not having declared foreign keys come with costs and benefits.

The point of a foreign key (FK) is to ensure that this column over here can only ever have values that come from that column over there¹. This way we can be sure we only ever capture orders for customers that actually exist, for products we actually produce and sell. A lot of people think this is a good idea.

The reason we declare them inside the DBMS is so it can take care of enforcing them. It will never ever allow in any data that breaks the rules. Also it will never allow you to get rid of data required to enforce the rules. By delegating this task to the machine we can have confidence in the integrity of the data, no matter what its source or when it was written or which application it came through. Of course this comes with a cost. The DBMS has to check the rules are being followed, for each and every row, for each and every query, all the time. This takes time and effort, which is a load on the server. It also requires the humans to submit DML in a sequence that respects the rules. No more slyly forcing in an Order, then catching up with the admin afterwards. Oh no naughty human, you must first create the Customer, then the Product (and all the prerequisite rows) and only then may you create an Order.

Without foreign keys we are much freer with what we can do, and the order in which we can do it. Problematical rows can be removed ad hoc to allow critical processes to complete. The data can be patched up afterwards, when the panic is over. INSERTs are generally a little quicker (which adds up over time) because no FKs checks are done. Arbitrary subsets of data can be pulled from the DB as desired without having to ensure all supporting data is included. And so on. This can be absolutely fine if the people involved know the system, take careful notes, have good reconciliation routines, understand the consequences and have the time to tidy up after themselves. The cost is that something, somewhere is missed one time and the database deteriorates into barely creditable junk.

Some teams put the checks in the application rather than in the database. Again, this can work. It seems to me, however, that the total server load will be much the same (or slightly higher) with this approach, but the risk of forgetting some check in a bit of code somewhere is much higher. With DRI the rule's communicated to the computer once and is enforced forever. In application code it has to be re-written with each new program.

For my two cents' worth, I'm all for foreign keys. Let the computers do what they're good at doing, like routine repetitive checking that column values match. We humans can concentrate on dreaming up new and interesting stuff. Having taken responsibility for a system a few months back I'm adding FKs to most tables. There are a few, however, where I will not be adding them. These are where we collect data from external sources. The cost of rejecting these rows is greater than the cost of accepting bad data and fixing it up later. So, for these few tables, there will be no foreign keys. I do this open-eyed, knowing we have monitoring and corrective procedures in place.

¹I acknowledge the existence of multi-column foreign key constraints.

Answer 2

It is nearly always better to declare foreign key constraints than to use foreign keys with no constraint in the DBMS. The foreign key constraint maintains referential integrity, which prevents one form of data corruption.

The alternative of enforcing referential integrity at the application level nearly always costs as much or more in computer resources and programmer effort as the DBMS constraint would.

The alternative of not maintaining referential integrity usually leads to corrupt data and unreliable results.

The chances are very large that your site's policy was mistaken from the outset. There are exceptions to this, but the are few and far between.

Answer 3

Your senior is disgustingly wrong. FK constraints are one line of code to ensure data integrity. What takes more time,

• literally one line of code in a declarative language, and sometimes less as you can add foreign keys inline when you CREATE the table.
• or, to assume integrity and handle the drawbacks of failed assumptions.

There is no reason not to use Foreign Key Constraints, and to use a relational database. You have two jobs as a SQL DBA:

• Make schema with DDL.
• Access schema with DML.

If you're not ensuring the integrity of your data with the schema, you're simply skipping part of the job. The very nature of a transaction is to ensure atomicy all-or-nothing. You want to define "all" without using foreign key constraints? That's a pita.

CREATE TABLE foo ( int a PRIMARY KEY );
CREATE TABLE bar ( int b REFERENCES foo );
CREATE TABLE baz ( int b );

Now do an atomic transaction for bar and baz

1. inserts into foo.a the value (1). Fails if the value exist.
2. inserts into other table (bar.b or baz.b) the same value, 1 and fails if the value doesn't exist in foo.a.

Which one is time-saving?

Answer 4

This is something that would truly need to be tested for your own situation but to provide the data integrity of foreign keys while quickly deleting or updating data you could look into cascading.

CREATE TABLE ChildTable (
    id INT NOT NULL IDENTITY(1,1), 
    parent_id INT,
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
);