Hi, On 22/01/2011, at 11:27 AM, Donovan Brooke wrote:
> Just an idear.. > > Don't auto_increment the main table.. create a unique Id table, > auto_increment that, and grab that value first for use with both fields in > your main table. This can be wrapped into a trigger, so the main table functions as usual: CREATE TABLE _sequence ( Name varchar(20) NOT NULL PRIMARY KEY, Value INT UNSIGNED NOT NULL DEFAULT 0 ) ENGINE=InnoDB; CREATE TABLE dupkey ( id INT UNSIGNED NOT NULL DEFAULT 0 PRIMARY KEY, DupKey INT UNSIGNED NOT NULL DEFAULT 0, Value VARCHAR(20) NOT NULL DEFAULT '' ) ENGINE=InnoDB; INSERT INTO _sequence VALUES ('dupkey', 0); DELIMITER // CREATE TRIGGER befins_dupkey BEFORE INSERT ON dupkey FOR EACH ROW BEGIN DECLARE v_id INT UNSIGNED; UPDATE _sequence SET Value = (LAST_INSERT_ID(Value+1)) where name = 'dupkey'; SET NEW.id := LAST_INSERT_ID(), NEW.DupKey := LAST_INSERT_ID(); END// DELIMITER ; INSERT INTO dupkey (Value) VALUES ('test 1'), ('test 2'); SELECT * FROM dupkey; +----+--------+--------+ | id | DupKey | Value | +----+--------+--------+ | 1 | 1 | test 1 | | 2 | 2 | test 2 | +----+--------+--------+ 2 rows in set (0.00 sec) Cheers, Jesper > > Donovan > > > -- > D Brooke > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=my...@wisborg.dk >