Referentiële integriteit (MySQL): verschil tussen versies
Naar navigatie springen
Naar zoeken springen
(Een tussenliggende versie door dezelfde gebruiker niet weergegeven) | |||
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 | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
-- ============================================================================================= | -- ============================================================================================= | ||
Regel 197: | Regel 188: | ||
set customer_id=12 where customer_id=1; | set customer_id=12 where customer_id=1; | ||
</pre> | </pre> | ||
+ | |||
+ | == Referentiële integriteit achteraf opleggen == | ||
+ | |||
+ | Het is geen probleem om pas later referentiële integriteit af te dwingen. Bv.: | ||
+ | |||
+ | <pre> | ||
+ | ALTER TABLE `dwh`.`brush_tool` | ||
+ | ADD CONSTRAINT `tool_id` | ||
+ | FOREIGN KEY (`tool_id`) | ||
+ | REFERENCES `dwh`.`tool` (`tool_id`) | ||
+ | ON DELETE cascade | ||
+ | ON UPDATE cascade; | ||
+ | </pre> | ||
+ | |||
+ | Het wordt echter vervelender, als de bestaande records niet voldoen aan referentiële integriteit | ||
== Bronnen == | == Bronnen == |
Huidige versie van 27 apr 2017 om 11:21
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