The following query is used to determine this information. You can investigate a particular constraint by joining the table and column name, from USER_CONS_COLUMNS view, with the definition of the constraint from the view USER_CONSTRAINTS. As described in Chapter 3, an Oracle error message on these constraints includes the table name and column name. SQL> students COLUMN_NAME CONSTRAINT_TYPE CONSTRAINT_NAME - STATE UNIQUE KEY UK_STUDENTS_LICENSE LICENSE_NO UNIQUE KEY UK_STUDENTS_LICENSE STUDENT_ID PRIMARY KEY PK_STUDENTS STATE FOREIGN KEY FK_STUDENTS_STATE STUDENT_ID CHECK OR NOT NULL SYS_C002073 STUDENT_NAME CHECK OR NOT NULL SYS_C002074 COLLEGE_MAJOR CHECK OR NOT NULL SYS_C002075 STATUS CHECK OR NOT NULL SYS_C002076Īll the constraint names that begin with SYS are NOT NULL constraints. The script file name is MY_CONST_ARE.SQL. 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 A.constraint_name = B.constraint_name AND a.owner = b.owner ORDER BY 2 DESC clear columns SQL> table_nameįollowing the script text is the SQL*Plus output from the STUDENTS table.

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

Tableplus check constraint