How to Find What Foreign Key References an Index on Table

How to find what foreign key references an index on table

Something like

Select
    f.name,
    object_name(f.parent_object_id)
From
    sys.foreign_keys f
        inner join
    sys.indexes i
        on f.referenced_object_id = i.object_id and
           f.key_index_id = i.index_id
Where
    i.name = 'idx_duplicate' and
    i.object_id = object_id('[dbo].[MyTable]')

How can I list all foreign keys referencing a given table in SQL Server?

Not sure why no one suggested but I use sp_fkeys to query foreign keys for a given table:

EXEC sp_fkeys 'TableName'

You can also specify the schema:

EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo'

Without specifying the schema, the docs state the following:

If pktable_owner is not specified, the default table visibility rules
of the underlying DBMS apply.

In SQL Server, if the current user owns a table with the specified
name, that table's columns are returned. If pktable_owner is not
specified and the current user does not own a table with the specified
pktable_name, the procedure looks for a table with the specified
pktable_name owned by the database owner. If one exists, that table's
columns are returned.

Index to find records where the foreign key does not exist

The best I can think of is your last idea in the comments: a materialized view.

CREATE MATERIALIZED VIEW orphaned_products AS
SELECT *
FROM   products p
WHERE  NOT EXISTS (SELECT 1 FROM transactions t WHERE t.product_id = p.id)

Then you can use this table (a materialized view is just a table) as drop-in replacement for the big table products in queries working with orphaned products - with obviously great impact on performance (a few 100 rows instead of 100 millions). Materialized views require Postgres 9.3, but that's what you are using according to the comments. And you can implement it by hand easily in earlier versions.

However, a materialized view is a snapshot and not updated dynamically. (This might void any performance benefit anyway.) To update, you run the (expensive) operation:

REFRESH MATERIALIZED VIEW orphaned_products;

You could do that at strategically opportune points in time and have multiple subsequent queries benefit from it, depending on your business model.

Of course, you would have an index on orphaned_products.id, but that would not be very important for a small table of a few hundred rows.

If your model is such that transactions are never deleted, you could exploit that to great effect. Create a similar table by hand:

CREATE TABLE orphaned_products2 AS
SELECT *
FROM   products p
WHERE  NOT EXISTS (SELECT 1 FROM transactions t WHERE t.product_id = p.id);

Of course you can refresh that "materialized view" just like the first one by truncating and refilling it. But the point is to avoid the expensive operation. All you actually need is:

• Add new products to orphaned_products2.
  
  Implement with a trigger AFTER INSERT ON products.

• Remove products from orphaned_products2 as soon as a referencing row appears in table transactions.
  
  Implement with a trigger AFTER UPDATE OF product_id ON transations. Only if your model allows transations.products_id to be updated - which would be an unconventional thing.
  
  And another one AFTER INSERT ON transations.

All comparatively cheap operations.

• If transactions can be deleted, too, you'd need another trigger to add orphaned products AFTER DELETE ON transations - which would a bit be more expensive. For every deleted transaction you need to check whether that was the last referencing the related product, and add an orphan in this case. May still be a lot cheaper than to refresh the whole materialized view.

VACUUM

After your additional information I would also suggest custom settings for aggressive vacuuming of orphaned_products2, since it is going to produce a lot of dead rows.

How do I see all foreign keys to a table or column?

For a Table:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>';

For a Column:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>' AND
  REFERENCED_COLUMN_NAME = '<column>';

Basically, we changed REFERENCED_TABLE_NAME with REFERENCED_COLUMN_NAME in the where clause.

If I have a table of secondary indexes and a foreign key, are indexes created per unique foreign key?

You typically write an SQL query referencing columns and values you want to search for. You don't refer specifically to indexes or foreign key constraints in a query.

SELECT * FROM Books WHERE author = 'Bill Karwin';

(No index or foreign key is referenced in this example.)

Indexes are chosen automatically by MySQL's query optimizer. There are ways to override that index choice, but normally you don't need to do that.

A foreign key is a constraint, not a thing you can query. You never refer to a foreign key by name in an SQL query, except when you create or drop that constraint.

Foreign keys automatically constrain the values you can insert, update, or delete in the table. To do this efficiently, there must be an index on the foreign key column. When you create the foreign key constraint, if there is no index it can use, it creates an index automatically. But if there was already an equivalent index, the constraint just uses that one.

---

Re your comments:

I can answer for MySQL. Other implementations may handle indexes differently. You tagged your question with at least two different implementations.

In the example you show:

SELECT * FROM Books 
WHERE author = 'AuthorWhoWroteAMillionBooks' 
  AND title='Book8888'; 

If title is indexed, first MySQL will narrow down the search by using the index. Suppose this results in 50 books that may or may not be authored by the author you are searching for. This is referred to as the "examined rows".

Then it will search this subset of rows (the examined rows) row-by-row, evaluating the other search term against these rows. This is more expensive than using an index, but at least it's done after any index has been used to reduce the set of examined rows.

MySQL knows how to do this regardless of the order of terms in your WHERE clause.

There is no such thing as a second layer of indexes.

You could further optimize this query by using a compound index on the pair of columns in your search.

CREATE INDEX author_and_title ON Books (author, title);

This will create a single index tree for the pair of values. Then the single index search will reduce the set of examined rows to just those that match both terms of your search conditions.

Indexing on foreign keys

Your assumption that foreign keys are not automatically indexed is incorrect, see Using FOREIGN KEY Constraints:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

However, your table is missing a unique key on (childId, groupId), otherwise, you would be allowed to add the same combination several times. Thus it would be a good idea to make those two columns the (composite) primary key (which is common for association tables):

CREATE TABLE childgroups 
(
    childId INT,
    groupId INT,
    primary key (childId, groupId)
);

In addition to the implicit index generated by the foreign key on groupId (the foreign key on childId can use the primary key), every index requirement you can think of is met, and you do not need to add any other index on that table.

Cannot find an index in the referenced table where the referenced columns appear as the first columns

This foreign key...

FOREIGN KEY (serial_no) 
    REFERENCES tag_master(orig_serial_no) 
    ON UPDATE CASCADE ON DELETE RESTRICT

... requires an index on tag_master(orig_serial_no), or at least a compound index where orig_serial_no appears first. This is not the case in your current set up, where the primary key of tag_master is (part_no, serial_no) (the concerned column appears in second position, not first).

One way around this would be to change the order of the columns in the primary key of tag_master:

PRIMARY KEY (orig_serial_no, orig_part_no)

This requires you to also change the order of columns in the compound foreign key in tag_log that references both columns:

FOREIGN KEY(serial_no, part_no) 
    REFERENCES tag_master(orig_serial_no, orig_part_no) 
    ON UPDATE CASCADE ON DELETE RESTRICT

Then you can create the additional foreign key, as demonstrated in this db fiddle.

But, that being said, I just do not see the point for creating this additional foreign key, since you already have another key that covers it (FOREIGN KEY(serial_no, part_no)). Most likely, you just do not need that additional constraint, since the functionalities it offers are aleardy provided by the compound key.

Cannot add foreign key constraint - Cannot find an index in the referenced table

I tested this on 8.0.17 and got the error:

ERROR 3780 (HY000): Referencing column 'UserId' and referenced column 'useridentifier' in foreign key constraint 'FK_UserRoles_oauthtokens_UserId' are incompatible.

UserRoles.UserId is using the table's default character set of utf8mb4, but you're trying to make it reference a column oauthtokens.useridentifier which uses the character set utf8.

The foreign key column and the column it references must use the same character set and same collation.

I got it to work this way:

ALTER TABLE oauthtokens CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Then add the foreign key and it works.

---

Related Topics