You are here

Learn SQL Part-2

sql constraint

LEARN SQL: PART-2

In this post we will discuss constraints in SQL server.

  • SQL constraints are used to specify rules for storing data in to table. Constraints defined rules that must be applied to data before storing them into database/table to ensure integrity.
  • Only data, which satisfies the conditions will be store into table.
  • Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table created with ALTER TABLE statement.
  • It can be defined at the column level or table level. column level constraint apply to a column and table level constraint apply to the whole table.
  • The following types of constraints are used in SQL:
  1. NOT NULL
  2. PRIMARY KEY
  3. FOREIGN KEY
  4. DEFAULT
  5. CHECK
  6. UNIQUE

 

NOT NULL:

  • The NOT NULL constraint allows column to not accept NULL value. This enforce a column to always contain value.it means we can not insert or update row without value.
  • NOT NULL constraint is defined only at the column level.
  • When a column is defined as NOT NULL, then that column becomes a mandatory.

        Syntax:

        Col_name  datatype(size)  NOT NULL

 PRIMARY KEY:

  • A primary key is one or more column in a table used to uniquely identify each row in the table.
  • A primary key specifies two attributes.
  1. It defines column as mandatory means NOT NULL attribute is active.
  2. The data of primary key must be unique.
  • Only one primary key allowed per table.
  • For auto-increment primary key use IDENTITY(seed, increment) property. Seed means starting value and increment means how much unit incremented each time on seed value.
  • If you want to provide your own value turn IDENTITY to ON. After inserting value set it to OFF.
  • Use system variables and functions to return last inserted identity:
  1. @@IDENTITY: The last identity generated in the session.
  2. SCOPE_IDENTITY(): The last identity generated in the current scope.
  3. IDENT_CURRENT(): The last identity inserted into a table.

   Syntax (Primary key defined at column level):

   Col_name datatype(size) NOT NULL IDENTITY(seed, increment) constraint constraint_name PRIMATY KEY

   Syntax (Primary key defined at table level):

   PRIMARY KEY(col_name)

   Syntax for IDENTITY ON:

   Set IDENTITY_INSERT(‘table_name’) ON;

FOREIGN KEY:

  • It represents relationship between tables. A foreign key is column whose value derived from the primary key of some table.
  • A foreign key can be defined in either a CREATE TABLE or an ALTER TABLE statement.
  • The table which defined foreign key is called a foreign table or detail table. The table which defines the primary key and is referenced by the foreign key is called the primary table or master table.

        Syntax (foreign key defined at column level):

        Col_name datatype(size) constraint constraint_name references table_name(col_name)

        Syntax (foreign key defined at table level):

        Foreign key(col_name) references table_name(col_name)

Example:

create table person(id int NOT NULL IDENTITY(1,1) constraint pk_per_id PRIMARY KEY,name varchar(20));

create table address(aid int,state varchar(20),pid int constraint fk_add_id REFERENCES person(id));

insert into person(name) values(‘Riya’);

set IDENTITY_INSERT person ON;

select SCOPE_IDENTITY();    –2

insert into person(id, name) values(2,’kaya’);

set IDENTITY_INSERT person OFF;

insert into address(aid, state,pid) values(11,’Gujarat’,1),(22,’Maharashtra’,2);

select * from person;

select * from address;

OUTPUT:

person

id name
1 Riya
2 Kaya

address

aid state pid
11 Gujarat 1
22 Maharashtra 2

for DEFAULT, CHECK, UNIQUE constraints see my next post. learn-sql-part-3. If you have any question related to this post then comment us.

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