“Referential integrity” för all framtid

Du har ett personregister där:

1. Varje person har en eller flera adresser.
2. Flera personer kan bo på samma adress.

Tabellerna Person och Address har alltså en ”ett till många”-relation till tabellen PersonAddress.

Referential integrity

Vi vill se till att databasen har ”referential integrity” dvs att alla personer har en adress och att på varje adress bor en person. Vill inte ha lösa poster som ligger och skräpar.

Problemen vi vill lösa är:

A) Om vi tar bort en person så skall personens adress också tas bort MEN endast om det inte finns fler personer på samma adress. Annars kommer andra att bli bostadslösa.

B) Om vi tar bort en adress så skall alla personer på den adressen också tas bort MEN endast om personen är den enda som bort på adressen. Flera personer kan ju bo på samma adress och en person kan ju ha två eller flera adresser.

C) Motsvarande post i tabellen PersonAddress skall också tas bort.

Du hittar all SQL-kod i slutet av artikeln.

Alla tabell och fält-namn skrivs på engelska, sålunda heter tabellen Address inte Adress.

Att blanda svenska och engelska är en styggelse som skapar mycken huvudbry och förvirring.

Hur löser vi nu problemen A,B och C ovan? Jo, i tabellen PersonAdress:

1) Skapa två främmande nycklar PersonId och AddressId

2) Skapa ”constraints” dvs regler för dessa som säger att när PersonId inte längre har ett motsvarande Id i Person-tabellen eller när AddressId inte längre har en motsvarande Id i Address-tabellen så skall raden tas bort. Detta kallas för ”CASCADING DELETE”.

3) Skapa en ”DELETE-Trigger” på tabellen PersonAddress som håller koll på om det finns någon rad i Person som inte har en adress och att det inte finns någon rad i Address som inte har någon person. Om några sådana rader finns så skall de tas bort.

Låt oss stoppa in lite data i tabellerna.

INSERT INTO Person(Id,FirstName,LastName)
VALUES(1,’Herr’,’Nilsson’),
(2,’Pippi’,’Långstrump’),
(3,’Apan’,’Ola’),
(4,’Nisse’,’Hult’),
(5,’Kalle’,’Anka’)

INSERT INTO Address(Id,Location)
VALUES(1,’Villa Villerkulla’),
(2,’Skansen’),
(3,’Manpower’),
(4,’Ankeborg’)

INSERT INTO PersonAddress(PersonId,AddressId)
VALUES(1,1),(2,1),(1,2),(3,2),(4,3),(5,4)

Vi har nu fem personer och fyra adresser:

1) Herr Nilsson som bor på Villa Villerkulla men som tillbringar Jul och Nyår med kusinerna på Skansen.

2) Pippi som endast bor i Villa Villerkulla tillsammans med Herr Nilsson

3) Apan Ola som endast bor på skansen

4) Nisse Hult som är den ende som huserar på Manpower

5) Kalle Anka som är den ende som bor i Ankeborg

Test

1) Om vi tar bort Nisse Hult så skall också Manpower tas bort automatiskt, DELETE Person WHERE Id=4

2) Om vi tar bort Ankeborg så skall också Kalle Anka tas bort automatiskt, DELETE Address WHERE Id=4

3) Om vi tar bort Skansen så skall BARA Apan Ola tas bort, inte Herr eftersom han också bor i villa Villerkulla, DELETE Address WHERE Id=2

4) Om vi tar bort Villa Villerkulla så skall både Pippi och Herr Nilsson tas bort eftersom Herr Nilsson inte längre bor på Skansen, DELETE Address WHERE Id=1

Det verkar fungera!

Ett varningens ord

Detta är queryplanen utan ”Constraints” och ”Trigger” för ”DELETE Person WHERE Id=1”.

Snabbt och effektivt.

Detta är queryplanen med ”Constraints” och ”Trigger” för ”DELETE Person WHERE Id=1”.

Den är betydligt mer komplex och tar därför längre tid.

Om tabellerna är små, några tusen rader, så märker du förmodligen ingen skillnad.

Men om tabellerna är stora så bör rensningar i databasen göras som en batch-körning på natten.

Man kan tex lägga till ett fält, ”IsValid”, som sätt till 1 som default men som sätts till 0 då man vill ta bort det. Under körning visar man endast poster med IsValid=1. På natten tar man sedan bort alla poster med IsValid=0.

SQL-Kod

/*
* Ta bort tabellen PersonAddress om den redan finns
*/

IF OBJECT_Id(‘dbo.PersonAddress’, ‘U’) IS NOT NULL
DROP TABLE dbo.PersonAddress

/*
* Ta bort tabellen Address om den redan finns
*/

IF OBJECT_Id(‘dbo.Address’, ‘U’) IS NOT NULL
DROP TABLE dbo.Address

/*
* Ta bort tabellen Person om den redan finns
*/

IF OBJECT_Id(‘dbo.Person’, ‘U’) IS NOT NULL
DROP TABLE dbo.Person

/*
* Skapa tabellen Person
*/

CREATE TABLE [dbo].[Person](
[Id] [int] PRIMARY KEY CLUSTERED NOT NULL,
[FirstName] [varchar](20) NOT NULL,
[LastName] [varchar](20) NOT NULL
);

/*
* Skapa tabellen Address
*/

CREATE TABLE [dbo].[Address](
[Id] [int] PRIMARY KEY CLUSTERED NOT NULL,
[Location] [varchar](20) NOT NULL
);

/*
* Skapa tabellen PersonAddress
*/

CREATE TABLE [dbo].[PersonAddress](
[PersonId] [int] NOT NULL,
[AddressId] [int] NOT NULL ,

PRIMARY KEY CLUSTERED

(
[PersonId] ASC,
[AddressId] ASC
)

);

ALTER TABLE [dbo].[PersonAddress] WITH CHECK ADD CONSTRAINT
[FK_PersonAddress_Address] FOREIGN KEY([AddressId])
REFERENCES [dbo].[Address] ([Id])
ON DELETE CASCADE

ALTER TABLE [dbo].[PersonAddress] WITH CHECK ADD CONSTRAINT
[FK_PersonAddress_Person] FOREIGN KEY([PersonId])
REFERENCES [dbo].[Person] ([Id])
ON DELETE CASCADE
GO

/*
* Skapa Triggern DeleteIfNotReferenced
*/

CREATE TRIGGER DeleteIfNotReferenced
ON [dbo].[PersonAddress]
FOR DELETE
AS
BEGIN
DELETE Address WHERE Id in
( SELECT a.Id
FROM dbo.Address a
LEFT JOIN dbo.PersonAddress pa on a.Id=pa.AddressId
WHERE pa.AddressId IS NULL
)

DELETE Person WHERE Id in
( SELECT p.Id
FROM dbo.Person p
LEFT JOIN dbo.PersonAddress pa on p.Id=pa.PersonId
WHERE pa.PersonId IS NULL
)
END
GO

/*
* Lägg till data i tabellerna
*/

INSERT INTO Person(Id,FirstName,LastName)
VALUES(1,’Herr’,’Nilsson’),
(2,’Pippi’,’Långstrump’),
(3,’Apan’,’Ola’),
(4,’Nisse’,’Hult’),
(5,’Kalle’,’Anka’)

INSERT INTO Address(Id,Location)
VALUES(1,’Villa Villerkulla’),
(2,’Skansen’),
(3,’Manpower’),
(4,’Ankeborg’)

INSERT INTO PersonAddress(PersonId,AddressId)
VALUES(1,1),(2,1),(1,2),(3,2),(4,3),(5,4)
GO

SELECT ‘All data’
SELECT * FROM Person
SELECT * FROM Address
SELECT * FROM PersonAddress
GO

SELECT ‘Test 1: Om vi tar bort Nisse Hult så skall också Manpower tas bort automatiskt’
DELETE Person WHERE Id=4
SELECT * FROM Person
SELECT * FROM Address
SELECT * FROM PersonAddress
GO

SELECT ‘Test 2: Om vi tar bort Ankeborg så skall också Kalle Anka tas bort automatiskt’
DELETE Address WHERE Id=4
SELECT * FROM Person
SELECT * FROM Address
SELECT * FROM PersonAddress
GO

SELECT ‘Test 3: Om vi tar bort Skansen så skall BARA Apan Ola tas bort, inte Herr Nilsson eftersom han också bor i villa Villerkulla’
DELETE Address WHERE Id=2
SELECT * FROM Person
SELECT * FROM Address
SELECT * FROM PersonAddress
GO

SELECT ‘Test 4: Om vi tar bort Villa Villerkulla så skall både Pippi och Herr Nilsson tas bort eftersom Herr Nilsson inte längre bor på Skansen ‘
DELETE Address WHERE Id=1
SELECT * FROM Person
SELECT * FROM Address
SELECT * FROM PersonAddress
GO