MySQL Partitioned Tables with Trigger enforced FK constraints

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:

1. Create a before insert trigger on the child table that validates the parent exists in the parent table.
2. If there is no parent found, then insert a row into a third table that automatically forces an error (say, dup key on index)
   
3. Create a before delete trigger on the parent table that validate there are no children in the child table.
4. 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'