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/[email protected]
>
