How to drop unnamed unique constraint in ORACLE


The following statement drops the unique key on the email column of the employees table:

ALTER TABLE employees DROP UNIQUE (email);

The DROP clause in this statement omits the CASCADE clause. Because of this omission, Oracle Database does not drop the unique key if any foreign key references it.

Description of the illustration drop_constraint_clause

Advertisements

List indexes which you named intentionally


Here’s a nice Oracle SQL script that lists indexes which you named on purpose.

SELECT index_name 
FROM all_indexes 
WHERE table_owner = 'SCHEME' --schema name
AND table_name = 'TABLE' --table name
AND index_name NOT LIKE 'SYS%';

if you comment out this part, this script lists all indexes.

AND index_name NOT LIKE 'SYS%';

Oracle prefixes “SYS_” constraints and indexes when u dont give names to them. That’s why we add the condition.

Find the tables referencing a specific table by foreign key


SELECT a.owner || '.' || a.table_name "Referenced Table", 
 b.owner || '.' || b.table_name "Referenced by", 
 b.constraint_name "Foreign Key", 
 b.constraint_type 
FROM all_constraints a, 
 all_constraints b 
WHERE b.constraint_type = 'R' 
AND a.constraint_name = b.r_constraint_name 
AND a.table_name = 'TABLE1' -- Table name
AND a.owner = b.r_owner
ORDER BY 1;

Source: http://www.databasejournal.com/features/oracle/article.php/3665591/Finding-Foreign-Key-Constraints-in-Oracle.htm