Как найти все foreign keys - Исправление недочетов и поиск решений вместе с Examum.ru

In MySQL, how do I get a list of all foreign key constraints pointing to a particular table? a particular column? This is the same thing as this Oracle question, but for MySQL.

asked Oct 14, 2008 at 15:18

Christian OudardChristian Oudard

47.7k25 gold badges65 silver badges69 bronze badges

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, change REFERENCED_TABLE_NAME with REFERENCED_COLUMN_NAME in the WHERE clause.

user16217248

2,75712 gold badges15 silver badges35 bronze badges

answered Oct 14, 2008 at 15:35

Vinko Vrsalovic♦Vinko Vrsalovic

329k53 gold badges333 silver badges371 bronze badges

EDIT: As pointed out in the comments, this is not the correct answer to the OPs question, but it is useful to know this command. This question showed up in Google for what I was looking for, and figured I’d leave this answer for the others to find.

SHOW CREATE TABLE `<yourtable>`;

I found this answer here:
MySQL : show constraints on tables command

I needed this way because I wanted to see how the FK functioned, rather than just see if it existed or not.

answered Jun 11, 2013 at 16:27

If you use InnoDB and defined FK’s you could query the information_schema database e.g.:

SELECT * FROM information_schema.TABLE_CONSTRAINTS 
WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY' 
AND information_schema.TABLE_CONSTRAINTS.TABLE_SCHEMA = 'myschema'
AND information_schema.TABLE_CONSTRAINTS.TABLE_NAME = 'mytable';

answered Oct 14, 2008 at 15:26

NodeNode

21.5k2 gold badges31 silver badges35 bronze badges

Posting on an old answer to add some useful information.

I had a similar problem, but I also wanted to see the CONSTRAINT_TYPE along with the REFERENCED table and column names. So,

To see all FKs in your table:

USE '<yourschema>';

SELECT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
FROM information_schema.TABLE_CONSTRAINTS i 
LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' 
AND i.TABLE_SCHEMA = DATABASE()
AND i.TABLE_NAME = '<yourtable>';

To see all the tables and FKs in your schema:

USE '<yourschema>';

SELECT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
FROM information_schema.TABLE_CONSTRAINTS i 
LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' 
AND i.TABLE_SCHEMA = DATABASE();

To see all the FKs in your database:

SELECT i.TABLE_SCHEMA, i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
FROM information_schema.TABLE_CONSTRAINTS i 
LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY';

Remember!

This is using the InnoDB storage engine. If you can’t seem to get any foreign keys to show up after adding them it’s probably because your tables are using MyISAM.

To check:

SELECT * TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = '<yourschema>';

To fix, use this:

ALTER TABLE `<yourtable>` ENGINE=InnoDB;

answered Jul 2, 2012 at 23:59

AndyAndy

8659 silver badges15 bronze badges

As an alternative to Node’s answer, if you use InnoDB and defined FK’s you could query the information_schema database e.g.:

SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = '<schema>'
AND TABLE_NAME = '<table>'

for foreign keys from <table>, or

SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = '<schema>'
AND REFERENCED_TABLE_NAME = '<table>'

for foreign keys to <table>

You can also get the UPDATE_RULE and DELETE_RULE if you want them.

answered Dec 12, 2013 at 12:02

ChrisVChrisV

8,6993 gold badges48 silver badges38 bronze badges

Constraints in SQL are the rules defined for the data in a table. Constraints also limit the types of data that go into the table. If new data does not abide by these rules the action is aborted.

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE = 'FOREIGN KEY';

You can view all constraints by using select * from information_schema.table_constraints;

(This will produce a lot of table data).

You can also use this for MySQL:

show create table tableName;

answered Aug 12, 2020 at 12:51

imatworkimatwork

5136 silver badges16 bronze badges

This solution will not only display all relations but also the constraint name, which is required in some cases (e.g. drop contraint):

select
    concat(table_name, '.', column_name) as 'foreign key',
    concat(referenced_table_name, '.', referenced_column_name) as 'references',
    constraint_name as 'constraint name'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null;

If you want to check tables in a specific database, at the end of the query add the schema name:

select
    concat(table_name, '.', column_name) as 'foreign key',
    concat(referenced_table_name, '.', referenced_column_name) as 'references',
    constraint_name as 'constraint name'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null
    and table_schema = 'database_name';

Likewise, for a specific column name, add

and table_name = ‘table_name

at the end of the query.

Inspired by this post here

KOGI

3,9492 gold badges24 silver badges35 bronze badges

answered Sep 16, 2013 at 10:36

PanayotisPanayotis

1,78223 silver badges32 bronze badges

Using REFERENCED_TABLE_NAME does not always work and can be a NULL value. The following query can work instead:

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = '<table>';

answered Feb 5, 2015 at 23:51

HazokHazok

5,3154 gold badges37 silver badges47 bronze badges

I’m reluctant to add yet another answer, but I’ve had to beg, borrow and steal from the others to get what I want, which is a complete list of all the FK relationships on tables in a given schema, INCLUDING FKs to tables in other schemas. The two crucial recordsets are information_schema.KEY_COLUMN_USAGE and information_schema.referential_constraints. If an attribute you want is missing, just uncomment the KCU., RC. to see what’s available

SELECT DISTINCT KCU.TABLE_NAME, KCU.COLUMN_NAME, REFERENCED_TABLE_SCHEMA, KCU.REFERENCED_TABLE_NAME, KCU.REFERENCED_COLUMN_NAME, UPDATE_RULE, DELETE_RULE #, KCU.*, RC.*
FROM information_schema.KEY_COLUMN_USAGE KCU
INNER JOIN information_schema.referential_constraints RC ON KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE TABLE_SCHEMA = (your schema name)
AND KCU.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY KCU.TABLE_NAME, KCU.COLUMN_NAME;

answered Jul 23, 2020 at 8:30

DJDaveDJDave

8671 gold badge13 silver badges28 bronze badges

A quick way to list your FKs (Foreign Key references) using the

KEY_COLUMN_USAGE view:

SELECT CONCAT( table_name, '.',
column_name, ' -> ',
referenced_table_name, '.',
referenced_column_name ) AS list_of_fks
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = (your schema name here)
AND REFERENCED_TABLE_NAME is not null
ORDER BY TABLE_NAME, COLUMN_NAME;

This query does assume that the constraints and all referenced and referencing tables are in the same schema.

Add your own comment.

Source: the official mysql manual.

answered Jun 13, 2013 at 2:37

It’s often helpful to know the update and delete behaviour, which the other answers don’t provide. So here goes.

SELECT cu.table_name,
       cu.column_name,
       cu.constraint_name,
       cu.referenced_table_name,
       cu.referenced_column_name,
       IF(rc.update_rule = 'NO ACTION', 'RESTRICT', rc.update_rule) AS update_rule,-- See: https://stackoverflow.com/a/1498015/2742117
       IF(rc.delete_rule = 'NO ACTION', 'RESTRICT', rc.delete_rule) AS delete_rule -- See: https://stackoverflow.com/a/1498015/2742117
FROM information_schema.key_column_usage cu
INNER JOIN information_schema.referential_constraints rc ON rc.constraint_schema = cu.table_schema
AND rc.table_name = cu.table_name
AND rc.constraint_name = cu.constraint_name
WHERE cu.referenced_table_schema = '<your schema>'
  AND cu.referenced_table_name = '<your table>';

answered Apr 15, 2022 at 10:17

The solution I came up with is fragile; it relies on django’s naming convention for foreign keys.

USE information_schema;
tee mysql_output
SELECT * FROM TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_SCHEMA = 'database_name';
notee

Then, in the shell,

grep 'refs_tablename_id' mysql_output

answered Oct 14, 2008 at 15:35

Christian OudardChristian Oudard

47.7k25 gold badges65 silver badges69 bronze badges

If you also want to get the name of the foreign key column:

SELECT i.TABLE_SCHEMA, i.TABLE_NAME, 
       i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, 
       k.COLUMN_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
  FROM information_schema.TABLE_CONSTRAINTS i 
  LEFT JOIN information_schema.KEY_COLUMN_USAGE k 
       ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
 WHERE i.TABLE_SCHEMA = '<TABLE_NAME>' AND i.CONSTRAINT_TYPE = 'FOREIGN KEY' 
 ORDER BY i.TABLE_NAME;

Hannele

9,2026 gold badges47 silver badges66 bronze badges

answered Aug 9, 2017 at 18:40

omarjebariomarjebari

4,7333 gold badges34 silver badges32 bronze badges

I had a «myprodb» MySql database and for checking all foreign keys in this data base I used the following simple command.

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_SCHEMA = 'myprodb' AND CONSTRAINT_TYPE = 'FOREIGN KEY';

I hope it help.

answered Jun 20, 2022 at 4:54

M Shafaei NM Shafaei N

3892 silver badges6 bronze badges

To find all tables containing a particular foreign key such as employee_id

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('employee_id')
AND TABLE_SCHEMA='table_name';

answered Dec 15, 2014 at 3:38

Anthony VipondAnthony Vipond

1,8792 gold badges19 silver badges21 bronze badges

I needed a bird’s-eye-view on the relationships among the tables (to use in an ORM). Using the suggestions from this page, and after experimenting, I’ve put together the following query:

SELECT
    KCU.CONSTRAINT_NAME,
    KCU.TABLE_NAME,
    KCU.COLUMN_NAME,
    KCU.REFERENCED_TABLE_NAME,
    KCU.REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
    JOIN INFORMATION_SCHEMA.COLUMNS AS COLS
        ON
                COLS.TABLE_SCHEMA = KCU.TABLE_SCHEMA
            AND COLS.TABLE_NAME   = KCU.TABLE_NAME
            AND COLS.COLUMN_NAME  = KCU.COLUMN_NAME
WHERE
        KCU.CONSTRAINT_SCHEMA = {YOUR_SCHEMA_NAME}
    AND KCU.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY
    KCU.TABLE_NAME,
    COLS.ORDINAL_POSITION

It returns just what I need, and in the order that I want.

I also do little processing on the result (turn it into a some kind of dictionary), so that it’s ready to be used for creating an aggregate.

answered May 31, 2021 at 20:26

akinuriakinuri

10.5k10 gold badges63 silver badges101 bronze badges

Источник

In this tutorial, we will show you how to display foreign keys in MySQL using the SHOW CREATE TABLE and INFORMATION_SCHEMA table commands. We’ll also provide some examples of how to use these commands in practice, along with some statistics on the importance of foreign keys in database design.

A foreign key is a column or set of columns in a table that refers to the primary key of another table. It is used to maintain referential integrity, which ensures that data is consistent and accurate across related tables in a database.

One of the most common ways to show foreign keys in MySQL is by using the SHOW CREATE TABLE command. This command displays the CREATE TABLE statement for a specific table, including any foreign key constraints. Here’s an example of how to use this command:

SHOW CREATE TABLE orders;

Another way to show foreign keys in MySQL is by querying the INFORMATION_SCHEMA table. The INFORMATION_SCHEMA table contains information about all the tables and columns in a database, including foreign key constraints. Here’s an example of how to use this method:

SELECT
    TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
    REFERENCED_TABLE_SCHEMA = 'your_database_name'
    AND REFERENCED_TABLE_NAME is not null;

It is worth mentioning that foreign keys are crucial for maintaining data integrity, and they help prevent data anomalies such as orphaned records and duplicate data. It is a best practice to use foreign keys whenever you are creating a relational database.

Conclusion: In this tutorial, we have shown you two ways to display foreign keys in MySQL: the SHOW CREATE TABLE command and the INFORMATION_SCHEMA table. We’ve also discussed the importance of foreign keys in maintaining data integrity and provided code examples to help you implement these commands in your own projects.

Note: Replace your_database_name with the actual database name in the above code snippet.

Additionally, you can use the SHOW INDEXES command to see all the indexes on a table, including foreign keys. Here’s an example of how to use this command:

SHOW INDEXES FROM orders;

This command will return information about all indexes on the «orders» table, including the name of the index, the column it is on, and whether it is a primary key or foreign key.

It’s also worth noting that MySQL 8.0 has introduced a new data dictionary system, which allows you to view foreign key constraints using the mysql command-line client. Here’s an example of how to use this command:

mysql> SELECT * FROM mysql.innodb_foreign;

This command will return the list of foreign keys on all InnoDB tables, including the name of the table, the name of the foreign key, and the columns that are part of the foreign key.

In conclusion, you have several options for displaying foreign keys in MySQL, including the SHOW CREATE TABLE command, the INFORMATION_SCHEMA table, the SHOW INDEXES command and the mysql.innodb_foreign table. Understanding and utilizing foreign keys is a crucial step in maintaining a well-designed and efficient database.

It’s also important to note that when creating a new table with foreign keys, it’s best practice to first create the table that the foreign key references, and then create the table with the foreign key. This ensures that the referenced table and its primary key exist before the foreign key is created.

When working with foreign keys, it’s also important to consider performance and indexing. Having a foreign key on a column also creates an index on that column, which can improve query performance. However, if the column is frequently updated, the index can negatively impact performance. In such cases, it’s best to consider other options such as partitioning or denormalization.

Another important aspect to consider is cascading actions. Cascading actions are defined actions that are performed automatically when a row is deleted or updated in the parent table. For example, when a row is deleted from the parent table, you can configure the database to automatically delete all the rows in the child table that reference the deleted row in the parent table.

In addition, it’s also important to keep in mind that foreign keys can only reference columns of the same data type, and both columns must have the same character set and collation.

In summary, foreign keys are a powerful tool for maintaining data integrity in relational databases, but it’s important to consider performance, indexing, and cascading actions when working with them. With the right approach and understanding of the different options available, you can effectively use foreign keys to design efficient and reliable databases.

Mastering Foreign Keys in MySQL: How to Display and Utilize Them for Data Integrity

Foreign keys are an essential tool for maintaining data integrity in relational databases. They establish a link between two tables, ensuring that data is consistent and accurate across related tables in a database. In this tutorial, we will provide a step-by-step guide on how to display foreign keys in MySQL using the SHOW CREATE TABLE and INFORMATION_SCHEMA table commands. We’ll also discuss the importance of foreign keys in database design and best practices for using them. Additionally, we will provide an overview of the new data dictionary system in MySQL 8.0 for viewing foreign key constraints.

SHOW CREATE TABLE orders;

SELECT
    TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
    REFERENCED_TABLE_SCHEMA = 'your_database_name'
    AND REFERENCED_TABLE_NAME is not null;

MySQL 8.0 has introduced a new data dictionary system, which allows you to view foreign key constraints using the mysql command-line client. Here’s an example of how to use this command:

mysql> SELECT * FROM mysql.innodb_foreign;

This command will return the list of foreign keys on all InnoDB tables, including the name of the table, the name of the foreign key, and the columns that are part of the foreign key.

Conclusion: In this tutorial, we have provided a step-by-step guide on how to display foreign keys in MySQL using the SHOW CREATE TABLE and INFORMATION_SCHEMA table commands. We’ve also discussed the importance of foreign keys in maintaining data integrity, provided best practices for using them and an overview of the new data dictionary system in MySQL 8.0 for viewing foreign key constraints. By understanding and utilizing foreign keys effectively, you can design efficient and reliable databases. Remember to replace your_database_name with the actual database name in the above code snippet.

Additionally, it’s important to keep in mind that when creating a new table with foreign keys, it’s best practice to first create the table that the foreign key references, and then create the table with the foreign key. This ensures that the referenced table and its primary key exist before the foreign key is created.

It’s also important to keep in mind that foreign keys can only reference columns of the same data type, and both columns must have the same character set and collation.

In conclusion, mastering foreign keys in MySQL is an important step in designing efficient and reliable databases. By understanding how to display foreign keys using the SHOW CREATE TABLE and INFORMATION_SCHEMA table commands, and by following best practices for working with foreign keys, you can ensure data integrity and optimize performance in your databases. Additionally, the new data dictionary system in MySQL 8.0 allows you to view foreign key constraints easily.

Optimizing Foreign Key Performance in MySQL: A Comprehensive Guide

Foreign keys are an essential tool for maintaining data integrity in relational databases. They establish a link between two tables, ensuring that data is consistent and accurate across related tables in a database. However, it’s important to understand that the use of foreign keys can also have an impact on performance. In this tutorial, we will provide an overview of best practices for creating and using foreign keys in MySQL, and discuss the effects of indexing on foreign key performance and strategies for optimizing performance. Additionally, we will explain cascading actions and how they can be used to automatically update or delete related data, and provide tips for troubleshooting and resolving foreign key performance issues.

When creating foreign keys, it’s best practice to first create the table that the foreign key references, and then create the table with the foreign key. This ensures that the referenced table and its primary key exist before the foreign key is created.

Foreign keys also create an index on the column, which can improve query performance. However, if the column is frequently updated, the index can negatively impact performance. In such cases, it’s best to consider other options such as partitioning or denormalization.

Cascading actions are defined actions that are performed automatically when a row is deleted or updated in the parent table. For example, when a row is deleted from the parent table, you can configure the database to automatically delete all the rows in the child table that reference the deleted row in the parent table.

It’s also important to keep in mind that foreign keys can only reference columns of the same data type, and both columns must have the same character set and collation.

If you encounter performance issues related to foreign keys, it’s important to first identify the source of the problem. You can use the EXPLAIN command to see the query execution plan and identify any bottlenecks, or check the slow query log to see which queries are taking the longest to execute. You can also use the SHOW CREATE TABLE command to see the structure of your tables, including any foreign key constraints.

In this tutorial, we have provided an overview of best practices for creating and using foreign keys in MySQL, and discussed the effects of indexing on foreign key performance and strategies for optimizing performance. Additionally, we have explained cascading actions and how they can be used to automatically update or delete related data, and provided tips for troubleshooting and resolving foreign key performance issues. By following these best practices and understanding the impact of foreign keys on performance, you can ensure data integrity while optimizing the performance of your databases.

the table with the foreign key. This ensures that the referenced table and its primary key exist before the foreign key is created.

It’s also important to keep in mind that foreign keys can only reference columns of the same data type, and both columns must have the same character set and collation.

In conclusion, showing foreign keys in MySQL is a simple process, but it’s important to keep in mind best practices for working with foreign keys and troubleshoot common issues. By understanding how to display foreign keys using the SHOW CREATE TABLE and INFORMATION_SCHEMA table commands, and by following best practices for working with foreign keys, you can ensure data integrity in your databases. Remember to replace your_database_name with the actual database name in the above code snippet. For further learning about foreign keys in MySQL, you can refer to official MySQL documentation and tutorials.

Questions and Answers

Q: What is a foreign key in MySQL?

A: A foreign key in MySQL is a column or set of columns in a table that is used to establish a link between the data in two tables. The foreign key in one table references the primary key in another table. This creates a relationship between the two tables, allowing the database management system to enforce referential integrity.

Q: How do I show the foreign keys for a specific table in MySQL?

A: To show the foreign keys for a specific table in MySQL, you can use the SHOW CREATE TABLE statement. For example, to show the foreign keys for a table named «orders,» you would use the following query:

SHOW CREATE TABLE orders;

This will return the CREATE TABLE statement for the «orders» table, which will include the foreign key constraints defined for the table.

Q: Can I show all foreign keys in a MySQL database?

A: To show all foreign keys in a MySQL database, you can use the SHOW CREATE DATABASE statement and pipe the output to grep command. For example, to show all foreign keys in a database named «mydb», you would use the following command:

mysql -u username -p -e "SHOW CREATE DATABASE mydb" | grep -i "CONSTRAINT"

This will show all the foreign keys defined in the mydb database.

Q: How can I check if a specific column is a foreign key in MySQL?

A: To check if a specific column is a foreign key in a table in MySQL, you can use the SHOW CREATE TABLE statement and check the output for the column name in the foreign key constraint. For example, to check if a column named «customer_id» is a foreign key in a table named «orders,» you would use the following query:

SHOW CREATE TABLE orders;

This will return the CREATE TABLE statement for the «orders» table, which you can then check for the presence of «customer_id» in a foreign key constraint.

Q: Can I rename a foreign key constraint in MySQL?

A: Yes, it is possible to rename a foreign key constraint in MySQL. You can use the ALTER TABLE statement to modify the constraint, using the RENAME CONSTRAINT clause. For example, to rename a foreign key constraint named «fk_orders_customer» to «fk_orders_customer_id» in a table named «orders,» you would use the following query:

ALTER TABLE orders RENAME CONSTRAINT fk_orders_customer TO fk_orders_customer_id;

Q: How can I drop a foreign key constraint in MySQL?

A: To drop a foreign key constraint in MySQL, you can use the ALTER TABLE statement with the DROP FOREIGN KEY clause. For example, to drop a foreign key constraint named «fk_orders_customer» in a table named «orders,» you would use the following query:

ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;

Q: How can I disable foreign key checks in MySQL?

A: To disable foreign key checks in MySQL, you can use the SET FOREIGN_KEY_CHECKS command. To disable foreign key checks, you would set the value to 0 like this:

SET FOREIGN_KEY_CHECKS=0;

You can re-enable foreign key checks by setting the value to 1 like this:

SET FOREIGN_KEY_CHECKS=1;

It is important to note that disabling foreign key checks may cause data integrity issues, and should only be used in specific cases where it is necessary, such as for bulk data imports.

https://stackoverflow.com/questions/201621/how-do-i-see-all-foreign-keys-to-a-table-or-column

Источник

There is how to get count of all responsibilities for selected Id. Just change @dbTableName value, @dbRowId value and its type (if int you need to remove » in line no 82 (..SET @SQL = ..)). Enjoy.

DECLARE @dbTableName varchar(max) = 'User'
DECLARE @dbRowId uniqueidentifier = '21d34ecd-c1fd-11e2-8545-002219a42e1c'

DECLARE @FK_ROWCOUNT int
DECLARE @SQL nvarchar(max)

DECLARE @PKTABLE_QUALIFIER sysname
DECLARE @PKTABLE_OWNER sysname
DECLARE @PKTABLE_NAME sysname
DECLARE @PKCOLUMN_NAME sysname
DECLARE @FKTABLE_QUALIFIER sysname
DECLARE @FKTABLE_OWNER sysname
DECLARE @FKTABLE_NAME sysname
DECLARE @FKCOLUMN_NAME sysname
DECLARE @UPDATE_RULE smallint
DECLARE @DELETE_RULE smallint
DECLARE @FK_NAME sysname
DECLARE @PK_NAME sysname
DECLARE @DEFERRABILITY sysname

IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL
    DROP TABLE #Temp1;
CREATE TABLE #Temp1 ( 
    PKTABLE_QUALIFIER sysname,
    PKTABLE_OWNER sysname,
    PKTABLE_NAME sysname,
    PKCOLUMN_NAME sysname,
    FKTABLE_QUALIFIER sysname,
    FKTABLE_OWNER sysname,
    FKTABLE_NAME sysname,
    FKCOLUMN_NAME sysname,
    UPDATE_RULE smallint,
    DELETE_RULE smallint,
    FK_NAME sysname,
    PK_NAME sysname,
    DEFERRABILITY sysname,
    FK_ROWCOUNT int
    );
DECLARE FK_Counter_Cursor CURSOR FOR
    SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
       PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)),
       PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME),
       PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME),
       FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
       FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)),
       FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME),
       FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME),
       -- Force the column to be non-nullable (see SQL BU 325751)
       --KEY_SEQ             = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),
       UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsUpdateCascade') 
                                        WHEN 1 THEN 0
                                        ELSE 1
                                      END),
       DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade') 
                                        WHEN 1 THEN 0
                                        ELSE 1
                                      END),
       FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),
       PK_NAME = CONVERT(SYSNAME,I.NAME),
       DEFERRABILITY = CONVERT(SMALLINT,7)   -- SQL_NOT_DEFERRABLE
    FROM   SYS.ALL_OBJECTS O1,
           SYS.ALL_OBJECTS O2,
           SYS.ALL_COLUMNS C1,
           SYS.ALL_COLUMNS C2,
           SYS.FOREIGN_KEYS F
           INNER JOIN SYS.FOREIGN_KEY_COLUMNS K
             ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID)
           INNER JOIN SYS.INDEXES I
             ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID
                 AND F.KEY_INDEX_ID = I.INDEX_ID)
    WHERE  O1.OBJECT_ID = F.REFERENCED_OBJECT_ID
           AND O2.OBJECT_ID = F.PARENT_OBJECT_ID
           AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID
           AND C2.OBJECT_ID = F.PARENT_OBJECT_ID
           AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID
           AND C2.COLUMN_ID = K.PARENT_COLUMN_ID
           AND O1.NAME = @dbTableName
OPEN FK_Counter_Cursor;
FETCH NEXT FROM FK_Counter_Cursor INTO @PKTABLE_QUALIFIER, @PKTABLE_OWNER, @PKTABLE_NAME, @PKCOLUMN_NAME, @FKTABLE_QUALIFIER, @FKTABLE_OWNER, @FKTABLE_NAME, @FKCOLUMN_NAME, @UPDATE_RULE, @DELETE_RULE, @FK_NAME, @PK_NAME, @DEFERRABILITY;
WHILE @@FETCH_STATUS = 0
   BEGIN
        SET @SQL = 'SELECT @dbCountOut = COUNT(*) FROM [' + @FKTABLE_NAME + '] WHERE [' + @FKCOLUMN_NAME + '] = ''' + CAST(@dbRowId AS varchar(max)) + '''';
        EXECUTE sp_executesql @SQL, N'@dbCountOut int OUTPUT', @dbCountOut = @FK_ROWCOUNT OUTPUT;
        INSERT INTO #Temp1 (PKTABLE_QUALIFIER, PKTABLE_OWNER, PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_QUALIFIER, FKTABLE_OWNER, FKTABLE_NAME, FKCOLUMN_NAME, UPDATE_RULE, DELETE_RULE, FK_NAME, PK_NAME, DEFERRABILITY, FK_ROWCOUNT) VALUES (@FKTABLE_QUALIFIER, @PKTABLE_OWNER, @PKTABLE_NAME, @PKCOLUMN_NAME, @FKTABLE_QUALIFIER, @FKTABLE_OWNER, @FKTABLE_NAME, @FKCOLUMN_NAME, @UPDATE_RULE, @DELETE_RULE, @FK_NAME, @PK_NAME, @DEFERRABILITY, @FK_ROWCOUNT)
      FETCH NEXT FROM FK_Counter_Cursor INTO @PKTABLE_QUALIFIER, @PKTABLE_OWNER, @PKTABLE_NAME, @PKCOLUMN_NAME, @FKTABLE_QUALIFIER, @FKTABLE_OWNER, @FKTABLE_NAME, @FKCOLUMN_NAME, @UPDATE_RULE, @DELETE_RULE, @FK_NAME, @PK_NAME, @DEFERRABILITY;
   END;
CLOSE FK_Counter_Cursor;
DEALLOCATE FK_Counter_Cursor;
GO
SELECT * FROM #Temp1
GO

Источник

You have a table and you want to see all the other tables which have the foreign key constraints pointing to that table, or to a particular column in that table.

To see foreign key relationships of 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 = 'db_name'
    AND REFERENCED_TABLE_NAME = 'table_name';

To see foreign key relationships of 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 = 'db_name'
    AND REFERENCED_TABLE_NAME = 'table_name'
    AND REFERENCED_COLUMN_NAME = 'column_name';

You might not need to address the REFERENCED_TABLE_SCHEMA for the current database you’re opening.

Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more. 

Download TablePlus here, it’s free anw!.

Источник

The query below returns the foreign key constraints defined in the user databases (schemas).

Query

select concat(fks.constraint_schema, '.', fks.table_name) as foreign_table,
       '->' as rel,
       concat(fks.unique_constraint_schema, '.', fks.referenced_table_name)
              as primary_table,
       fks.constraint_name,
       group_concat(kcu.column_name
            order by position_in_unique_constraint separator ', ') 
             as fk_columns
from information_schema.referential_constraints fks
join information_schema.key_column_usage kcu
     on fks.constraint_schema = kcu.table_schema
     and fks.table_name = kcu.table_name
     and fks.constraint_name = kcu.constraint_name
-- where fks.constraint_schema = 'database name'
group by fks.constraint_schema,
         fks.table_name,
         fks.unique_constraint_schema,
         fks.referenced_table_name,
         fks.constraint_name
order by fks.constraint_schema,
         fks.table_name;

Note: if you need the information for a specific database (schema), then uncomment the where clause and provide your database name.

Columns

foreign_table — foreign table name with its database (schema) name
rel — relationship symbol implicating direction
primary_table — primary (referenced) table name with its database (schema) name
fk_constraint_name — foreign key constraint name
fk_columns — foreign key columns separated by ‘,’

Rows

One row: represents one foreign key. If the foreign key consists of multiple columns (composite key), it is still represented as one row
Scope of rows: all foreign keys in the database (schema)
Ordered by: foreign database name and table name

Sample results

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free

Comments are only visible when the visitor has consented to statistics cookies.
To see and add comments please accept statistics cookies.

Источник