Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

II.6.4. Managing Relationship Errors

Relationships are one place where many database applications typically run into trouble. In this section, we look at a few of the most common error conditions that can arise, along with how SQL Server protects your data.

Despite of all the integrity protections we're about to show you, deleting an entire database will override any of the table level safeguards enforced by SQL Server. Consequently, use extreme caution when deleting databases; these rules will not step in and save you.

II.6.4.1. Primary key violation

Primary keys are what SQL Server uses to locate a given record quickly based on a unique value. Here's some SQL that creates a table and defines its primary key:

CREATE TABLE [dbo].[STUDENTS]
(
   [STUDENT_ID] [int] PRIMARY KEY NOT NULL,
   [LAST_NAME] [varchar](50) NOT NULL,
   [FIRST_NAME] [varchar](50) NOT NULL,
   [STREET1] [varchar](50) NOT NULL,
   [STREET2] [varchar](50) NULL,
   [CITY] [varchar](50) NOT NULL,
   [STATE] [char](4) NOT NULL,
   [COUNTRY] [varchar](50) NOT NULL,
   [PHONE] [varchar](20) NULL,
   [EMAIL] [varchar](50) NULL,
)

Suppose that you insert a new row as follows:

INSERT INTO STUDENTS VALUES (2291,'JONES','BOB','2454 LEGION STREET',
   'APT 21', 'PHOENIX', 'AZ', 'USA', 555-555-5555, '[e-mail]')

So far, so good. However, your application logic is a little sloppy, and you attempt to insert another row with the same student_id. SQL ...