All you need is an extra table and a couple triggers.
The concept is fairly straight forward:
- Create a before insert trigger on the child table that validates the parent exists in the parent table.
- If there is no parent found, then insert a row into a third table that automatically forces an error (say, dup key on index)
- Create a before delete trigger on the parent table that validate there are no children in the child table.
- If there are children found, then insert a row into the third table that automatically forces an error (dup key on index)
Here's an example:
- Note: This example uses Innodb tables, but it can be done with MyISAM tables just as well.
Parent Table (orders) :
CREATE TABLE IF NOT EXISTS orders
(
id INT NOT NULL AUTO_INCREMENT,
customer_name VARCHAR(30) NOT NULL,
total_amount DECIMAL,
order_date DATETIME ,
PRIMARY KEY (id,customer_name)
)
ENGINE=innodb
PARTITION BY RANGE (id)
(
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (20),
PARTITION p3 VALUES LESS THAN (30),
PARTITION p4 VALUES LESS THAN (40),
PARTITION p_max VALUES LESS THAN MAXVALUE
);
Child Table (order_items) :
CREATE TABLE IF NOT EXISTS order_items
(
id INT NOT NULL AUTO_INCREMENT,
order_id INT NOT NULL ,
item_name VARCHAR(30) NOT NULL,
item_desc VARCHAR(4000),
item_amount DECIMAL,
PRIMARY KEY (id,order_id)
)
ENGINE=innodb
PARTITION BY HASH (order_id)
PARTITIONS 20
;
Special Table (fk_trigger) used to enforce FK:
CREATE TABLE IF NOT EXISTS fk_trigger
(
id INT NOT NULL PRIMARY KEY DEFAULT 0
)
ENGINE=innodb
;
Insert default row into special table (fk_trigger)
that will force all inserts to fail:
INSERT INTO fk_trigger VALUES();
Child Trigger (order_item_fk_trig) used to check
that parent row exists before insert:
DELIMITER /
CREATE TRIGGER order_item_fk_trig
BEFORE INSERT ON order_items
FOR EACH ROW BEGIN
DECLARE my_count INT;
SELECT count(*)
INTO @my_count
FROM orders
WHERE id = NEW.order_id;
IF (@my_count = 0) THEN
INSERT INTO fk_trigger VALUES ();
END IF;
END;
/
DELIMITER ;
Parent Trigger (order_fk_trig) used to check
that child row exists before delete:
DELIMITER /
CREATE TRIGGER order_fk_rev_trig
BEFORE DELETE ON orders
FOR EACH ROW BEGIN
DECLARE my_count INT;
SELECT count(*)
INTO @my_count
FROM order_items
WHERE order_id = OLD.id;
IF (@my_count > 0) THEN
INSERT INTO fk_trigger VALUES ();
END IF;
END;
/
DELIMITER ;
Insert some data into the parent table
INSERT INTO orders
(customer_name,total_amount,order_date)
VALUES
('Customer 1',300.00,now()),
('Customer 2',600.00,now());
Insert some data into the child table
INSERT INTO order_items
(order_id,item_name,item_desc, item_amount)
VALUES
(1,'This 1','Stuff 1',100),
(1,'This 2','Stuff 2',100),
(1,'This 3','Stuff 3',100),
(2,'This 1','Stuff 1',200),
(2,'This 2','Stuff 2',200),
(2,'This 3','Stuff 3',200);
Try to delete order #1 (without deleting it's children)
DELETE FROM orders
WHERE id = 1;
Results:
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
Try to insert orderitems for order #3 (Which doesn't exist)
INSERT INTO order_items
(order_id,item_name,item_desc, item_amount)
VALUES
(3,'This 3','Stuff 1',100);
Results:
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
