Обсуждение: serial data type usage

I have a table where I would like the primary key to be generated during
the insert.

Here is a simplified example:


CREATE TABLE employee_type
{
    tname varchar(10) PRIMARY KEY,
    id_prefix char(1) ;
    ...
}

tname      | id_prefix
--------------+----------
worker     | W
manager  | M
executive | E

CREATE TABLE employee {
    id varchar(10) PRIMARY KEY,
    type varchar(10) REFERENCES employee_type
    ...
}

When an employee of type 'worker' is inserted the id generated will have
a prefix "W" followed by a 6-digit number. W000001, W000002 ..
When the employee type is 'manager' the employee id is M000001, M000002
...
When the employee type is 'executive' the employee id is E000001,
E000002 ...

The sequences for each employee type are separate.

CREATE SEQUENCE worker_seqnum MINVALUE 1 MAXVALUE 999999 ;
CREATE SEQUENCE manger_seqnum MINVALUE 1 MAXVALUE 999999 ;
CREATE SEQUENCE executive_seqnum MINVALUE 1 MAXVALUE 999999 ;


I have thought about using the serial data type for the employee.id but
I also want to automate the prepending of the { W, M, E } prefix.

Any suggestions?

On Thursday 06 November 2008, "EXT-Rothermel, Peter M"
<Peter.M.Rothermel@boeing.com> wrote:
> I have thought about using the serial data type for the employee.id but
> I also want to automate the prepending of the { W, M, E } prefix.
>

You'll need write a before-insert trigger to assign the ID.

--
Alan

EXT-Rothermel, Peter M wrote:
> I have a table where I would like the primary key to be generated during
> the insert.
>
> Here is a simplified example:
>
>
> CREATE TABLE employee_type
> {
>     tname varchar(10) PRIMARY KEY,
>     id_prefix char(1) ;
>     ...
> }
>
> tname      | id_prefix
> --------------+----------
> worker     | W
> manager  | M
> executive | E
>
> CREATE TABLE employee {
>     id varchar(10) PRIMARY KEY,
>     type varchar(10) REFERENCES employee_type
>     ...
> }
>
> When an employee of type 'worker' is inserted the id generated will have
> a prefix "W" followed by a 6-digit number. W000001, W000002 ..
> When the employee type is 'manager' the employee id is M000001, M000002
> ...
> When the employee type is 'executive' the employee id is E000001,
> E000002 ...
>
> The sequences for each employee type are separate.
>
> CREATE SEQUENCE worker_seqnum MINVALUE 1 MAXVALUE 999999 ;
> CREATE SEQUENCE manger_seqnum MINVALUE 1 MAXVALUE 999999 ;
> CREATE SEQUENCE executive_seqnum MINVALUE 1 MAXVALUE 999999 ;
>
>
> I have thought about using the serial data type for the employee.id but
> I also want to automate the prepending of the { W, M, E } prefix.
>
> Any suggestions?

Do not put the worker type and worker sequence value in the same
column ... you're violating normal form. Keep them in separate
columns in the employee table, drop the type column from employee
(it's redundant given the first character of your proposed
primary key which, as I said, should be a separate column
anyway), define a compound primary key (id_prefix, id) on it, and
use a view for presentation in the concatenated format you desire
along with the redundant (but more verbose) type value by way of
   a join. Secondly, you don't need the three separate sequences,
but you do need to sort of roll your own based on my description
appearing at

http://archives.postgresql.org/pgsql-general/2004-04/msg00940.php


-- BMT