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.
fuerteventuraAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
Example from books online

/* ************************** jobs table ************************** */
CREATE TABLE jobs
(
   job_id  smallint
      IDENTITY(1,1)
      PRIMARY KEY CLUSTERED,
   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 ************************* */
CREATE TABLE employee
(
   emp_id  empid
      CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED
      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
         CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED
         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
            DEFAULT('USA')
)
0
 
SteveH_UKConnect With a Mentor Commented:
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.

See http://msdn2.microsoft.com/en-us/library/ms167593.aspx
0
 
maradamConnect With a Mentor Commented:
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))
0
All Courses

From novice to tech pro — start learning today.