CREATE TABLE tickets
(dept enum('NEW', 'DEP'),
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (dept, id),
other column defs
);You'll get a separate sequence for id, starting with 1, for each value of dept. This works for MYISAM and BDB tables. See the manual for more <http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html>.
So, to create a row, you would insert the appropriate value for dept and let mysql assign the id. To retrieve rows, you would do something like
SELECT CONCAT(dept, '-', id) AS TicketID, other columns FROM tickets ...
This makes sense so long as the dept of a ticket won't change, because changing the dept will likely cause id collisions. If dept can change, then you probably want the auto_increment id alone to be your primary key, with dept prepended to it for display purposes only.
Michael
electroteque wrote:
cool so i create a unique field say ticket_number , then when inserting concat it to look like NEW-20040414-01 or NEW-01 u reckon ?
-----Original Message----- From: Terence [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 14, 2004 7:29 PM To: [EMAIL PROTECTED] Subject: Re: two auto-inc fields possible ?
nope i don't think it's possible BUT you can use the concat feature to get the result:
select concat('NEW-',running_id), concat('DEP-',running_id')
so long as your running numbers are the same...we use this for our helpdesk system too :)
good luck.
----- Original Message ----- From: "electroteque" <[EMAIL PROTECTED]>
Hi there i was wondering if its possible to have two auto-inc fields ? I am having to generate ticket numbers and i want it to look like DEP-00001, DEP-00002, NEW-00001, NEW-000002 where NEW and DEP are the first 3 letters of of the department associated with the ticket. How is this possible ? I've noticed postgres has the serial data type is there an equiv in mysql ?
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
