Postgresql – How to you have a combined primary key for one table

database-designpostgresqlsequence

I have exactly the same question as posted here.
But in my case I do not use MySQL but Postgres. Can this be done with Postgres? All things indicate I can not use a combined index in Postgres, so assuming it can not: what solution would be most recommended?

Original question:

I have a table that contains information on invoices of a company. However, this company has two branches, and each of them has a unique invoicing sequence; a "Serie A" and "Serie B", so to speak. However, this is one single company and I do not want to create two invoice tables. Rather, I somehow want to have two different auto-increments for one table. (…)

What I am doing right now is not using the primary key as invoice number (which would be ideal), but rather using a secondary column with the invoice id, which is incremented manually (well, using a PHP script, but it's still not automatic), by checking the latest invoice for that particular series.

This is my current setup:

CREATE TABLE `invoices` (
  `id` mediumint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `invoicenumber` mediumint unsigned NOT NULL,
  `branch` enum('A','B') NOT NULL,
  `date` date NOT NULL,
  `client` varchar(100) NOT NULL
) COMMENT='' ENGINE='InnoDB';

To check the latest invoice, I run:

SELECT MAX(invoicenumber+1) AS new_invoice_number FROM invoices WHERE branch = 'A'

Best Answer

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 ).

Related Solutions

Mysql – How to you have two auto-incremental columns in one table

What you are proposing to do can only be done with MySQL cleanly under three(3) conditions

CONDITION #1 : Use the MyISAM storage engine
CONDITION #2 : Make auto_increment column part of a compound primary key
CONDITION #3 : Each auto_increment for a given type must exist in its own row
See the auto_increment documentation for MyISAM

Here is your original table layout

CREATE TABLE `invoices` ( 
  `id` mediumint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  `invoicenumber` mediumint unsigned NOT NULL, 
  `branch` enum('A','B') NOT NULL, 
  `date` date NOT NULL, 
  `client` varchar(100) NOT NULL 
) COMMENT='' ENGINE='InnoDB';

Based on the three conditions I just mentioned, here is the new proposed table layout:

CREATE TABLE `invoices` ( 
  `invoicenumber` mediumint unsigned NOT NULL auto_increment, 
  `branch` enum('A','B') NOT NULL, 
  `date` date NOT NULL, 
  `client` varchar(100) NOT NULL,
  PRIMARY KEY (branch,invoicenumber)
) COMMENT='' ENGINE='MyISAM';

Here is an example via sample data and SQL:

drop database if exists user1162541;
create database user1162541;
use user1162541
CREATE TABLE `invoices` ( 
  `invoicenumber` mediumint unsigned NOT NULL auto_increment, 
  `branch` enum('A','B') NOT NULL, 
  `date` date NOT NULL, 
  `client` varchar(100) NOT NULL,
  PRIMARY KEY (branch,invoicenumber)
) COMMENT='' ENGINE='MyISAM'; 
INSERT INTO invoices (branch,date,client) VALUES
('A',DATE(NOW()),'John'),
('B',DATE(NOW()),'Jack'),
('A',DATE(NOW()),'Jeff'),
('B',DATE(NOW()),'Joel'),
('A',DATE(NOW()),'Jane'),
('B',DATE(NOW()),'Joan'),
('A',DATE(NOW()),'June');
SELECT * FROM invoices ORDER BY branch,invoicenumber;

Here it is executed:

mysql> drop database if exists user1162541;
Query OK, 1 row affected (0.01 sec)

mysql> create database user1162541;
Query OK, 1 row affected (0.02 sec)

mysql> use user1162541
Database changed
mysql> CREATE TABLE `invoices` (
    ->   `invoicenumber` mediumint unsigned NOT NULL auto_increment,
    ->   `branch` enum('A','B') NOT NULL,
    ->   `date` date NOT NULL,
    ->   `client` varchar(100) NOT NULL,
    ->   PRIMARY KEY (branch,invoicenumber)
    -> ) COMMENT='' ENGINE='MyISAM';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO invoices (branch,date,client) VALUES
    -> ('A',DATE(NOW()),'John'),
    -> ('B',DATE(NOW()),'Jack'),
    -> ('A',DATE(NOW()),'Jeff'),
    -> ('B',DATE(NOW()),'Joel'),
    -> ('A',DATE(NOW()),'Jane'),
    -> ('B',DATE(NOW()),'Joan'),
    -> ('A',DATE(NOW()),'June');
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM invoices ORDER BY branch,invoicenumber;
+---------------+--------+------------+--------+
| invoicenumber | branch | date       | client |
+---------------+--------+------------+--------+
|             1 | A      | 2012-04-21 | John   |
|             2 | A      | 2012-04-21 | Jeff   |
|             3 | A      | 2012-04-21 | Jane   |
|             4 | A      | 2012-04-21 | June   |
|             1 | B      | 2012-04-21 | Jack   |
|             2 | B      | 2012-04-21 | Joel   |
|             3 | B      | 2012-04-21 | Joan   |
+---------------+--------+------------+--------+
7 rows in set (0.00 sec)

mysql>

Give it a Try !!!

CAVEAT : At present, only the MyISAM Storage Engine supports multiple auto_increment values grouped with other columns. This is not possible with InnoDB based on auto_increment columns being tied directly to the gen_clust_index (aka Clustered Index) !!!

Postgresql – postgres composite index design

I would try making two composite partial indexes like

CREATE INDEX idx1 ON tw_schedules (scenario_id, type_well_id) WHERE type_well_id IS NOT NULL;

CREATE INDEX idx2 ON tw_schedules (scenario_id, tw_import_id) WHERE tw_import_id IS NOT NULL;

But, of course, everything depends on the selectivity of these indexes.

Best Answer

Related Solutions

Mysql – How to you have two auto-incremental columns in one table

Postgresql – postgres composite index design

Related Question