CODEWITHSUNDEEP
sqlsql-serveroracle-database
Ryan Rodemoyer
Ryan Rodemoyer

Reputation: 5692

SQL for Oracle to check if a constraint exists

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.

Upvotes: 15

Views: 36894

Answers (1)

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

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.

Upvotes: 34

Related Questions