Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database where I want to generate unique tokens of six numeric characters. These tokens should be generated each time an invoice number is inserted in the invoice_no column. This would mean that every time I do an insert and the column invoice_no is not null, the token is generated.

I want the token to be to generate as many tokens as possible without a token being repeated.

create table ledgers( invoice_no varchar(10), 
                      debit numeric, 
                      credit numeric, 
                      details varchar(20), 
                      token char(6) );
share|improve this question
    
Are you ever going to update a token manually or will you always only have generated tokens in there? –  Jack Douglas Aug 16 '12 at 10:12
    
shoukd be generated on the fly @JackDouglas –  G. Ngotho Aug 16 '12 at 11:51

1 Answer 1

up vote 3 down vote accepted

If you don't mind the unique token being sequential, you can use a sequence, like so:

CREATE TABLE invoices
(
    invoice_no integer,
    token char(6)
);

CREATE SEQUENCE invoices_token_seq START WITH 100000;

ALTER TABLE invoices 
ALTER COLUMN token
SET DEFAULT CAST(NEXTVAL('invoices_token_seq') AS CHAR(6));

To test:

postgres=# INSERT INTO invoices ( invoice_no ) VALUES ( 1 );
INSERT 0 1
postgres=#
postgres=# SELECT * FROM invoices;
 invoice_no | token
------------+--------
          1 | 100000
(1 row)

postgres=#

Note that I have used CAST as you specified that the token should be "numeric characters".

The above is the same as using an AUTO_INCREMENT column in MySQL.

However, to accommodate for the fact that you only want to generate a token when the invoice_no IS NOT NULL, you need to use a trigger as follows:

create table ledgers
(
    invoice_no varchar(10), 
    debit numeric(6,2), 
    credit numeric(6,2), 
    details varchar(20), 
    token char(6) 
);

CREATE SEQUENCE ledgers_token_seq START WITH 100000;

CREATE OR REPLACE FUNCTION token_insert() RETURNS trigger AS '
BEGIN
  IF NEW.invoice_no IS NOT NULL
  THEN
    NEW.token := CAST(NEXTVAL(''ledgers_token_seq'') AS CHAR(6));
    END IF;
  RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER ledgers_insert BEFORE INSERT ON ledgers FOR EACH ROW EXECUTE PROCEDURE token_insert();

Test:

postgres=# INSERT INTO ledgers ( invoice_no, debit, credit, details ) VALUES ( 1, 2, 3, 'NOT NULL invoice_no' );
INSERT 0 1
postgres=#
postgres=# INSERT INTO ledgers ( debit, credit, details ) VALUES ( 2, 3, 'NULL invoice_no' );
INSERT 0 1
postgres=#
postgres=# select * from ledgers;
 invoice_no | debit | credit |       details       | token
------------+-------+--------+---------------------+--------
 1          |  2.00 |   3.00 | NOT NULL invoice_no | 100000
            |  2.00 |   3.00 | NULL invoice_no     |
(2 rows)

postgres=#
share|improve this answer
    
let's say I have a table like this create table ledgers(invoice_no varchar(10), debit numeric(), credit numeric(), details varchar(20), token char(6) now some records may not have an invoice number and therefore would not generate a token. I want the token to o ly be generated when an invoice_no gets a value. I hope you got me right. –  G. Ngotho Aug 16 '12 at 7:55
    
Answer edited accordingly –  Phil Aug 16 '12 at 8:07
    
One invoice number could be repeated in more than 1 row, while I want my token to be unique not to a row but to an invoice @Phill ? –  G. Ngotho Aug 16 '12 at 15:00
1  
an auto-increment (or sequence) might result in gaps when a transaction isn't successful, it's not locked. This might be a problem for invoices. –  Frank Heikens Aug 16 '12 at 17:43
    
The Q states unique –  Phil Aug 16 '12 at 17:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.