Inspecting constraints
Types of Constraints
- Primary Key: Ensures that each row in a table has a unique identifier and that no null values are allowed in the primary key column(s).
- Foreign Key: Ensures referential integrity between tables by requiring that a value in one table matches a value in another table.
- Unique: Ensures that all values in a column or a group of columns are unique across the table.
- Check: Ensures that all values in a column meet a specific condition or set of conditions.
- Not Null: Ensures that a column does not contain null values.
Inspecting Constraints in Oracle
Inspecting Primary Key Constraints
To view primary key constraints for a table, you can query the ALL_CONS_COLUMNS and ALL_CONSTRAINTS views:
SELECT constraint_name, column_name FROM all_cons_columns WHERE table_name = 'TABLE_NAME' AND constraint_name IN ( SELECT constraint_name FROM all_constraints WHERE constraint_type = 'P' AND table_name = 'TABLE_NAME' );
Inspecting Foreign Key Constraints
To view foreign key constraints and the tables they reference, use the following query:
SELECT a.constraint_name, a.column_name, c.owner, c.table_name, c.column_name FROM all_cons_columns a JOIN all_constraints b ON a.constraint_name = b.constraint_name JOIN all_cons_columns c ON b.r_constraint_name = c.constraint_name WHERE b.constraint_type = 'R' AND a.table_name = 'TABLE_NAME';
Inspecting Unique Constraints
To see unique constraints and the columns they apply to:
SELECT constraint_name, column_name FROM all_cons_columns WHERE constraint_name IN ( SELECT constraint_name FROM all_constraints WHERE constraint_type = 'U' AND table_name = 'TABLE_NAME' );
Inspecting Check Constraints
To view check constraints and their expressions:
SELECT constraint_name, search_condition FROM all_constraints WHERE constraint_type = 'C' AND table_name = 'TABLE_NAME';
Inspecting Not Null Constraints
Not null constraints are a bit different and are typically part of the column definition. To inspect not null constraints:
SELECT column_name FROM all_tab_columns WHERE table_name = 'TABLE_NAME' AND nullable = 'N';
Inspecting Constraints in Other SQL Databases
SQL Server
Primary Key Constraints:
SELECT k.name AS constraint_name, c.name AS column_name FROM sys.key_constraints k JOIN sys.index_columns ic ON k.object_id = ic.object_id AND k.unique_index_id = ic.index_id JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE k.type = 'PK' AND OBJECT_NAME(k.parent_object_id) = 'TABLE_NAME';
Foreign Key Constraints:
SELECT fk.name AS constraint_name, tp.name AS parent_table, ref.name AS referenced_table, c.name AS column_name FROM sys.foreign_keys fk JOIN sys.tables tp ON fk.parent_object_id = tp.object_id JOIN sys.tables ref ON fk.referenced_object_id = ref.object_id JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id WHERE tp.name = 'TABLE_NAME';
Unique Constraints:
SELECT u.name AS constraint_name, c.name AS column_name
FROM sys.indexes u
JOIN sys.index_columns ic ON u.object_id = ic.object_id AND u.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE u.is_unique = 1 AND OBJECT_NAME(u.object_id) = 'TABLE_NAME';
Check Constraints:
SELECT name AS constraint_name, definition FROM sys.check_constraints WHERE OBJECT_NAME(parent_object_id) = 'TABLE_NAME';
MySQL
Primary Key Constraints:
SELECT constraint_name, column_name FROM information_schema.key_column_usage WHERE table_name = 'TABLE_NAME' AND constraint_name IN ( SELECT constraint_name FROM information_schema.table_constraints WHERE constraint_type = 'PRIMARY KEY' AND table_name = 'TABLE_NAME' );
Foreign Key Constraints:
SELECT constraint_name, column_name, referenced_table_name, referenced_column_name FROM information_schema.key_column_usage WHERE table_name = 'TABLE_NAME' AND referenced_table_name IS NOT NULL;
Unique Constraints:
SELECT constraint_name, column_name FROM information_schema.key_column_usage WHERE table_name = 'TABLE_NAME' AND constraint_name IN ( SELECT constraint_name FROM information_schema.table_constraints WHERE constraint_type = 'UNIQUE' AND table_name = 'TABLE_NAME' );
Check Constraints:
SELECT constraint_name, check_clause FROM information_schema.check_constraints WHERE table_name = 'TABLE_NAME';
PostgreSQL
Primary Key Constraints:
SELECT constraint_name, column_name FROM information_schema.key_column_usage WHERE table_name = 'TABLE_NAME' AND constraint_name IN ( SELECT constraint_name FROM information_schema.table_constraints WHERE constraint_type = 'PRIMARY KEY' AND table_name = 'TABLE_NAME' );
Foreign Key Constraints:
SELECT constraint_name, column_name, foreign_table_name, foreign_column_name FROM information_schema.key_column_usage WHERE table_name = 'TABLE_NAME' AND foreign_table_name IS NOT NULL;
Unique Constraints:
SELECT constraint_name, column_name FROM information_schema.key_column_usage WHERE table_name = 'TABLE_NAME' AND constraint_name IN ( SELECT constraint_name FROM information_schema.table_constraints WHERE constraint_type = 'UNIQUE' AND table_name = 'TABLE_NAME' );
Check Constraints:
SELECT constraint_name, check_clause FROM information_schema.check_constraints WHERE table_name = 'TABLE_NAME';
Conclusion
Inspecting constraints is vital for ensuring data integrity and enforcing business rules within a database. By querying the appropriate system views or tables, you can review the constraints applied to your tables and understand how they contribute to maintaining data consistency and correctness.