Well, I suppose its' true you can't use the official MySQL ALTER TABLE statement to do it, but if you're willing to trust your trigger coding abilities you can.
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)
It could also be extended to handle cascading deletes, but I'm personally not a fan of that, so I leave that to you... :)
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'
