Sharing a single primary key sequence across a database

Postgres has sequences, and you can definitely use more than 1 of them for the same table. For example, the following code generates value for invoiceNumber column based on invoiceType in the trigger :

create sequence seq_1;
create sequence seq_2;

create table table1(invoiceType varchar(10) not null,
invoiceNumber int not null,
constraint PK_table1 PRIMARY KEY (invoiceType,invoiceNumber),
constraint CHK_invoiceType CHECK (invoiceType IN ('Type1','Type2')));

CREATE OR REPLACE FUNCTION table1_seq_generator() RETURNS trigger AS $body_start$
    BEGIN
        IF NEW.invoiceType = 'Type1' THEN 
          NEW.invoiceNumber := nextval('seq_1');
        ELSIF NEW.invoiceType ='Type2' THEN
          NEW.invoiceNumber := nextval('seq_2');
        END IF;
        RETURN NEW;
    END;
$body_start$ LANGUAGE plpgsql;

CREATE TRIGGER biTable1 BEFORE INSERT ON table1
FOR EACH ROW
WHEN (NEW.invoiceNumber IS NULL) 
   EXECUTE PROCEDURE table1_seq_generator();

---------------Testing ----------------------------
   INSERT INTO table1(invoiceType) values ('Type1');
   INSERT INTO table1(invoiceType) values ('Type2');
   INSERT INTO table1(invoiceType) values ('Type1');
   INSERT INTO table1(invoiceType) values ('Type2');
   SELECT * FROM table1;

You don't necessary have to do it in trigger, application code itself can get nextval for different sequence.

Also, I'd not use invoiceType,invoiceNumber as primary key constraint, but rather have surrogate pk and unique constraint on invoiceType,invoiceNumber (or branch,invoiceNumber according to your naming ).

I currently work in this field. This is a case where a surrogate key should be used.

Our software probably deals with a much wider range of scenarios than yours, but that extra flexibility may mean a lot for you in the future.

• Student numbers (overall) may change quite frequently depending on how the numbers are assigned by the distribution authority.

  When a new student is enrolled, a number or unique identifier may have to be internally assigned to the student before the authority has actually generated a number for the student (asynchronously). In this case, it may be necessary to use more than one field in the database... which crushes the primary key idea immediately.

  Student numbers that change are just bad news for lots of reasons. Remember that the primary key in this table will be propagated to many parts of the system, and with many associated rows.

• Performance is rarely a concern for single-student data operations. Depending on how the indexes are set up, using the student number as an alternate key may have a slight performance hit. However, at least in my experience, it's pretty rare that this is a significant user experience issue. You're more likely to run into problems with performance of batch processes and reporting that looks at groups of students (by school, by grade, by class, report cards, etc.).

• The length and format of a student number varies by jurisdiction. Some may be numeric, some may be alphanumeric. Do you need to handle more than one format? If a numeric student number is used as the primary key now, you set yourself up for a world of hurt later if you need to change to alphanumeric (and not only from a database perspective!).

  Also, if that happens (or if you use alphanumeric to start), performance will be diminished, as string comparison is always going to be less efficient than integer comparison.

That you can guarantee the student number is unique is good. Make sure to enforce it using appropriate uniqueness and validation constructs.

Use the student number (alone, or in combination with other business keys) whenever it's necessary to integrate between your application and another.

Your software may fit a narrow box of a situation where it looks "okay" to use the student number as a primary key. If that's the case, I cannot offer many negatives about that approach. However, not using a surrogate now sets you up for pain down the road if you need to venture even an inch out of that box.