Referential Integrity in SQL Server

I am very new to SQL Server 2005 - I need to look at a database that has been upsized from Access 2003 and I want to ensure that Referential Integrity is in place. What is the best way to enfore RI - using constraints? Can anyone give me a simple example of how to enforce it? Many thanks.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
Example from books online

/* ************************** jobs table ************************** */
   job_id  smallint
   job_desc        varchar(50)     NOT NULL
      DEFAULT 'New Position - title not formalized yet',
   min_lvl tinyint NOT NULL
      CHECK (min_lvl >= 10),
   max_lvl tinyint NOT NULL
      CHECK (max_lvl <= 250)

/* ************************* employee table ************************* */
   emp_id  empid
      CONSTRAINT CK_emp_id CHECK (emp_id LIKE
         '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or
         emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'),
      /* Each employee ID consists of three characters that
      represent the employee's initials, followed by a five
      digit number ranging from 10000 through 99999 and then the
      employee's gender (M or F). A (hyphen) - is acceptable
      for the middle initial. */
   fname   varchar(20)     NOT NULL,
   minit   char(1) NULL,
   lname   varchar(30)     NOT NULL,
   job_id  smallint        NOT NULL
      DEFAULT 1
      /* Entry job_id for new hires. */
      REFERENCES jobs(job_id), ----------------------------------foreign key
   job_lvl tinyint
      DEFAULT 10,
      /* Entry job_lvl for new hires. */
   pub_id  char(4) NOT NULL
      DEFAULT ('9952')
      REFERENCES publishers(pub_id), -------------------------------Foreign key
      /* By default, the Parent Company Publisher is the company
      to whom each employee reports. */
   hire_date       datetime        NOT NULL
      DEFAULT (getdate())
      /* By default, the current system date is entered. */

/* ***************** publishers table ******************** */
CREATE TABLE publishers
   pub_id  char(4) NOT NULL
         CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756')
            OR pub_id LIKE '99[0-9][0-9]'),
   pub_name      varchar(40)     NULL,
   city         varchar(20)     NULL,
   state      char(2) NULL,
   country      varchar(30)     NULL

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The easiest method is to use SQL Server Management Studio (or the Express edition) and check that the appropriate primary and foreign keys are created on the database tables.  When you examine the properties, it will indicate what referential integrity actions are configured.

A primary key means UNIQUE NON-NULL at a minimum.
A foreign key means NULL or key exists in referenced table at a minimum.

You can enforece referential integrity with SQL Server 2005 foreign key constraints. If upsizeing from Access make sure that types of referencing and referenced columns are the same including length.
SQL2k5 supports CASCADE and SET NULL referencing actions. If none of them is set (or explicitly set NO ACTION) an error is raised while you try to delete or update master key while detail exists and insert detail with missing master key.

CREATE TABLE dbo.PKTable (PK int identity, primary key)
CREATE TABLE dbo.FKTAable(id int identity primary key, fk int, constraint FK_FKTable_PKTable foregin key (fk) references PKTable(pk))
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.