From: | Kaiting Chen <ktchen14(at)gmail(dot)com> |
---|---|
To: | Wolfgang Walther <walther(at)technowledgy(dot)de> |
Cc: | James Coleman <jtc331(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org, hellopfm(at)gmail(dot)com, Peter Eisentraut <peter(dot)eisentraut(at)enterprisedb(dot)com>, David Rowley <dgrowleyml(at)gmail(dot)com> |
Subject: | Re: Allow foreign keys to reference a superset of unique columns |
Date: | 2022-09-27 22:09:33 |
Message-ID: | CA+CLzG_cC66Ha_cC7FA8Vw-OW3X7ngZra47vNGgR7+WwsrCjFA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> Could we create this additional unique constraint implicitly, when using
> FOREIGN KEY ... REFERENCES on a superset of unique columns? This would
> make it easier to use, but still give proper information_schema output.
Currently, a foreign key declared where the referenced columns have only a
unique index and not a unique constraint already populates the constraint
related columns of information_schema.referential_constraints with NULL. It
doesn't seem like this change would require a major deviation from the
existing
behavior in information_schema:
CREATE TABLE foo (a integer, b integer);
CREATE UNIQUE INDEX ON foo (a, b);
CREATE TABLE bar (
x integer,
y integer,
FOREIGN KEY (x, y) REFERENCES foo(a, b)
);
# SELECT * FROM information_schema.referential_constraints
WHERE constraint_name = 'bar_x_y_fkey';
-[ RECORD 1 ]-------------+----------------------------------------------
constraint_catalog | kaitingc
constraint_schema | public
constraint_name | bar_x_y_fkey
unique_constraint_catalog |
unique_constraint_schema |
unique_constraint_name |
match_option | NONE
update_rule | NO ACTION
delete_rule | NO ACTION
The only change would be to information_schema.key_column_usage:
# SELECT * FROM information_schema.key_column_usage
WHERE constraint_name = 'bar_x_y_fkey';
-[ RECORD 173
]---------------+----------------------------------------------
constraint_catalog | kaitingc
constraint_schema | public
constraint_name | bar_x_y_fkey
table_catalog | kaitingc
table_schema | public
table_name | bar
column_name | x
ordinal_position | 1
position_in_unique_constraint | 1
-[ RECORD 174
]---------------+----------------------------------------------
constraint_catalog | kaitingc
constraint_schema | public
constraint_name | bar_x_y_fkey
table_catalog | kaitingc
table_schema | public
table_name | bar
column_name | y
ordinal_position | 2
position_in_unique_constraint | 2
Where position_in_unique_constraint would have to be NULL for the referenced
columns that don't appear in the unique index. That column is already
nullable:
For a foreign-key constraint, ordinal position of the referenced column
within
its unique constraint (count starts at 1); otherwise null
So it seems like this would be a minor documentation change at most. Also,
should that documentation be updated to mention that it's actually the
"ordinal
position of the referenced column within its unique index" (since it's a
little
confusing that in referential_constraints, unique_constraint_name is NULL)?
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-09-27 22:25:45 | Re: Allow foreign keys to reference a superset of unique columns |
Previous Message | Kaiting Chen | 2022-09-27 21:58:31 | Re: Allow foreign keys to reference a superset of unique columns |