sql: permit multiple foreign keys with intersecting outbound columns

[jordanlewis]

Currently, we do not permit a table to have multiple outbound foreign keys when those foreign keys have column sets with a non-zero intersection.

For example, consider the following schema:

[email protected]:61088/defaultdb> show create table users;
  table_name |                create_statement
+------------+------------------------------------------------+
  users      | CREATE TABLE users (
             |     id INT8 NOT NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (id ASC),
             |     FAMILY "primary" (id)
             | )
(1 row)

Time: 4.682ms

[email protected]:61088/defaultdb> show create table friendships;
  table_name  |                           create_statement
+-------------+----------------------------------------------------------------------+
  friendships | CREATE TABLE friendships (
              |     user_id_a INT8 NOT NULL,
              |     user_id_b INT8 NOT NULL,
              |     CONSTRAINT "primary" PRIMARY KEY (user_id_a ASC, user_id_b ASC),
              |     FAMILY "primary" (user_id_a, user_id_b)
              | )
(1 row)

If you wanted to create a table that allowed a user to comment on one of their friendships, that ensured referential integrity by checking that one of their friendships existed, you couldn't do so without a workaround:

[email protected]:61088/defaultdb> create table friendships_comments(commenting_user_id int references users(id), friendship_user int, foreign key (commenting_user_id, friendship_user) references friendships(user_id_a, user_id_b));
pq: columns cannot be used by multiple foreign key constraints

[otan]

From my basic testing today, removing a check still works, except we need to be smarter about renaming foreign keys -- e.g.

# add a million of these
alter table messages add foreign key (user_id_a, user_id_b) references creepy_c(a, b);

should result in automatically renamed a la postgres (but fails in cockroach because it tries to give it the same name):

                              Table "public.messages"
  Column   |            Type             | Collation | Nullable |      Default
-----------+-----------------------------+-----------+----------+-------------------
 user_id_a | integer                     |           | not null |
 user_id_b | integer                     |           | not null |
 t         | timestamp without time zone |           | not null | CURRENT_TIMESTAMP
 message   | text                        |           | not null |
Indexes:
    "messages_pkey" PRIMARY KEY, btree (user_id_a, user_id_b, t)
Foreign-key constraints:
    "messages_user_id_a_fkey" FOREIGN KEY (user_id_a) REFERENCES users(id) ON DELETE CASCADE
    "messages_user_id_a_fkey1" FOREIGN KEY (user_id_a, user_id_b) REFERENCES creepy_b(a, b)
    "messages_user_id_a_fkey2" FOREIGN KEY (user_id_a, user_id_b) REFERENCES creepy_c(a, b)
    "messages_user_id_a_fkey3" FOREIGN KEY (user_id_a, user_id_b) REFERENCES creepy_c(a, b)
    "messages_user_id_a_fkey4" FOREIGN KEY (user_id_a, user_id_b) REFERENCES creepy_c(a, b)
    "messages_user_id_b_fkey" FOREIGN KEY (user_id_b) REFERENCES users(id) ON DELETE RESTRICT
    "must_be_friends" FOREIGN KEY (user_id_a, user_id_b) REFERENCES friendships(user_id_a, user_id_b) ON DELETE CASCADE

not totally sure what behaviours would be deemed ambiguous in

-       // Multiple FKs from the same column would potentially result in ambiguous or
-       // unexpected behavior with conflicting CASCADE/RESTRICT/etc behaviors.

but it currently seems to work