SQL - CONSTRAINTS

What are constraints in SQL?

SQL constraints define rules for the data in a table. Constraints are used to limit the kind of data that can be entered into a table. This ensures that the table's data is accurate and reliable.

Types of Constraints :

• Domain Integrity Constraints ---- Not null, Check, Default
• Entity Integrity Constraints ----- Unique, Primary Key
• Referential Integrity Constraints --- Foreign Key

Types of SQL Constraints :

• NOT NULL Constraint
• UNIQUE Constraint
• DEFAULT Constraint
• CHECK Constraint
• PRIMARY KEY Constraint
• FOREIGN KEY Constraint

How to check constraints

• Desc user_constraints;
• Desc dba_constraints;
• Desc all_constraints;

How to check column level constraints :

• Desc dba_cons_columns;
• Desc all_cons_columns;
• Desc user_cons_columns;

NOT NULL Constraint

• Makes sure a column cannot contain a null value.

Create table t1 (id number constraint nn not null,name varchar2(20));

SQL> Create table t1 (id number constraint nn not null,name varchar2(20));

Table created.

SQL> insert into t1 values (1,'aa');

1 row created.

SQL> insert into t1 (name) values ('bb');
insert into t1 (name) values ('bb')
*
ERROR at line 1: ORA-01400: cannot insert NULL into ("TESDB"."T1"."ID")


Now,
Alter table t2 modify name varchar2(20) constraint nt not null;

SQL> create table t2 (id number,name varchar2(20));
Table created.
SQL> alter table t2 modify id number constraint nt not null;
Table altered.
SQL> insert into t2 values (1,'tesdb');
1 row created.
SQL> insert into t2 (name) values ('ccc');
insert into t2 (name) values ('ccc')
*
ERROR at line 1:ORA-01400: cannot insert NULL into ("TESDB"."T2"."ID")

SQL> alter table t2 disable constraint nt;
Table altered.
SQL> select * from t2;

ID       NAME
---------- --------------------
1         tesdb

SQL> insert into t2 values (2,'aaa');

1 row created.

SQL> insert into t2 (name) values('sdbt');

1 row created.

SQL> select * from t2;

ID NAME
---------- --------------------
1 tesdb
2 aaa
Sdbt
		

Now, Alter table t2 drop constraint nt;

UNIQUE Constraint :

Guarantees that each value in a column is unique.

Alter table sample add constraint uni unique(name);

Alter table sample drop constraint uni;

DEFAULT Constraint :

Sets a column's default value in the event that no value is specified.

create table con_def (id number,name varchar2(20),course_center varchar2(20) default ‘chennai’);

Alter table con_defu1 modify course_name varchar2(20) default ‘ORACLEDBA’;

Alter table con_defu1 modify course_name varchar2(20) default null;

CHECK Constraint :

Verifies that a column's values meet a predetermined requirement.

create table con_chk (id number,name varchar2(20),location varchar2(20) constraint ck check (location in ('south','north','east','west')));

Alter table tesdb add constraint chk check (salary>1000);

Alter table tesdb drop constraint chk ;

PRIMARY KEY Constraint :

• A blend of UNIQUE and NOT NULL.
• Each row in a table is uniquely identified by its main key.
• A table may have only one primary key. Create table parent_tab (id number constraint pk primary key,name varchar2(20));

Alter table tesdb add constraint prk primary key;

Alter table tesdb drop constraint prk;

FOREIGN KEY Constraint :

• Avoids operations that could break the connections between tables
• A field, or group of fields, in one table that points to the primary key in another table is called a foreign key.

Create table child_tab (id number,constraint fk foreign key(id) references parent_tab(id),English_mark int,tamil_mark int);

How to check table level constraints :

How to use on delete cascade :

• The corresponding records in the child table will automatically be erased whenever a record in the parent table is deleted. Create table child_tab1 (id number,constraint fk foreign key(id) references parent_tab(id) on delete cascade,English_mark int,tamil_mark int);