Referentiële integriteit (MySQL): verschil tussen versies
Naar navigatie springen
Naar zoeken springen
(4 tussenliggende versies door dezelfde gebruiker niet weergegeven) | |||
Regel 58: | Regel 58: | ||
-- ============================================================================================= | -- ============================================================================================= | ||
− | -- === Cascaded delete | + | -- === Cascaded delete - werkt niet! |
-- ============================================================================================= | -- ============================================================================================= | ||
-- | -- | ||
Regel 73: | Regel 73: | ||
delete from customer where customer_id = 1; | delete from customer where customer_id = 1; | ||
</pre> | </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> | ||
+ | |||
+ | == 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 10: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