Querying records in Oracle for all foreign key references to a table

Actually find this article my main use of SQL is this sentence select * from User_cons_columns t where t.constraint_name like ' FK_CTS_CONT_REINS__CRR ';

Cause:

System testing when it is found that if a record has been quoted, this time to delete this record will cause inconsistent data, the system will error. For example, police information, in the Attendance record table will cite the police ID, if the attendance record has already existed in the police ID, then remove the police officer from the table, it will cause inconsistent data, the front desk error. So, at this point, I want to find all the tables in the database that reference this ID.

One: Introduction to constraint Types

Constraints are used to ensure that database data meets specific business logic or enterprise rules, if constraints are defined, and data does not match

, the DML operation (INSERT, UPDATE, DELETE) will not execute successfully. Constraints include the five types of not NULL, UNIQUE, PRIMARY key, Foreing key, and check

NOT NULL constraint:

The NOT NULL constraint enforces that the column does not accept null values.

Unique constraint:

Unique constraints uniquely identify each record in a database table.

PRIMARY KEY constraint:

The PRIMARY KEY constraint uniquely identifies each record in a database table. The primary key must contain a unique value.

Each table should have a primary key, and each table can have only one primary key. Primary key columns cannot contain NULL values.

Foreing key constraint:

The data for the foreign key column must exist in the primary key column (or the unique column) of the primary table, or null.

Check constraint:

A CHECK constraint is used to limit the range of values in a column.

If you define a CHECK constraint on a single column, the column only allows a specific value.

If a CHECK constraint is defined on a table, the constraint restricts the value in a specific column.

II: View constraint information in Oracle data dictionary

1. The objects we create can be viewed from the "user_" open view. If 10 tables are created in the schema, the query from User_tables will return 10 rows, each of which is about the attribute information for a table

User_tables cannot view tables created in non-current user mode.

All_tables View displays not only the tables created in the current user mode, but also the table information that is authorized to access

The view that we care about is named after the following column prefixes:

· User_ objects created in the current user mode

· All_ objects created in current user mode plus objects created by other users that the current user can access. The all view often contains an "owner" column that reflects the owner of the object that can be accessed. You cannot see the owner column in the User_tables table because you are the owner of all the tables in this view; there is an owner field in All_tables.

· Dba_ It provides information for the entire database. Includes the name and owner of all tables in the database-including the basic tables in the SYS mode

These prefixes are a help in limiting what we want to see, what needs to be seen, and what should be allowed to see. View the table created in the current mode, query user_tables;

View all the tables we have created and the tables that are authorized to be accessed from other user modes, querying all_tables;

A user with a DBA or Select_catalog_role role can query dba_tables to query the list of all tables in the database;

Not all views have a user, all, and DBA prefix, and some views exist only in the DBA view scope. For example: Dba_data_files.

The DBA view is sometimes accessed in the development environment, allowing developers to explore Oracle data dictionaries without any harm. You can get this permission without having a DBA role. In general, by giving the Connect and resource roles to the application developer, who cannot access these, you can assign the Select any table permission or the Select_catalog role to a user so that he will be allowed access to the entire data dictionary.

The more a person understands a data dictionary, the more it touches the complex operations in the database, the more sensitive it is to SQL statement optimization.

2. Data dictionary View: overview

There are some implicit relationships in the data dictionary view. It is often found that these relationships are the result of data dictionary validation using SQL queries, and fortunately there are many features that use names in the data dictionary.

For example, each table in the User_tables table contains a separate row. Each row contains detailed information about a table, such as the physical storage parameters of the table. The extended information provided by this data tells you how the table is growing.

Each row of each table in the User_tables table in the User_tab_columns view contains a single record. If a table has 10 fields, you will find 10 rows of records in the User_tab_columns table, which is more information about each of these fields. such as the field data type. Field names are table_name in both user_table and user_tab_columns tables, so it's easy to connect them together.

User_objects User Objects View

All_objects All Objects View

Dba_objects dba Object View

User_sequences the user Sequence object view, the User_sequences record also appears in the User_objects table

3. Constrained view

There are two data dictionary views that provide detailed information about the constraints. User_constraints and User_cons_columns.

A table may or may not have constraints, and for each constraint on a table there is a record in user_constraints that describes the constraint. Includes the name of the table to which the constraint applies. If you know the constraint name and want to know the constraint type, query the User_constraints table. This view describes the definition of constraints. It does not provide constraints on which field names are defined. The constraint name is represented in Constraint_name in user_constraints, and if the constraint name is not specified when the table is established, the system default constraint name is similar to a string of letters like "sys_c006274". R_constraint_name is the constraint name of the Foreign key reference table primary key. Constraint_type field P: denotes primary key, R: denotes foreign key, c means not null or check,u represents unique

Displays the field name of the constraint in the User_cons_columns view. If the primary key is a federated primary key, there will be two records of this constraint in this view. Each field of a federated primary key corresponds to a record. Each record is distinguished by the position (position in the Federated primary key). You can associate User_constraints and user_cons_columns based on the Constraint_name field.

Three: Get the results you want

is divided into two steps:

PRIMARY KEY constraint name for query table

SELECT * from User_constraints e where e.table_name= '--Enter table name

Query all records referenced to this primary key

Select B.table_name,b.column_name from User_constraints a

INNER JOIN User_cons_columns b

On a.constraint_name = B.constraint_name

Where A.r_constraint_name= '-Enter the constraint name of the table's primary key that you just queried

Querying records in Oracle for all foreign key references to a table