You are here

Learn SQL Part-3

learn sql

LEARN SQL: PART-3

In this post we will discuss DEFAULT, CHECK,UNIQUE constraints with example.

DEFAULT:

  • Default constraint inserts the default value into a column when you do not provide value for column.
  • At the time of table creation default value is assigned to column. the datatype of the value should match the data type of the column.
  • Character and date values will be specified in single quotes.

           Syntax:

            Col_name datatype(size) DEFAULT value

 

CHECK:

  • Check constraint limits the values that are accepted by one or more columns.
  • It must be specified as a logical expression that evaluates either to TRUE or FALSE.
  • The check constraint requires that a condition be true for the row to be inserted.
  • The condition can not contain sub queries.

      Syntax (check defined at column level):

       Col_name datatype(size) CHECK logical_expression

     Syntax (check defined at column level):

      CHECK(logical_expression)

UNIQUE KEY:

  • Unique key will not allow duplicate value. A table can have more than one unique key. It allows multiple entry of NULL into the column.
  • These NULL values are clubbed at the top of the column in the order in which they were entered into the table.
  • Unique index is created automatically.

       Syntax:

       Col_name datatype(size) UNIQUE

EXAMPLE:

use patel;

create table employee(id int NOT NULL IDENTITY(1,1) constraint pk_eid PRIMARY KEY,ename nvarchar(20),city nvarchar(20) DEFAULT ‘Anand’);

create table salary(said int UNIQUE,salary money CHECK(salary>10000),id int constraint fk_id references employee(id));

insert into employee(ename, city) values(‘john’,’vvnagar’);

insert into employee(ename) values (‘sara’);                 –default value of city

select SCOPE_IDENTITY();                                             –returns last identity inserted into column,it returns 2

set IDENTITY_INSERT employee ON;                           –to insert own primary key value

insert into employee(id, ename) values(3,’Riya’);

set IDENTITY_INSERT employee OFF;                        –off identity_insert

insert into salary(said, salary,id) values(1,20000,1);

insert into salary(said, salary,id) values(2,40000,2);

insert into salary(said, salary,id) values(3,60000,3);

select * from employee;

select * from salary;

OUTPUT:

employee:  

Id ename City
1 John vvnagar
2 Sara Anand
3 Riya Anand

 salary:

said

salary

id

1

20000.00

1

2

40000.00

2

3

60000.00

3

Keep Visiting TechwithR.com for more upcoming top technical articles.

Detail course about DBA visit TechNet Consultancy

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

Leave a Reply

Top