Introduction
It's a surprisingly easy thing to do: create orphans by design in a database. In this post I present one such example.
Relationship First
First you need a a relationship between two tables. In this example, we'll start with a parent-child relationship.
Looking at this diagram, you can infer a relationship between Parent and Child. The likely candidate key is ParentID. But - and this is a big butt (typo intentional) - there is no relationship present in the database. If there was a relationship in the database, the diagram would appear as shown:
This is called referential integrity in database-speak. It's important because it defines and maintains this relationship between parent and child records. It prevents "orphans" - rows in the child table that do not have related rows in the parent.
"So?"
"What's the big deal, Andy?" I'm glad you asked.
Let's look at the downside of maintaining a relationship while developing against a couple tables with referential integrity applied:
-
You have to write inserts a certain way. You cannot, for example, just poke a row into Child. It has to have a matching row in Parent. The value you insert into the Child.ParentID column must have a matching value in the Parent.ParentID column. If it doesn't already exist, you have to insert a row into Parent before you do the insert into Child.
-
You have to do deletes in a certain way. Similar to inserts, you cannot just delete a row willy-nilly from the Parent table. It may have related rows in the Child table, and those rows would be "orphaned" if you delete the parent.
Now let's look at the upside of maintaining a relationship while developing against a couple tables with referential integrity applied:
-
Your code will be built against a production-ready version of the database. Dropping keys may make your inserts work while developing, but it will cost you later. Truth is, you may develop bad code - and lots of it - before you realize the scope of the damage.
-
Persistence is the art of state and data lends itself to certain designs. One key of good database design is: Identify the relationships and build the tables around them. It will be easier to design, build, maintain, load, and ultimately use.
-
These aren't just helpful suggestions, well-designed databases perform and scale better; and well-designed databases are designed with referential integrity.
Constraints In Action
If you attempt to insert a row into the Child table without a related Parent table row, an error is raised. For example, if you attempt to execute this statement:
insert into Child
(ParentID, ChildName, ChildValue)
Values(1, 'A', 12)
an error similar to the following is raised:
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Child_Parent". The conflict occurred in database "TestDB", table "dbo.Parent", column 'ParentID'.
Defeating Constraints
It's possible to defeat constraints in several ways. I'm not going to exhaust all the ways you can do this here. I'm just going to advise that you not do it... unless you are absolutely certain you understand the implications of this action.
The Anti-Pattern
The anti-pattern is shown in the first image above: there's a relationship, but it's managed elsewhere. There are occassions when the right design involves managing the relationship in another location and not in the database. Those occassions are rare.
:{> Andy