Constraints

Constraints are used to limit the type of data that can go into the table. It can be specified during or after table creation. There are 6 constraints.

* Not Null Constraint

* Unique Constraint

* Primary Key Constraint

* Foreign Key Constraint

* Check Constraint

* Default Constraint

Not Null Constraint

It enforces the column not to accept the null values, also enforces a field always contain a value means you cannot insert or update null records. 

Ex:

CREATE TABLE SUPPLIERS

(      SUPPLIER_ID       NUMBER Not Null,

       SUPPLIER_NAME   VARCHAR2(30) CHECK SUPPLIER_NAME=UPPER(SUPPLIER_NAME)

);

-      Using ALTER statement

ALTER TABLE SUPPLIERS modify SUPPLIER_ID NOT NULL;

Unique Constraint

- The Unique Constraint identifies each record in database table. 

- It’s a single field or the combination of fields that uniquely defines the record.

- Some of the fields may contain the null values as long as the record is unique.

- Can have more than one unique constraint in table

Ex:

CREATE TABLE supplier

( supplier_id numeric(10) not null,

  supplier_name VARCHAR2(50) not null,

  CONSTRAINT supplier_uk UNIQUE (supplier_id, supplier_name)

);

-      Using ALTER statements

ALTER TABLE supplier ADD CONSTRAINT s_uk1 UNIQUE (supplier_id);

ALTER TABLE supplier ADD CONSTRAINT s_UK2 UNIQUE (supplier_id, supplier_name);

Primary Key Constraint:

- Primary Key Constraint defines each record uniquely in the field.

- It’s defined on Table column during or after table creation.

- There is only one Primary Key for a table.

- It does not accept Null or Duplicate Values.

- Primary Key Constraint may be of single column or combination of column.

Ex:

CREATE TABLE supplier

(

  supplier_id numeric(10) not null,

  supplier_name VARCHAR2(50) not null,

  CONSTRAINT pk_1 PRIMARY KEY (supplier_id)

);

- For more than one column

CREATE TABLE supplier

(

  supplier_id numeric(10) not null,

  supplier_name VARCHAR2(50) not null,

  CONSTRAINT pk_1 PRIMARY KEY (supplier_id, supplier_name)

);

- Using ALTER statement

ALTER TABLE supplier ADD CONSTRAINT s_pk1 PRIMARY KEY (supplier_id);

ALTER TABLE supplier ADD CONSTRAINT s_pk2 PRIMARY KEY (supplier_id,supplier_name);

Foreign Key Constraint:

- It’s the referential integrity string which creates relationship between any two tables.

- Foreign key accepts Null and Duplicate Values.

- To Create Foreign Key the master table should contain Primary Key defined.

- A composite foreign key cannot have more than 32 columns.

Ex:

CREATE TABLE supplier

( supplier_id NUMBER NOT NULL,

  supplier_name VARCHAR2(50) NOT NULL,

  CONSTRAINT key_pk1 PRIMARY KEY (supplier_id)

);

CREATE TABLE products

( product_id NUMBER NOT NULL,

  supplier_id NUMBER NOT NULL,

  CONSTRAINT fkey_1 FOREIGN KEY (supplier_id)

  REFERENCES supplier(supplier_id)

  ON DELETE CASCADE

);

- Using ALTER statement

ALTER TABLE products ADD CONSTRAINT fk_Key2 FOREIGN KEY (supplier_id)

REFERENCES supplier (supplier_id) on DELETE CASCADE;

Default Constraint:

- Its used to provide the default values for the columns.

- Once we provide the default value, the same value will be used to populate into a column when we don’t

  Provide a value explicitly.

Ex:

CREATE TABLE supplier

( supplier_id NUMBER NOT NULL,

  supplier_name VARCHAR2(50) NOT NULL DEFAULT 'xyz'

);

CREATE TABLE products

( product_id NUMBER NOT NULL,

  supplier_id NUMBER DEFAULT 0

);

Check Constraint:

- It’s used for providing the additional data validation in a db.

- Helps to solve business requirements.

- Check Constraint cannot be defined on view.

- Check Constraint cannot include the sub-query.

Ex:

CREATE TABLE suppliers

(        SUPPLIER_ID            NUMBER,      

          SUPPLIER_NAME       VARCHAR2(50),       

          CONSTRAINT           check_supplier_id CHECK (supplier_id BETWEEN 100 and 9999)

);

CREATE TABLE SUPPLIERS

(      SUPPLIER_ID       NUMBER CHECK (SUPPLIER_ID BETWEEN 100 AND 9999),

       SUPPLIER_NAME   VARCHAR2(30) CHECK SUPPLIER_NAME=UPPER(SUPPLIER_NAME)

);

-     -   Using ALTER statement

ALTER TABLE suppliers add CONSTRAINT check_supplier_name

CHECK (supplier_name IN ('INTEL','TCS','WIPRO'));

Enable Constraint:

ALTER TABLE table_name enable CONSTRAINT constraint_name;

Disable Constraint:

ALTER TABLE table_name disable CONSTRAINT constraint_name;

Droping Constraint:

ALTER TABLE table_name drop CONSTRAINT constraint_name;

Oracle Constraint Errors

The following errors are associated with Oracle constraint alter commands:

• ORA-02290: check constraint (owner.constraintname) violated 
• ORA-02291: integrity constraint (owner.constraintname) violated - parent key not found
• ORA-02292:violated integrity constraint (owner.constraintname)- child record found