SQL for Oracle to check if a constraint exists
oracle, sql, sql-server
Solution
SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME = 'CONSTR_NAME';
THE `CONSTRAINT_TYPE` will tell you what type of contraint it is
- R - Referential key ( foreign key)
- U - Unique key
- P - Primary key
- C - Check constraint
To find out if an object is a trigger, you can query `USER_OBJECTS`. `OBJECT_TYPE` will tell you if the object's a trigger, view, procedure et al.
Problem
In SQL Server I can use the SQL below to check if a constraint exists and if it's a primary key, trigger, etc. ``` SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[SCHEMA].[TABLENAME]') AND OBJECTPROPERTY(id, N'IsPrimaryKey') = 1 ``` What would be the Oracle equivalent because my query uses SQL Server specific tables to find the answer.