CODEWITHSUNDEEP
sqlsql-server-2005sql-server-2008
Ghita
Ghita

Reputation: 4505

SQL Server - add foreign key relationship

I have an already existing database with tables. I added foreign key relationships (because they were referring data from another table, just that relationship was not explicit in the way tables were created) for one of the tables.

How does this change impact the existing database? Does the database engine have to do some extra work on existing data in the database? Can this change be a "breaking change" if you already have an application that uses the current database schema?

Upvotes: 0

Views: 308

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

You can specify WITH NOCHECK when creating a foreign key constraint:

The WITH NOCHECK option is useful when the existing data already meets the new FOREIGN KEY constraint, or when a business rule requires the constraint to be enforced only from this point forward.

However, you should be careful when you add a constraint without checking existing data because this bypasses the controls in the Database Engine that enforce the data integrity of the table.

Upvotes: 1

Neville Kuyt
Neville Kuyt

Reputation: 29629

There is indeed some extra work (though very minimal, depending on your database server) to enforce referential integrity. In practice, the performance impact is almost never something you'd notice.

It can be a "breaking change" - your client code may insert data that doesn't meet the referential constraints. If the DB allowed you to create the constraints in the first place, it's not likely, but it is possible.

Upvotes: 1

Gigi
Gigi

Reputation: 29421

If you added a referential constraint, then the database stores that constraint and ensures it is maintained. For example, if table A has a foreign key referring to table B, then you cannot insert a row into table A that refers to a key that does not exist in table B.

Upvotes: 1

Related Questions