SQL Server 7 0 Maintaining Referential Integrity SQL

SQL Server 7. 0 Maintaining Referential Integrity

SQL Server is a RDBMS • Relationships between entities in the data model. • Entities are implemented as Tables. • A mechanism ensures that Referential Integrity is not violated.

Mechanisms to enforce Referential Integrity, Data Integrity • Data integrity is enforced by constraints, triggers, rules, and defaults. • We will focus on constraints and triggers. • Constraints are: – Simple. – Proactive. – Query optimizer uses them. • Triggers: – Complex logic. – Checking data in other tables/cascade changes. – Reactive / Incur more overhead.

Entity Integrity • PRIMARY KEY constraint. – Creates a unique index. – Disallows Nulls. • UNIQUE constraint. – Creates a unique index. – Allows Nulls. • UNIQUE index.

FOREIGN KEY • FOREIGN KEY constraint REFERENCES primary table’s PRIMARY KEY. • It is recommended not to allow changes to the PRIMARY KEY, this may lead to the complex enforcement approach - Triggers.

Relationship between two tables • A typical Primary / Secondary table’s scenario. • Example: Orders and Order. Details.

Relationship in a Self-Referencing table • A typical selfreferencing table scenario. • Example: Employees table, where each employee has a manager in charge; the manager is an employee himself.

Four Referential Integrity rules • Deleting a row from Primary. Table is not allowed if there are related rows in Secondary. Table. • An update to Primary. Table. col 1 is not allowed if there are related rows in Secondary. Table. • An insert of a row into Secondary. Table is not allowed if there is no related row in Primary. Table. • An update to Secondary. Table. col 1 is not allowed if there is no related row in Primary. Table.

FOREIGN KEY and REFERENCES constraint The best way to enforce all those rules is to create a FOREIGN KEY and REFERENCES constraint, as the following statement shows: ALTER TABLE Secondary. Table ADD CONSTRAINT FK_Secondary. Table_Primary. Table FOREIGN KEY(col 1) REFERENCES Primary. Table(col 1) We will use the same constraint to enforce Referential Integrity in the Self-Referencing table, as the following statement shows: ALTER TABLE Self. Ref. Table ADD CONSTRAINT FK_Self. Ref. Table FOREIGN KEY(col 2) REFERENCES Self. Ref. Table(col 1)

Triggers and Cascading changes • Things get complicated if we need to allow changes to Primary. Table and cascade those changes to Secondary. Table. • We need to decide whether to allow cascading DELETE and/or UPDATE operations. • Constraints can not be used in this case.

Choosing the right Referential Integrity mechanism

Cascading DELETE Operations The following trigger achieves cascading DELETE operations from the Primary. Table to the Secondary. Table: CREATE TRIGGER dbo. TRG_cascade_delete ON Primary. Table FOR DELETE AS DELETE FROM Sec. Tbl FROM Secondary. Table Sec. Tbl INNER JOIN deleted d ON Sec. Tbl. col 1 = d. col 1

Trigger • Is a special kind of a stored procedure. • Is invoked automatically for individual INSERT, UPDATE, DELETE operation, or a combination of those operations. • Creates special areas in cache where it keeps the modified data, before and after the change. • Is an implicit transaction. • Now has 32 nesting levels. • Can define more than one trigger of the same type on the same table.

Cascade DELETE example

Preventing a DELETE operation If we decide not to cascade DELETE operations we need to enforce an invalid DELETE operation: CREATE TRIGGER dbo. TRG_prevent_delete ON Primary. Table FOR DELETE AS IF EXISTS (SELECT * FROM Secondary. Table Sec. Tbl INNER JOIN deleted d ON Sec. Tbl. col 1 = d. col 1) BEGIN RAISERROR('Primary. Table has related rows in Secondary. Table. TRANSACTION rolled back. ', 10, 1) ROLLBACK TRANSACTION END

Cascading UPDATE operations CREATE TRIGGER dbo. TRG_cascade_update ON Primary. Table FOR UPDATE AS DECLARE @numrows int SET @numrows = @@rowcount IF UPDATE(col 1) IF @numrows = 1 UPDATE Sec. Tbl SET col 1 = (SELECT col 1 FROM inserted) FROM deleted d INNER JOIN Secondary. Table Sec. Tbl ON d. col 1 = Sec. Tbl. col 1 ELSE IF @numrows > 1 BEGIN RAISERROR('Updates to more than one row in Primary. Table are not allowed. TRANSACTION rolled back. ', 10, 1) ROLLBACK TRANSACTION END

Cascade UPDATE example

Preventing invalid updates CREATE TRIGGER dbo. TRG_prevent_update ON Primary. Table FOR UPDATE AS IF EXISTS (SELECT * FROM Secondary. Table Sec. Tbl INNER JOIN deleted d ON Sec. Tbl. col 1 = d. col 1) BEGIN RAISERROR('Primary. Table has related rows in Secondary. Table. TRANSACTION rolled back. ', 10, 1) ROLLBACK TRANSACTION END

Preventing invalid changes to the Secondary. Table CREATE TRIGGER dbo. TRG_prevent_insupd ON Secondary. Table FOR INSERT, UPDATE AS DECLARE @numrows int SET @numrows = @@rowcount IF @numrows <> (SELECT COUNT(*) FROM Primary. Table Prm. Tbl INNER JOIN inserted i ON Prm. Tbl. col 1 = i. col 1) BEGIN RAISERROR('Result rows in Secondary. Table do not have related rows in Primary. Table. TRANSACTION rolled back. ', 10, 1) ROLLBACK TRANSACTION END

Encapsulating the logic sp_Create. Relationship [@primary_table =] ‘primary table name’ , [@secondary_table =] ‘secondary table name’ , [@primary_col 1 =] ‘primary column name_n’, [, … 16] , [@secondary_col 1 =] ‘secondary column name_n’, [, … 16] [, [@cascade_updates =] 'true' | 'false'] [, [@cascade_deletes =] 'true' | 'false'] Implementing the stored procedure is just a matter of getting the user’s input and combining it in the ALTER TABLE ADD CONSTRAINT or CREATE TRIGGER commands.

Summery • A poorly designed database will lead to the use of complex, resource consuming mechanisms such as triggers. • In most of the cases, a well-designed database will allow the use of the preferred mechanism – constraints. • I presented deferent scenarios that need different approaches in implementing Referential Integrity.