Constraints:-it specify the rules for data. for maintaining some rules and standard of data we use sql constraints.we place constraints at the time table creation or in the create statement and also in alter statement.
Types of constraints present in sql:-
NOT NULL:-it restrict the user for storing the null value .if any column contain not null constraints then that column does not allow null value .
Ex:-create table which has not null constraints.
simply we add "not null" after the column name
insert the value .
in this we insert only id it successfully insert because we are entering in id column it does not give any error because not null is used only on id column.
when we insert the value in name column only ,it will give an error violation of not null key or you cannot enter any null value.
Unique Key:-it is used to restrict the user to entering the duplicate data.if any column has unique then we cannot insert same data.we can add any number of unique key in a same table.unique key also allows the null value.
Ex:-we can add unique key into the column n 3 ways which are as follows:-
now we insert the value in the table emp1
when we entering the same value "1" again it will give an error.
Primary Key:-it is the combination of not null and unique key.it does not allow same data and the null values.
but we can use only one primary key in a table.
Ex:-we can add primary key into the column n 3 ways which are as follows:-
now we insert the value
above insert statement fulfills all the condition of primary key
now we apply primary key on name column and in below insert statement we provide the value for id and age column so by default it will take null value in name column but we apply primary key for name so it will give an error.and 2nd error show when we enter the same data again.
we cannot use more than 1 primary key in a same table i.e.
here we try to use more than 1 primary key it will throw an error
if you want to use more than one primary key then use not null and unique key for one column and simply primary key for another column so in this way you can use any number of primary key in a same table
Foreign key:-Foreign key of one table relates to the primary key of another table which is also known as referential integrity .for this we use 2 tables.we have at least one same column in both table .we apply primary key in table1 column and in second table we apply foreign key to same column.
Ex:-create two tables like this.
in first table stu2 we take 3 columns in which we apply primary key on id column.
in second table stu3 we take 3 columns in which first column same as table 1 first column
and we apply foreign key in that way
now enter the data in both table
id column of table 2 i.e stu3 points the id column of table1 i.e. stu2.
it takes value when the id column of both table will same.
if you entering an id in table 2 which is not present in table 1 it will give an error.
Ex:-we enter the value of id in table2 =3 which is not present in table 1 .Table 1 contains only id value=1,2
we give here 3 which will give an error.
so it proves that id column of table 2 points to the id column of table1
Check:-it is used to provide rule for the data or you can say that validation of the data occurs.
you give condition in the check key..
Ex:- here we define the condition for id column which takes 10 as a value if we enter other than 10 in id column it will give an error.
here we give 10 successfully enters
here we give other than 10 which will give an error
Ex;-here id takes those value which is greater than 10
here we give 11 successfully enters
here we give 10 which gives an error
Default:-it is used to provide default value for the column.if user forget to enters the value or not want to enter the value then it will take default value.
Ex:-here we give default value "sql" to the name column if users does not enters any value it will take "sql"
by default.
here in second insert statement we did not enter name and by default it took sql
this is all about the keys use in the sql.........
Types of constraints present in sql:-
- NOT NULL
- UNIQUE KEY
- PRIMARY KEY
- FOREIGN KEY
- CHECK
- DEFAULT
NOT NULL:-it restrict the user for storing the null value .if any column contain not null constraints then that column does not allow null value .
Ex:-create table which has not null constraints.
simply we add "not null" after the column name
insert the value .
when we insert the value in name column only ,it will give an error violation of not null key or you cannot enter any null value.
Unique Key:-it is used to restrict the user to entering the duplicate data.if any column has unique then we cannot insert same data.we can add any number of unique key in a same table.unique key also allows the null value.
Ex:-we can add unique key into the column n 3 ways which are as follows:-
now we insert the value in the table emp1
Primary Key:-it is the combination of not null and unique key.it does not allow same data and the null values.
but we can use only one primary key in a table.
Ex:-we can add primary key into the column n 3 ways which are as follows:-
now we insert the value
above insert statement fulfills all the condition of primary key
now we apply primary key on name column and in below insert statement we provide the value for id and age column so by default it will take null value in name column but we apply primary key for name so it will give an error.and 2nd error show when we enter the same data again.
we cannot use more than 1 primary key in a same table i.e.
here we try to use more than 1 primary key it will throw an error
if you want to use more than one primary key then use not null and unique key for one column and simply primary key for another column so in this way you can use any number of primary key in a same table
Ex:-create two tables like this.
in first table stu2 we take 3 columns in which we apply primary key on id column.
in second table stu3 we take 3 columns in which first column same as table 1 first column
and we apply foreign key in that way
now enter the data in both table
id column of table 2 i.e stu3 points the id column of table1 i.e. stu2.
it takes value when the id column of both table will same.
if you entering an id in table 2 which is not present in table 1 it will give an error.
Ex:-we enter the value of id in table2 =3 which is not present in table 1 .Table 1 contains only id value=1,2
we give here 3 which will give an error.
so it proves that id column of table 2 points to the id column of table1
Check:-it is used to provide rule for the data or you can say that validation of the data occurs.
you give condition in the check key..
Ex:- here we define the condition for id column which takes 10 as a value if we enter other than 10 in id column it will give an error.
here we give 10 successfully enters
here we give other than 10 which will give an error
Ex;-here id takes those value which is greater than 10
Default:-it is used to provide default value for the column.if user forget to enters the value or not want to enter the value then it will take default value.
Ex:-here we give default value "sql" to the name column if users does not enters any value it will take "sql"
by default.
here in second insert statement we did not enter name and by default it took sql
this is all about the keys use in the sql.........
No comments :
Post a Comment