Search code examples
sqlsql-serverdatabase-administration

Maintain referential integrity without FK

Please suggest some of the best ways to maintain referential integrity without FK's in already existing database. Planning to remove the FK's from some of the tables. How can we maintain the referential integrity.

Solution

  • It is good practice to have foreign key as part of your database design. Always leveraging something out of the box with RDBMS, is better, instead of trying to reinvent the wheel.

    Foreign keys, in addition to maintaining database referential integrity, also provide performance benefits in the execution of queries. Read FK and performance benefits

    If you don't want to have Foreign keys and want to ensure referential integrity, you can think of below options:

    • Create stored procedure for all the inserts. The stored procedure first checks the existance of parent, before inserting record to child. Otherwise, it throws exception
    • Create INSERT, UPDATE, DELETE triggers in the parent, child tables and handle the referential integrity accordingly
    • If you have got ETL loads, you can think of having Lookups to handle the referential integrity before inserting data. Error rows can be moved to separate error destination.