Referentiële integriteit (MySQL): verschil tussen versies
Naar navigatie springen
Naar zoeken springen
| Regel 108: | Regel 108: | ||
index (customer_id), | index (customer_id), | ||
foreign key (customer_id) references customer (customer_id) | foreign key (customer_id) references customer (customer_id) | ||
| + | |||
on delete cascade -- CASCADED DELETE | on delete cascade -- CASCADED DELETE | ||
); | ); | ||
| Regel 120: | Regel 121: | ||
(1, 123, 1), -- transactie #1: € 123 met klant Vos | (1, 123, 1), -- transactie #1: € 123 met klant Vos | ||
(2, 234, 2); -- Transactie #2: € 234 met klant Veldt | (2, 234, 2); -- Transactie #2: € 234 met klant Veldt | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
-- ============================================================================================= | -- ============================================================================================= | ||
Versie van 26 apr 2017 15:44
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;