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