4nfLjHNQ

1MySQL: Grouped primary key with InnoDB
2CREATE TABLE images (
3   parent_id INT(11) NOT NULL,
4   image_id INT(11) AUTO_INCREMENT, //will this work with a grouped primary key?
5   source_url VARCHAR(255) NOT NULL,
6   caption VARCHAR(255) NOT NULL,
7) ENGINE = InnoDB;
8	
9PRIMARY KEY(parent_id, image_id)
10	
11delimiter //
12DROP TRIGGER IF EXISTS test_img_trigger;
13CREATE TRIGGER test_img_trigger BEFORE INSERT ON venue_images
14FOR EACH ROW
15BEGIN
16DECLARE img_id INT UNSIGNED DEFAULT 0;
17
18SELECT image_id + 1 INTO img_id FROM venue_images WHERE venue_id = NEW.venue_id;
19UPDATE venue_images SET image_id = img_id WHERE venue_id = NEW.venue_id;
20
21END IF;
22END;//
23delimiter;
24	
25CREATE TABLE images (
26   parent_id INT(11) NOT NULL,
27   image_id INT(11) Not Null  AUTO_INCREMENT, 
28   source_url VARCHAR(255) NOT NULL,
29   caption VARCHAR(255) NOT NULL,
30
31   key(image_id),                          -- add as a key       
32   PRIMARY KEY(parent_id, image_id)
33) ENGINE = InnoDB;
34	
35CREATE TABLE image_seq (
36    parent_id INT(11) NOT NULL,
37    catagory INT(11) NOT NULL,
38    next_seq_id INT(11) NOT NULL,
39    KEY(catagory),
40    PRIMARY KEY(parent_id, catagory);
41);
42	
43CREATE TRIGGER bi_image_trig
44BEFORE INSERT ON images
45FOR EACH ROW
46BEGIN
47
48DECLARE id INT UNSIGNED DEFAULT 1;
49 SELECT next_seq_id + 1 INTO id FROM image_seq WHERE parent_id=NEW.parent_id AND       catagory=NEW.catagory;
50SET NEW.image_id = id;
51UPDATE image_seq SET next_seq_id=id WHERE parent_id=NEW.parent_id AND catagory=NEW.catagory;
52
53END;