oracle query foreign key

Oracle Query Foreign Key

In database design, foreign key is one of the important constraints connecting two tables. A foreign key is a column or columns in a table whose value corresponds to the value of a primary key or unique key column in another table. Foreign keys ensure data integrity and consistency. When a foreign key is used when a row is inserted, updated, or deleted in the master table, it automatically performs the corresponding operation in the slave table.

Querying foreign keys is one of the important aspects of database management. In Oracle Database, how you query foreign keys depends on the information you wish to obtain. Here are some queries from the list that may be useful.

Query all foreign keys of a specific table

In Oracle, you can use the following query to find all foreign keys of a specific table:

SELECT constraint_name, table_name , column_name, owner
FROM all_cons_columns
WHERE owner = 'YourTableName' AND constraint_name LIKE 'FK_%' ORDER BY constraint_name;

This query will return the constraint names of all foreign keys of the given table, Table name, column name and owner.

Query for a specific foreign key in a specific table

If you only want to find a specific foreign key in a given table, you can use the following query:

SELECT constraint_name , table_name, column_name, owner
FROM all_cons_columns
WHERE constraint_name = 'YourForeignKeyName' AND owner = 'YourTableName';

This will return the constraint name, table name, Column name and owner.

Query the tables referenced by all foreign keys

To find the tables referenced by foreign keys, use the following query:

SELECT DISTINCT f.constraint_name, r.table_name, f. table_name
FROM all_constraints r, all_constraints f
WHERE r.constraint_type = 'P'
AND f.r_constraint_name = r.constraint_name
and f.constraint_type = 'R';

this The query will return the constraint names of all foreign keys, reference tables and related table names.

Querying the referencing (primary) tables and columns of a foreign key

To find the tables and columns referencing a foreign key, use the following query:

SELECT c.table_name, c .column_name, cc.table_name ref_table, cc.column_name ref_column, c.constraint_name
FROM all_constraints c, all_constraints cc
WHERE cc.owner = c.r_owner AND cc.constraint_name = c.r_constraint_name
AND c. constraint_type = 'R';

This will return a list of the foreign key's table name, column name, associated table name, associated column name, and constraint name.

Query the details of the foreign key

You can use the following query to get the details about the foreign key:

SELECT constraint_name, table_name, status, delete_rule, deferrable, validated
FROM all_constraints c
WHERE constraint_type = 'R' AND owner = 'YourTableName' AND constraint_name = 'YourForeignKeyName';

This query will return detailed information about the foreign key, such as constraint name, table name , status, deletion rules, deferrability, and verification status.

In short, querying foreign keys is an important aspect of database management to ensure the integrity and consistency of data between tables. In Oracle, you can use a variety of queries to find information about foreign keys. The queries listed above are among a few you can use that will return details of a foreign key and information about the table and column that the foreign key refers to.

The above is the detailed content of oracle query foreign key. For more information, please follow other related articles on the PHP Chinese website!