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);

« Previous Next Topic » (SQL - Joins)