5.6. Data Dictionary Constraint Scripts

5.6.1. Constraints on a Table

The following query is a general script to query the constraints of a table. The use of the script accepts a table name as a command line argument.

SQL> @my_const_are table_name

Following the script text is the SQL*Plus output from the STUDENTS table.

 -- Filename: my_const_are.sql set verify off column column_name format a15 column table_name format a12 set pagesize 1000 SELECT SUBSTR(A.column_name,1,30) column_name, DECODE(B.constraint_type, 'P', 'PRIMARY KEY', 'U', 'UNIQUE KEY', 'C', 'CHECK OR NOT NULL', 'R', 'FOREIGN KEY') constraint_type, A.constraint_name FROM user_cons_columns A, user_constraints B WHERE A.table_name = UPPER('&1') AND A.table_name = B.table_name AND ...

Get Programming Oracle® Triggers and Stored Procedures, Third Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.