LISTING 1: Demonstrates Different Forms of Referential Integrity Enforcement

/* First create the referenced table, Table1
 */

USE pubs
SET nocount ON
-- drop table Table1
CREATE TABLE Table1
(a int  PRIMARY KEY,
 b datetime DEFAULT getdate(),
 c varchar(10))

/* Second, create the referencing table Table2 */
--drop table Table2
CREATE TABLE Table2
 (a int PRIMARY KEY
	CONSTRAINT FK_Table2_Table1_a  FOREIGN KEY   
	REFERENCES Table1(a) 
	ON DELETE no action ON UPDATE cascade
  ,message varchar(100))
GO

/* Insert 4 rows into Table1 */
INSERT INTO Table1(a) VALUES (1)
INSERT INTO Table1(a) VALUES (2)
INSERT INTO Table1(a) VALUES (3)
INSERT INTO Table1(a) VALUES (4)

/* Insert 6 rows into Table2; 
 * Note there are no referencing rows for the 
 * value 4 
 */
INSERT INTO Table2 VALUES (1, 'first row')
INSERT INTO Table2 VALUES (1, 'second row')
INSERT INTO Table2 VALUES (2, 'first row')
INSERT INTO Table2 VALUES (2, 'second row')
INSERT INTO Table2 VALUES (2, 'third row')
INSERT INTO Table2 VALUES (3, 'first row')
GO

/* Attempt to insert a row into Table2 that will
 * violate the referential integrity
 */
INSERT INTO Table2 VALUES (5, 'first row')

/* Verify the contents of the two tables. */
SELECT * FROM Table1
SELECT * FROM Table2

/* This delete will fail */
DELETE FROM Table1
WHERE a = 1

/* This delete will succeed because the value 
 * of column 'a' is not referenced in Table2
 */
DELETE FROM Table1
WHERE a = 4

/* Update all the rows in Table1 */
UPDATE Table1
SET a = a* 10

/* Verify the contents of the two tables. */
SELECT * FROM Table1
SELECT * FROM Table2