Can a Foreign Key Reference a Non-Unique Index

Can a foreign key reference a non-unique index?

From MySQL documentation:

InnoDB allows a foreign key constraint to reference a non-unique key. This is an InnoDB extension to standard SQL.

However, there is a pratical reason to avoid foreign keys on non-unique columns of referenced table. That is, what should be the semantic of "ON DELETE CASCADE" in that case?

The documentation further advises:

The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined (...) You are advised to use foreign keys that reference only UNIQUE (including PRIMARY) and NOT NULL keys.

Foreign key on a non-unique index? (oracle)

One technique would be to use a materialised view (fast refresh on commit) to store the unique values of the referenced column, and constrain your table against that.

Attempts at using triggers to enforce integrity are generally doomed due to read consistency or locking issues.

Mysql foreign key by non unique key -- how is that possible?

From the manual:

Deviation from SQL standards: A
FOREIGN KEY constraint that references
a non-UNIQUE key is not standard SQL.
It is an InnoDB extension to standard
SQL.

So it looks like InnoDB allows non-unique indexes as candidates for foreign key references. Elsewhere the manual states that you can reference a subset of columns in the referenced index as long as the referenced columns are listed first and in the same order as the primary key.

Therefore, this definition is legal in InnoDB, although it's not standard SQL and leaves me, at least, a little confused as to the original designer's intentions.

Manual page here.

MySQL: FKs to non-unique column

This is a peculiarity of the implementation of InnoDB. The foreign key column(s) must reference the leftmost column(s) of any index. You can make it reference a non-unique index, as you discovered.

You can also make it reference a leftmost subset of columns in a unique index:

create table parent (id1 int, id2 int, primary key (id1, id2));

create table child (id1 int, foreign key (id1) references parent(id1) on delete cascade);

But this is nonstandard, and incompatible with other SQL databases. It brings up uncomfortable questions:

mysql> insert into parent values (1,1), (1,2);
mysql> insert into child values (1);
mysql> delete from parent where id1=1 and id2=1;
mysql> select * from child;
Empty set (0.00 sec)

It seems that if any row referenced by the foreign key is deleted, then this causes the delete to cascade. Is this what is desired? Even though there still exists a row in parent that satisfies the foreign key reference?

mysql> select * from parent;
+-----+-----+
| id1 | id2 |
+-----+-----+
|   1 |   2 |
+-----+-----+

Even though it is allowed by InnoDB, I strongly recommend you don't design your tables to depend on it. Keep making foreign keys reference only primary keys or unique keys, and only the complete set of columns of those keys.

does foreign key always reference to a unique key in another table?

By the SQL standard, a foreign key must reference either the primary key or a unique key of the parent table. If the primary key has multiple columns, the foreign key must have the same number and order of columns. Therefore the foreign key references a unique row in the parent table; there can be no duplicates.

Re your comment:

If T.A is a primary key, then no you can't have any duplicates. Any primary key must be unique and non-null. Therefore if the child table has a foreign key referencing the parent's primary key, it must match a non-null, unique value, and therefore references exactly one row in the parent table. In this case you can't make a child row that references multiple parent rows.

You can create a child row whose foreign key column is NULL, in which case it references no row in the parent table.

Foreign Key to non-primary key

If you really want to create a foreign key to a non-primary key, it MUST be a column that has a unique constraint on it.

From Books Online:

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY
KEY constraint in another table; it can also be defined to reference
the columns of a UNIQUE constraint in another table.

So in your case if you make AnotherID unique, it will be allowed. If you can't apply a unique constraint you're out of luck, but this really does make sense if you think about it.

Although, as has been mentioned, if you have a perfectly good primary key as a candidate key, why not use that?

Should I index my foreign keys if they are already part of a unique constraint?

It is a matter of taste, but personally I don't think you need the surrogate key (id) here. (is it ever used?) Also: role is a (non reserved) keyword. Avoid using it as identifier.

For the foreign keys, an index is absolutely necessary, otherwise a CASCADEing delete or update would (internally) lead to a sequential scan for every deleted/updated user- or project- tuple.

For a junction (bridge) table like this, it is sufficient to create an index (or UNIQUE constraint) with the key elements in the reversed order. This also serves as a supporting index for the FK. [the first element(s) of a composite index can be used as if an index with only these fields existed]

The extra key field in the index can enable index-only scans (for instance: when the the_role field is not needed)

CREATE TABLE project_ownerships
  ( project_id BIGINT REFERENCES projects (id) ON DELETE CASCADE
  , user_id BIGINT REFERENCES users(id) ON DELETE CASCADE
  , the_role INTEGER
  , PRIMARY KEY  (project_id, user_id)
  , CONSTRAINT reversed_pk UNIQUE (user_id, project_id)
  );

A small test-setup
(I need to disable sort and hashjoin, because for small tables like this these actually lead to cheaper plans ;-)

SET search_path=tmp;
SELECT version();

CREATE TABLE projects
        ( id bigserial not NULL PRIMARY KEY
        , the_name text UNIQUE
        );

CREATE TABLE users
        ( id bigserial not NULL PRIMARY KEY
        , the_name text UNIQUE
        );

CREATE TABLE project_ownerships
  ( project_id BIGINT REFERENCES projects (id) ON DELETE CASCADE
  , user_id BIGINT REFERENCES users(id) ON DELETE CASCADE
  , the_role INTEGER
  , PRIMARY KEY  (project_id, user_id)
  , CONSTRAINT reversed_pk UNIQUE (user_id, project_id)
  );

INSERT INTO projects( the_name)
SELECT 'project-' || gs::text
FROM generate_series(1,1000) gs
        ;

INSERT INTO users( the_name)
SELECT 'name_' || gs::text
FROM generate_series(1,1000) gs
        ;

INSERT INTO project_ownerships (project_id,user_id,the_role)
SELECT p.id, u.id , (random()* 100)::integer
FROM projects p
JOIN users u ON random() < .10
        ;

VACUUM ANALYZE projects,users,project_ownerships;


SET enable_hashjoin = 0;
SET enable_sort = 0;
-- SET enable_seqscan = 0;

EXPLAIN ANALYZE
SELECT p.the_name AS project_name
        , po.the_role AS the_role
FROM projects p
JOIN project_ownerships po ON po.project_id = p.id
        AND EXISTS (
        SELECT *
        FROM users u
        WHERE u.id = po.user_id
        AND u.the_name >= 'name_10'
        AND u.the_name < 'name_20'
        );



EXPLAIN ANALYZE
SELECT u.the_name AS user_name
        , po.the_role AS the_role
FROM users u
JOIN project_ownerships po ON po.user_id = u.id
        AND EXISTS (
        SELECT *
        FROM projects p
        WHERE p.id = po.project_id
        AND p.the_name >= 'project-10'
        AND p.the_name < 'project-20'
        );

Resulting query plans:

SET
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 11.6 on armv7l-unknown-linux-gnueabihf, compiled by gcc (Raspbian 8.3.0-6+rpi1) 8.3.0, 32-bit
(1 row)
SET
SET
                                                                      QUERY PLAN                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.97..4693.68 rows=11924 width=15) (actual time=0.333..153.660 rows=11157 loops=1)
   ->  Nested Loop  (cost=0.69..1204.55 rows=11924 width=12) (actual time=0.268..53.192 rows=11157 loops=1)
         ->  Index Scan using users_the_name_key on users u  (cost=0.28..7.02 rows=119 width=8) (actual time=0.126..0.317 rows=112 loops=1)
               Index Cond: ((the_name >= 'name_10'::text) AND (the_name < 'name_20'::text))
         ->  Index Scan using reversed_pk on project_ownerships po  (cost=0.42..9.06 rows=100 width=20) (actual time=0.015..0.308 rows=100 loops=112)
               Index Cond: (user_id = u.id)
   ->  Index Scan using projects_pkey on projects p  (cost=0.28..0.29 rows=1 width=19) (actual time=0.005..0.005 rows=1 loops=11157)
         Index Cond: (id = po.project_id)
 Planning Time: 6.218 ms
 Execution Time: 162.319 ms
(10 rows)

                                                                            QUERY PLAN                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.97..4057.79 rows=11022 width=12) (actual time=0.084..93.584 rows=11236 loops=1)
   ->  Nested Loop  (cost=0.69..832.59 rows=11022 width=12) (actual time=0.063..25.260 rows=11236 loops=1)
         ->  Index Scan using projects_the_name_key on projects p  (cost=0.28..6.84 rows=110 width=8) (actual time=0.037..0.163 rows=112 loops=1)
               Index Cond: ((the_name >= 'project-10'::text) AND (the_name < 'project-20'::text))
         ->  Index Scan using project_ownerships_pkey on project_ownerships po  (cost=0.42..6.51 rows=100 width=20) (actual time=0.010..0.111 rows=100 loops=112)
               Index Cond: (project_id = p.id)
   ->  Index Scan using users_pkey on users u  (cost=0.28..0.29 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=11236)
         Index Cond: (id = po.user_id)
 Planning Time: 0.971 ms
 Execution Time: 99.671 ms
(10 rows)

Non-unique foreign key Oracle?

Is this acceptable, given that Order ID on Table B is not unique?

Yes, absolutely. This is the standard way of modeling a 1:many relationship

You should nevertheless find a primary key for TableB. If a customer cannot be assigned to more than one order, then using (order_id, customer_id) as the PK would make sense.

Can a Foreign Key Reference a Non-Unique Index