Referentiële integriteit (MySQL): verschil tussen versies
Naar navigatie springen
Naar zoeken springen
(4 tussenliggende versies door dezelfde gebruiker niet weergegeven) | |||
Regel 3: | Regel 3: | ||
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: 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 == |
<pre> | <pre> | ||
+ | -- ============================================================================================= | ||
+ | -- === Create tables & populate | ||
+ | -- ============================================================================================= | ||
+ | -- | ||
+ | -- Parent table | ||
+ | -- ============ | ||
+ | -- | ||
drop table if exists customer; | drop table if exists customer; | ||
create table customer | create table customer | ||
( | ( | ||
− | + | customer_id int not null, | |
name varchar(30), | name varchar(30), | ||
Regel 15: | Regel 22: | ||
); | ); | ||
+ | -- Child table | ||
+ | -- =========== | ||
+ | -- | ||
drop table if exists customer_sales; | drop table if exists customer_sales; | ||
create table customer_sales | create table customer_sales | ||
( | ( | ||
− | + | transaction_id int not null, | |
− | amount | + | amount int, |
customer_id int not null, | customer_id int not null, | ||
Regel 35: | Regel 45: | ||
values | values | ||
(1, 123, 1), -- transactie #1: € 123 met klant Vos | (1, 123, 1), -- transactie #1: € 123 met klant Vos | ||
− | (2, 234, 2) | + | (2, 234, 2); -- Transactie #2: € 234 met klant Veldt |
− | -- (3, 345, 12); -- | + | |
+ | -- ============================================================================================= | ||
+ | -- === 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; | ||
+ | </pre> | ||
+ | |||
+ | == Cascaded delete == | ||
+ | |||
+ | Als hiervoor, maar nu inclusief <code>on delete cascade</code> als ''foreign key constraint'': | ||
+ | |||
+ | <pre> | ||
+ | -- ============================================================================================= | ||
+ | -- === 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; | ||
+ | </pre> | ||
+ | |||
+ | == Cascaded update == | ||
+ | |||
+ | Als hiervoor, maar nu inclusief <code>on delete cascade</code> als ''foreign key constraint'': | ||
+ | |||
+ | <pre> | ||
+ | -- ============================================================================================= | ||
+ | -- === 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; | ||
</pre> | </pre> | ||
Versie van 26 apr 2017 14: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;