Description
By using DB2 Constraints, different number of conditions can be applied on a column. Constraints setup the RDBMS and execute the integrity of the database automatically, without need to create triggers and rules. DB2 Constraints are utilized to limit the type of information that can be inserted into a table.
DB2 Constraints can be classified into the following types.They are:
- Unique Constraint
- NOT Null Constraints
- Primary Key Constraints
- Forign Key Constraints
- Check constraint
Description
By defining a column with unique constraint, then that unique constraint column will not accept duplicate values i.e, unique constraint will accept a single character or number. It will not accept repeated values in the table.
Syntax
create table <table_name>(<column_name1> datatype(size) constraint type, <column_name2> datatype(size));
table_name => Any accurate table in the database.
column_name => The operation that can be perform on a column in the table.
constraint_type => constraint type will specify the type of constraint, that going to be executed in the database.
Examples
By viewing the below example, the concept of unique constraint can be understood easily.
[sql]
db2 => create table stud2(stu_id int unique,stu_name varchar(255),fee int);
DB20000I The SQL command completed successfully.
db2 => insert into experience.stud2(stu_id, stu_name, fee) values(1001,'Rock',12000);
SQL executed ok (0.52 sec)
db2 => insert into experience.stud2(stu_id, stu_name, fee) values(1002,'hairy',13000);
SQL executed ok (0.52 sec)
db2 => insert into experience.stud2(stu_id, stu_name, fee) values(1003,'scena',13000);
SQL executed ok (0.52 sec)
db2 => insert into experience.stud2(stu_id, stu_name, fee) values(1003,'joe',13000);
ERROR 1062 (23000): Duplicate entry '1003' for key 'stu_id'[/sql]
In the above example, the unique constraint is applied to column_name stu_id in which only unique stu_id's values will be accept, and duplicate stu_id values will not be accepted.
Description
By defining a column with not null constraint then that constraint column won't acknowledge null values.
Syntax
create table( datatype(size)constraint_type, datatype(size);
table_name => Any accurate table.
Column_name =>The operation that can be perform on a column in the table.
constraint_type =>constraint type will specify the type of constraint, that going to be executed in the database.
Examples
By viewing the below example, the concept of not null constraint can be easily understood.
[sql]
db => create table stud12(stu_id int not null,stu_name varchar(255)not null,fee int);
DB20000I The SQL command completed successfully.
db2 => insert into experience.stud12(stu_id, stu_name, fee) values(1001,'jack',12000);
SQL executed ok (0.52 sec)
db2 => insert into experience.stud2(stu_id, stu_name, fee) values(null,'jack',12000);
ERROR 1048 (23000): Column 'stu_id' cannot be null[/sql]
In the above example, if not null constraint is applied on a column_name stu_id, then that column will accepts the values other than null values.
Description
By defining a column with primary key constraint then that primary key constraint won't acknowledge invalid and copy values and a table can contain only one primary key.
Primary key = Unique+Not null
Unique constraints can acknowledge null values where as primary key won't acknowledge null and copy values.
Syntax
create table <table_name> (<column_name1>data type(size) constraint type, <column_name2>data type(size),constraint(column_name1);
table_name => Any accurate table.
column_name => The operation that can be performed in the column of a table.
constraint_type =>constraint type will specify the type of constraint, that going to be executed in the database.
Examples
By viewing the below example, the concept of primary key constraint can be easily understand.
[sql]
db => create table prod13(prod_id int not null auto_increment,prod_name varchar(255)not null,primary key(prod_id));
DB20000I The SQL command completed successfully.
db => insert into experience.prod13 (prod_name) values ('mobile');
SQL executed ok (0.52 sec)
db => insert into experience.prod13 (prod_name) values ('laptop');
SQL executed ok (0.52 sec)
db => insert into experience.prod13 (prod_name) values ('system');
SQL executed ok (0.52 sec)
db => select * from experience.prod13;
prod_id prod_name
----------- -----------
1 mobile
2 laptop
3 system
3 record(s) selected.[/sql]
In the above example, the column prod_id is given not null auto increment constraint and at the same time in another column prod_id is given primary key constraint. In such case we no need to insert each an every value, just writing the prod_name it will automatically display the prod_id values.
Description
When a column is defined with foreign key constraint, then that foreign key constraint column will accept only the values of given primary key constraint column.
- A table can have stand out primary key constraint columns.
- A table can have any number of foreign key constraints columns.
- A table in which primary key constraint is defined is called parent key.
- A table in which foreign key constraint column is defined is called child table.
- A parent table can have any number of child tables, But a child table can have only one parent table.
- Without inserting parent record in parent table we cannot insert child record in child table.
- Without deleting child record from child table, we cannot delete parent record from parent table.
Syntax
Create table <table_name1>(<column_name1> datatype(size), <column_name2>datatype(size), primary key constraint(column_name1));
create table <table_name2>(<column_name1( datatype(size), <column_name2>datatype(size), datatype(size), FOREIGN KEY constraint(column_name1) REFERENCES(column_name1); );
table_name => Any accurate table.
column_name => The operation that can be perform on a column in the table.
constraint_type => constraint type will specify the type of constraint, that going to be executed in the database.
Examples
By viewing the below example, the concept of foreign key constraint can be easily understand.
[sql]
db => CREATE TABLE Student14(stud_id int AUTO_INCREMENT, name varchar(30)NOT NULL, age int NOT NULL, PRIMARY KEY(stud_id));
DB20000I The SQL command completed successfully.
db => insert into experience.Student14 (name,age) values ('mike',25);
SQL executed ok (0.52 sec)
db => insert into experience.Student14 (name,age) values ('kate',35);
SQL executed ok (0.52 sec)
db => select * from experience.Student14;
stud_id name age
-------- ------- -----
1 mike 25
2 kate 35
2 record(s) selected.
db => CREATE TABLE enrol15(rol_no int NOT NULL AUTO_INCREMENT, stud_id int NOT NULL, PRIMARY KEY(rol_no), FOREIGN KEY(stud_id)REFERENCES Student14(stud_id));
DB20000I The SQL command completed successfully.
db => INSERT INTO staff.enrol15 (stud_id) values (1);
SQL executed ok (0.52 sec)
db => INSERT INTO staff.enrol15 (stud_id) values (2);
SQL executed ok (0.52 sec)
db => INSERT INTO staff.enrol15 (stud_id) values (3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`employee`.`enrol15`, CONSTRAINT `enrol15_ibfk_1` FOREIGN KEY (`stud_id`)REFERENCES `student14` (`stud_id`))
db => select * from staff.enrol15;
rol_no stud_id
-------- ---------
1 1
2 2
2 record(s) selected.
[/sql]
In the above example, 2 tables have been has created i.e, experience. studentd14 and staff. enrol15. In experience. student14 table we assign primary key constraint to column stud_id, so no need to assign stud_id values just write name and age values, stud_id values will be taken automatically. In the table staff. eroll15 foreign key is assign to stud_id with reference to primary key. In such case in enroll15 table assigning stud_id values has to satisfy the stud_id of student14 values, if not it will terminate the values.
Description
By the SQL CHECK Constraint one can apply our own conditions on a column and if one want to define a column with check constraint then that check constraint column cannot accept the values that are not satisfying the condition.
Syntax
The syntax for check constraint is as follows
Create table <table_name>(<column_name1>datatype (size),<column_name2>datatype(size),<column_name3>datatype(size) constraint constraint name constraint type);
Table name => The accurate table in the database.
Column name => The operation that can be performed on a column.
Examples
The below example show the execution of SQL CHECK Constraint.
[c]
db => CREATE TABLE col.Remp(empno number(4),ename varchar2(20),salary number(7,2) constraint Remp_sal_ck check(sal db => insert into col. Remp(empno, ename, salary) values (1001,'Jack',8000);
SQL executed ok (0.52 sec)
db => insert into col. Remp(empno, ename, salary) values (1002,'Mack',null);
SQL executed ok (0.52 sec)
db => insert into col. Remp(empno, ename, salary) values (1003,'James',12000);
Error(5200):Salary values for empno(1003) is greater then the assigned value
db => select * from col.Remp;
empno ename salary
-------- ---------- --------
1 Jack 8000
2 mack null
2 rocord(s) selected.
[/c]
Here in the above example the column name salary is assigned a condition i.e, salary<10000. Then while assigning the value check condition will check the condition and if the salary is null then the check constraint will not check the condition, and if the salary greater then the assigned condition the oracle server execute the logic and null value will be placed in the table.
Key Points
- Constraints - Different number of conditions can be applied on a column with constraints.
- Unique Constraints - Unique constraint will not accept duplicate values.
- Not Null Constraint - Not Null constraint will not accept Null values.
- Primary Key Constraint - Primary key constraint won't accept null and copied values.
- Foreign Key Constraint - Foreign key values will accept only the primary key values.
- Check constraint - Assigning individual user conditions