Constraints in Oracle

Constraints

Its apply rule on a table.when time of creating a table.

Constraints can be applied

column    level: Column level constraints apply to a column
table level: Table level constraints apply to a Whole table.

Types of Constraints:

1. Primary Key
2. Unique
3. Foriegn Key
4. Check
5. Not Null

Primary Key

• It is a entity integrity.
• It ignore Null values.
• It ignore duplicates.
• only one primary key accepted in a table.
• It automatically create unique index.

Primary Key = Unique Key + Not Null

Scenario : 

consider one table name as course and column are course table is  CId and CName. Here set cid is primary key.

Syntax : 

create table course123( CId Number, CName varchar(2) constraints c1 primary key(CId)) ;

Table Created;

insert into course123 values(1,'parthi');
insert into course123 values(2,'karthi');

Two Rows Inserted

insert into course123 values(1,'prakash');

when inserting this third row face this error

ORA-00001: unique constraint (HR.C1) violated 

Note: Here Cid value is 1 Its already inserted). The main thing in the primary key constraints is ignores null values and duplicates.

Unique Key

• Its a entity and integrity.
• It ignores duplicates.
• It accept null values and multiple null values.
• Its automatically generates unique as index.

Scenario : 

consider one table name as course234 and column are course table is  CId and CName. Here set cid is unique key.

Syntax : 

create table course( CId Number, CName varchar(2) constraints c2 unique(CId)) ;

Table Created;

insert into course234 values(1,'parthi');
insert into course234 values(2,'karthi');

Two Rows Inserted

insert into course234 values(1,'prakash');

when inserting this third row face this error

ORA-00001: unique constraint (HR.C1) violated 

insert into course234 values(null ,'prakash');

1 row inserted

Note: Here Cid value is 1 Its already inserted). The main thing in the unique constraints is ignores duplicate values but it accept null values.

select * from course234;

Output :

CID CNAME
1 parthi
2 karthi
- prakash

Not Null:

• Not Null is a domain integrity.
• It ignores Null Values. 

Syntax:

create table course345( CId Number, CName varchar(20), gender char(1) Not null);

Table Created

insert into course345 values(1,'parthi','m');
insert into course345 values(2,'karthi','m');

Two Rows Created

insert into course345 values(1,'arthi',null);

When run this above query its redirect to the below error

ORA-01400: cannot insert NULL into ("HR"."COURSE345"."GENDER")

Check:

• Check is a domain integrity
• It should satisfy some condition defined by a user.
• Modify option is not done in the check constraints.

Syntax :

create table course456( CId Number, CName varchar(20), gender char(1) Not null, constraints c3 check(gender in ('m','f')));

Table Created;

insert into course456 values(1,'parthi','m');
insert into course456 values(2,'arthi','f');

Two Rows inserted

insert into course456 values(2,'arthi','fq');

Error: ORA-12899: value too large for column "HR"."COURSE456"."GENDER" (actual: 2, maximum: 1) 

Note : On creating the table char size defined as 1 but in the table declared value as 2.

insert into course456 values(2,'arthi','h');

Error: ORA-02290: check constraint (HR.C3) violated

Note : The check constraints check some conditions set by the user.Here user set the condition gender as m or f. 

Foreign Key:

• Foreign Key is referential integrity.
• It refers primary key on unique constraints on another table.
• It accept null values.
• It accept duplicates. 

Syntax

Create table t4(cid number, cname Varchar(10), constraints c12 primary key (cid));

Table Created;

insert into t4 values (1, 'alex');
insert into t4 values (2, 'arun');

select * from t4;

Cid Cname
1 Alex
2 Arun

create table t112 (sid number, sname varchar(10),cid number, constraints c14 foreign key(cid) references t4(cid));
Table created

Here Cid is foreign key Its refers to the t4 table primary key.

insert into t112 values (10, 'alex',2 );

1 row inserted

cid= 2 is in the t4 table

insert into t112 values (10, 'alex',3 );

ORA-02291: integrity constraint (HR.C14) violated - parent key not found

Note : Here in the t4 table there is no cid value as 3 so its redirect to error


SQL Composite Key

Composite key is a key which is the combination of more than one field or column of a given table. It may be a candidate key or primary key.Columns that make up the composite key can be of different data types.

Syntax(Multiple Datatypes):

CREATE TABLE Compkey 
(COL1 integer, 
COL2 varchar(30), 
COL3 varchar(50), 
PRIMARY KEY (COL1, COL2));

Table Created;

insert into compkey values(1,'parthi','cse');
insert into compkey values(2,'karthi','cse');
insert into compkey values(1,'karthi','cse');

OutPut
Select * From comkey

COL1         COL2         COL3
1           parthi        cse
2           karthi        cse 
1           karthi        cse

Note: Both are different and unique Its not duplicate a duplicate of 1 

Syntax(Same Datatypes) 

CREATE TABLE compkey1 (
COL1 integer, 
COL2 varchar(30), 
COL3 varchar(50), 
PRIMARY KEY (COL2, COL3));

Here Col2 and Col3 set as a primary key

insert into compkey1 values(1,'par','cse');
insert into compkey1 values(1,'cse','par');
insert into compkey1 values(1,'ece','cse');
insert into compkey1 values(1,'mech',null);

Select * from comkey1
COL1        COL2           COL3
1           par            cse
1           cse            par
1           ece            cse

Note : when inserting null values it will not accepted
Key Points:
1. only one primary key is allowed in the table
2. modify option is not done in the check constraint
3. you can add N number of constraint with the help of modify column
4. you can add upto 32 column composite primary key

Table Copy

CREATE TABLE compkey1 
(COL1 integer, 
COL2 varchar(30), 
COL3 varchar(50), 
PRIMARY KEY (COL2, COL3));

Table Created;

insert into compkey1 values(1,'parthi','cse');
insert into compkey1 values(1,'cse','parthi');
insert into compkey1 values(1,'ece','cse');

create table copy as(select * from compkey1); -- compkey1 table having two composite primary keys.

insert into copy values( 1,'parthi','cse');
insert into copy values( 2,'parthi','cse');

It  does copy the data and table structure and it does  not copy the constraint. 

Data Dictionary table : Its contain all information about the data.

select *from user_constraints uc where uc.table_name = upper('Sts_Com_PKey');

select *from user_cons_columns ucc  where ucc.table_name =upper( 'Sts_Com_PKey');

CASCADE -- Cascade option in constraints

CREATE TABLE buildings 
(   building_no number  primary key ,
    building_name VARCHAR2(255) NOT NULL,
    Address_details VARCHAR2(255) NOT NULL
);

select * from user_cons_columns where table_name=upper('buildings');

CREATE TABLE rooms (
    room_no number PRIMARY KEY ,
    room_name VARCHAR(255) NOT NULL,
    building_no INT NOT NULL,
    FOREIGN KEY (building_no)
        REFERENCES buildings (building_no)
        ON DELETE CASCADE
);

INSERT INTO buildings VALUES(1,'ACME Headquaters','3950 North 1st Street CA 95134');

INSERT INTO buildings VALUES(2,'ACME Sales','5000 North 1st Street CA 95134');

select * from buildings;

INSERT INTO rooms VALUES(1,'Amazon',1);
INSERT INTO rooms VALUES(2,'War Room',1);
INSERT INTO rooms VALUES(3,'Office of CEO',1);
INSERT INTO rooms VALUES(4,'Marketing',2);
INSERT INTO rooms VALUES(5,'Showroom',2);

select * from rooms;

DELETE FROM buildings 
WHERE
    building_no = 2;

After deleting buildings table value 2

INSERT INTO buildings VALUES(2,'ACME Sal','5000 South 1st Street CA 95134');

Then insert the values as 2. it will not be inserted in the second table.

Without Cascade option 

CREATE TABLE build 
(   building_no number  primary key ,
    building_name VARCHAR2(255) NOT NULL,
    Address_details VARCHAR2(255) NOT NULL
);

CREATE TABLE room12 (
    room_no number PRIMARY KEY ,
    room_name VARCHAR(255) NOT NULL,
    building_no INT NOT NULL,
    FOREIGN KEY (building_no)
        REFERENCES buildings (building_no)
);

INSERT INTO build VALUES(1,'ACME Headquaters','3950 North 1st Street CA 95134');

INSERT INTO build VALUES(2,'ACME Sales','5000 North 1st Street CA 95134');

INSERT INTO room12 VALUES(1,'Amazon',1);
INSERT INTO room12 VALUES(2,'War Room',1);
INSERT INTO room12 VALUES(3,'Office of CEO',1);
INSERT INTO room12 VALUES(4,'Marketing',2);
INSERT INTO room12 VALUES(5,'Showroom',2);

select * from build;

select * from room12;

DELETE FROM build 
WHERE
    building_no = 2;

ON DELETE SET NULL

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10),
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id)
    ON DELETE SET NULL
);

insert into products values(10,1);
insert into products values(20,1);
insert into products values(30,2);
insert into products values(40,1);
insert into products values(50,3);

Select * from supplier;
Select * from products;

Delete from supplier where supplier_id=1;
-- after deleting the record all will be changed into null in the foriegn key column

--ENABLE DISABLE NOVALIDATE

CREATE table t123
( 
s_id number(3),
Sname varchar2(20),
CONSTRAINT s_id_pk1 primary key (s_id),
CONSTRAINT Sname_uk1 unique (Sname));

INSERT into t123 VALUES(1,'parthi');

INSERT into t123 VALUES(2,'karthi');

ALTER table t123 DISABLE CONSTRAINT S_id_pk1;

insert into t123 VALUES(1,'keerthi');

SELECT * FROM t123;

ALTER table t123 ENABLE constraint S_id_pk1;

ORA-02437: cannot validate (HR.S_ID_PK1) - primary key violated

CREATE index idx1 on t123(s_id); 

ALTER table t123 ENABLE NOVALIDATE constraint S_id_pk1;


select * from t123;