Referential Integrity

OK, I’ve talked about Primary Keys and Foreign Keys.  Now let’s talk about Referential Integrity.  Basically this just means that any Foreign Key value in one table has to have that same value defined in the table where that Foreign Key is the Primary Key.  Let’s look at two tables, and try to explain this more clearly.

CREATE TABLE person (
    personID INT IDENTITY(1,1)
  , firstName VARCHAR(50)
  , lastName VACHAR(50)
  , CONSTRAINT PK_person_personID PRIMARY KEY CLUSTERED (
      personID 
    )
) 

CREATE TABLE address (
    addressID INT IDENTITY(1,1)
  , personID INT
  , streetAddress VARCHAR(100)
  , city VARCHAR(50)
  , state CHAR(2)
  , zip VARCHAR(10)
  , CONSTRAINT PK_address_addressID PRIMARY KEY CLUSTERED (
      addressID
    )
)
ALTER TABLE address  WITH CHECK ADD  CONSTRAINT FK_address_person_personID FOREIGN KEY(personID)
REFERENCES Person (personID)
GO

In our example we have a person table, with the Primary Key personID.  We also have an address table with a Foreign Key of personID.  Notice the ALTER statement at the end.  This is what is setting up our referential integrity.  This CONSTRAINT requires that any value I choose for personID in address, must already be defined in person.  Otherwise we get a CONSTRAINT violation.

The term Cascade Referential Integrity comes into play when you have several tables each referring to another.  The check must cascade down, like a waterfall.  Each table affecting the next that depends on it.  When you start setting up references like this, you’re going to make it more difficult on your programmers.  You’re going to require that they do things in a certain way… unless you provide them a view that uses an INSTEAD OF trigger… check out my lessons on that for more details.

This integrity check for your data can make it more difficult on your programmers, but it also make sure they do their job properly.  Dropping these constraints is never the answer.  Which would you prefer, lots of dirty data that has duplicates and causes your computations to be off by double, triple, or even cause you to multiply or divide by zero?

Or would you rather your programmers take a little more time to understand the relationships in the data for the database they’re developing?

It’s a balancing act, developing relationships in the data versus the speed to build on top of that data… but I think you’ll find some relationships will benefit you far more than no relationships.

Talk to me.  Let me know what you’re going through.  I can help you build stronger database solutions, while working with your developers.  I’m here to help!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

1 comment

Leave a comment

Your email address will not be published. Required fields are marked *