Constraints

Common Types

PRIMARY KEY (PK) - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of columns) have an unique identity which helps to find a particular record in a table more easily and quickly.

CHECK (CHK) - Ensures that the value in a column meets a specific condition.

FOREIGN KEY (FK) - Ensure the referential integrity of the data in one table to match values in another table

NOT NULL (NN) - Indicates that a column cannot store a NULL value.

UNIQUE (UNQ) - Ensures that each row for a column must have a unique value.

Create

ALTER TABLE
   table_name
ADD CONSTRAINT
   check_constraint_name
   CHECK 
   (check_column_name IN 
      (
       'check_constraint1_value',
       'check_constraint2_value',
       'check_constraint3_value',
       'check_constraint4_value'
      )
   ) DISABLE|ENABLE;

ALTER TABLE
   cust
ADD CONSTRAINT
   fk_cust_name FOREIGN KEY (person_name, person_gender)
REFERENCES
   person_table (person_name, person_gender)
INITIALLY DEFERRED deferrable;

Display

-- Total Constraints For Schema

SELECT count(*) FROM all_constraints 
WHERE owner = &SchemaName;

-- User Constraints that are not ENABLED

SELECT owner,table_name,constraint_name 
FROM all_constraints 
WHERE owner NOT IN ('SYS','SYSTEM')
AND  status != 'ENABLED'
ORDER BY owner,table_name,constraint_name;

Drop

ALTER TABLE [Schema].TableName DROP CONSTRAINT ConstraintName;

ALTER TABLE HR.EMPLOYEE DROP CONSTRAINT 'EMPLOYEE_PK';

Enable|Disable

ALTER TABLE [Schema].TableName ENABLE  CONSTRAINT ConstraintName;
ALTER TABLE [Schema].TableName DISABLE CONSTRAINT ConstraintName [CASCADE];

ALTER TABLE hr.emp DISABLE CONSTRAINT chk_BusinessUnit;

<- Schema