SQL - CONSTRAINTSWhat 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 :
Types of SQL Constraints :
How to check constraints
How to check column level constraints :
NOT NULL Constraint
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 SdbtNow, 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 : Alter table tesdb add constraint prk primary key; Alter table tesdb drop constraint prk; FOREIGN KEY Constraint : How to check table level constraints : How to use on delete cascade : ☛ Join to Learn from Experts: Oracle SQL Training in Chennai by TesDBAcademy
« Previous
Next Topic »
(SQL - Joins)
|