Referentiële integriteit (MySQL)

In bepaalde situaties is automatisch afdwingen of bijwerken van referentiële integriteit onmisbaar. Bv. als je data aanpast, en wilt dat die aanpassingen propageert.

Simpel voorbeeld: De kaartenbak in de openbare bibliotheek. Op het moment dat Gerard van het Reve zijn naam verandert in Gerard Reve, wil je dat die verandering ook wordt doorgevoerd in de kaarten die betrekking hebben op zijn boeken.

Simpel voorbeeld

-- =============================================================================================
-- === Create tables & populate
-- =============================================================================================
--
-- Parent table
-- ============
-- 
drop table if exists customer;
create table customer
(
    customer_id         int not null,
    name 		varchar(30),
    
    primary key(customer_id)
);

-- Child table
-- ===========
-- 
drop table if exists customer_sales;
create table customer_sales
(
    transaction_id	int not null,
    amount 		int,
    customer_id 	int not null,
    
    primary key (transaction_id),
    index (customer_id),
    foreign key (customer_id) references customer (customer_id)
);

insert into customer
values
    (1, "Vos"),
    (2, "Veldt");

insert into customer_sales
values
    (1, 123, 1),	-- transactie #1: € 123 met klant Vos
    (2, 234, 2);	-- Transactie #2: € 234 met klant Veldt

-- =============================================================================================
-- === Add child for non-existing parent
-- =============================================================================================
--
-- This creates an error: You cannot add a child for a non-existing parent "12" - Very good!
--
-- insert into customer_sales
-- values
--    (3, 345, 12);

-- =============================================================================================
-- === Cascaded delete - werkt niet!
-- =============================================================================================
--
-- You cannot just delete a parent record
-- ======================================
-- 
-- Corresponding error:
--
-- 17:16:51	delete from customer where customer_id = 1	Error Code: 1451. 
-- Cannot delete or update a parent row: a foreign key constraint fails 
-- (`tmp`.`customer_sales`, CONSTRAINT `customer_sales_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`))	
-- 0.016 sec
--
delete from customer where customer_id = 1;

Cascaded delete

Als hiervoor, maar nu inclusief on delete cascade als foreign key constraint:

-- =============================================================================================
-- === Create tables & populate
-- =============================================================================================
--
-- Parent table
-- ============
-- 
drop table if exists customer;
create table customer
(
    customer_id         int not null,
    name 		varchar(30),
    
    primary key(customer_id)
);

-- Child table
-- ===========
-- 
drop table if exists customer_sales;
create table customer_sales
(
    transaction_id	int not null,
    amount 		int,
    customer_id 	int not null,
    
    primary key (transaction_id),
    index (customer_id),
    foreign key (customer_id) references customer (customer_id)

    on delete cascade -- CASCADED DELETE
);

insert into customer
values
    (1, "Vos"),
    (2, "Veldt");

insert into customer_sales
values
    (1, 123, 1),	-- transactie #1: € 123 met klant Vos
    (2, 234, 2);	-- Transactie #2: € 234 met klant Veldt

-- =============================================================================================
-- === Cascaded delete - Werkt!
-- =============================================================================================
--
delete from customer where customer_id = 1;

Cascaded update

Als hiervoor, maar nu inclusief on delete cascade als foreign key constraint:

-- =============================================================================================
-- === Create tables & populate
-- =============================================================================================
--
-- Parent table
-- ============
-- 
drop table if exists customer;
create table customer
(
    customer_id         int not null,
    name 		varchar(30),
    
    primary key(customer_id)
);

-- Child table
-- ===========
-- 
drop table if exists customer_sales;
create table customer_sales
(
    transaction_id	int not null,
    amount 		int,
    customer_id 	int not null,
    
    primary key (transaction_id),
    index (customer_id),
    foreign key (customer_id) references customer (customer_id)

    on delete cascade
    on delete update
);

insert into customer
values
    (1, "Vos"),
    (2, "Veldt");

insert into customer_sales
values
    (1, 123, 1),	-- transactie #1: € 123 met klant Vos
    (2, 234, 2);	-- Transactie #2: € 234 met klant Veldt

-- =============================================================================================
-- === Cascaded update - Werkt!
-- =============================================================================================
--
-- Het gaat om het bijwerken van de key!
-- ==========================================
-- 
update customer
set customer_id=12 where customer_id=1;

Referentiële integriteit achteraf opleggen

Het is geen probleem om pas later referentiële integriteit af te dwingen. Bv.:

ALTER TABLE `dwh`.`brush_tool` 
ADD CONSTRAINT `tool_id`
  FOREIGN KEY (`tool_id`)
  REFERENCES `dwh`.`tool` (`tool_id`)
  ON DELETE cascade
  ON UPDATE cascade;

Het wordt echter vervelender, als de bestaande records niet voldoen aan referentiële integriteit

Bronnen

• https://dev.mysql.com/tech-resources/articles/mysql-enforcing-foreign-keys.html
• https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html