Thread: Typical foreign key creation question

Typical foreign key creation question

From
Sean Davis
Date:
I am migrating from mysql to postgresql.  I have many auto_increment
columns, all requiring code during loading to explicitly save the last
insert id of one table to place into another linked table.  I know that
I can probably do this with sequences and directly selecting current
value.  However, I would like to learn to do this more simply in
postgresql using views/triggers.  I have looked for a code example
showing the "canonical" linked table creation idiom in postgresql, but
have not found it.  I have tables that look like:

CREATE TABLE abixref (
                                  xrefid int,
                                 platepos      INT NOT NULL,
                                 gene_id       VARCHAR(30),
                                 value         VARCHAR(75),
                                 dbid          INT NOT NULL
                             );

CREATE TABLE abidb (
                            dbid         INT NOT NULL,
                            db           VARCHAR(30),
                           );

Of course, dbid is the foreign key with abidb referencing the column of
the same name in the other table.

Thanks,
Sean