Menu
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.
994
The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a column it will allow only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
The syntax for enabling a check constraint in SQL Server (Transact-SQL) is: ALTER TABLE table_name WITH CHECK CHECK CONSTRAINT constraint_name; table_name. The name of the table that you wish to enable the check constraint.
Type a short Oracle program, using the following code as a guide: DECLARE record_exists INTEGER; BEGIN SELECT COUNT(*) INTO record_exists FROM your_table WHERE search_field = 'search value' AND ROWNUM = 1; IF record_exists = 1 THEN DBMS_OUTPUT. put_line('Record Exists') ELSE DBMS_OUTPUT.
The Oracle EXISTS condition is used in combination with a subquery and is considered "to be met" if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME = 'CONSTR_NAME';
THE CONSTRAINT_TYPE will tell you what type of contraint it is
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.
139
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
