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:
- NOT NULL
- PRIMARY KEY
- FOREIGN KEY
- 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.
Col_name datatype(size) NOT NULL
- 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.
- It defines column as mandatory means NOT NULL attribute is active.
- 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:
- @@IDENTITY: The last identity generated in the session.
- SCOPE_IDENTITY(): The last identity generated in the current scope.
- 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):
Syntax for IDENTITY ON:
Set IDENTITY_INSERT(‘table_name’) ON;
- 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)
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;
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.