On 8/12/19 8:51 AM, stan wrote:
Please reply to list also.
Ccing list.
On Mon, Aug 12, 2019 at 08:17:33AM -0700, Adrian Klaver wrote:
On 8/12/19 8:11 AM, stan wrote:
I am creating a table that has 2 values in it which are keys pointing to 2
other tables. I need for the UNIQUE combination of these 2 keys to exist in
a fourth table. It has been recommended to use a foreign key constraint with
the MATCH FULL parameter.
Without the table schema it difficult for me to figure out what it is you
are attempting.
See below for how MATCH FULL works:
https://www.postgresql.org/docs/11/sql-createtable.html
From that page:
MATCH FULL will not allow one column of a multicolumn foreign key to be null
unless all foreign key columns are null; if they are all null, the row is
not required to have a match in the referenced table.
I think that means that what I am trying to enforce will not work.
The table the insert is on has 2 columns, each of these is a foreign key to
other table. I need the unique combination of these 2 keys to exist in a 3rd
table that is a rate table. It has 3 columns, key 1, key 2, and rate. Looks
like to me, if neither of the 2 keys are in the rate table the constraint
will allow the insert. Do I have this wrong?
The docs are referring to a multicolumn FK so something like:
create table parent_tbl(fld_1 integer, fld_2 integer, UNIQUE(fld_1, fld_2));
create table child_tbl(fk_fld_1 integer, fk_fld_2 integer, FOREIGN KEY
(fk_fld_1, fk_fld_2) REFERENCES parent_tbl(fld_1, fld_2));
\d child_tbl
Table "public.child_tbl"
Column | Type | Collation | Nullable | Default
----------+---------+-----------+----------+---------
fk_fld_1 | integer | | |
fk_fld_2 | integer | | |
Foreign-key constraints:
"child_tbl_fk_fld_1_fkey" FOREIGN KEY (fk_fld_1, fk_fld_2)
REFERENCES parent_tbl(fld_1, fld_2)
Not sure what your setup is. That is why it is important to show the
actual schema.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
