HOW TO APPLY KEY CONSTRAINTS?
NOT NULL:
By default, a column can contain null value. So, by using NULL constraint you can get rid of this. It enforces the column to not accept null values.
Example: CREATE TABLE Employee(Emp_id INT NOT NULL, Name CHAR(20), Age INT, Address VARCHAR(40) NOT NULL, Salary DEC(7,2) NOT NULL, mobile no VARCHAR(10));
You can apply NOT NULL to all the columns in which you want to insert any value.
UNIQUE:
It is used to ensure that all values in a column are different. This is used to provide uniqueness to the column.
For Example: All the students in a class have different roll no. , mobile no. etc. This is inserted in the database with the help of UNIQUE Constraint.
SYNTAX: CREATE TABLE table_name( col_1 datatype NOT NULL UNIQUE, col_2 datatype,......col_n datatype);
You can also apply UNIQUE Constraint on multiple column by using the below syntax:
CREATE TABLE Employee(Emp_id INT NOT NULL, Name CHAR(20), Age INT, Address VARCHAR(40) NOT NULL, Salary DEC(7,2) NOT NULL, mobile no VARCHAR(10), CONSTRAINT UC_Emp UNIQUE(Emp_id, mobile no));
Here, UC mean Unique Constraint and UC_Emp is the name of the UNIQUE Constraint.
- UNIQUE can contain Null values.
PRIMARY KEY:
It uniquely identifies each record in a table. Primary key must contain UNIQUE value but it can not contain null values. There is only one primary key in the table which uniquely identifies the record of the table.
Example: CREATE TABLE Employee(Emp_id INT NOT NULL AUTO INCREMENT PRIMARY KEY, Name CHAR(20), Age INT, Address VARCHAR(40) NOT NULL, Salary DEC(7,2) NOT NULL, mobile no VARCHAR(10));
AUTO INCREMENT automatically increments the value.
- If a single column cannot be able to uniquely identify a record then we can also make multiple column as primary key using the syntax given below:
CREATE TABLE Employee(Emp_id INT NOT NULL, Name CHAR(20), Age INT, Address VARCHAR(40) NOT NULL, Salary DEC(7,2) NOT NULL, mobile no VARCHAR(10), CONSTRAINT PK_Emp PRIMARY KEY(Emp_id, mobile no));
FOREIGN KEY:
If you want to link two tables then it would be done with the help of FOREIGN KEY.
Example:
Employee table:
Emp_id Name Age Address Salary Dept_id.
1 A 22 A1 S1 10
2 B 23 A2 S2 11
3 C 20 A3 S3 12
4 D 21 A4 S4 10
Department table:
Dept_id Dept_loc Dept_name
10 L1 N1
11 L2 N2
12 L3 N3
- Emp_id in Employee table is PRIMARY KEY and Dept_id in Employee table is Alternate key.
- Dept_id in Department table is foreign key.
Example: CREATE TABLE Department { Dept_id INT NOT NULL , Dept_loc CHAR(40) NOT NULL, Dept_name CHAR(10), PRIMARY KEY(Dept_id), CONSTRAINT FK_empdep FOREIGN KEY(Dept_id) REFERENCES Employee(Emp_id) };
CHECK:
CHECK is used to limit the value range of the column.
Example : The age of employee must be greater than 18, if not, then the database deny. This is done by using CHECK during the time of creation of the table.
Example: CREATE TABLE Employee(Emp_id INT NOT NULL, Name CHAR(20), Age INT CHECK(Age>18), Address VARCHAR(40) NOT NULL, Salary DEC(7,2) NOT NULL, mobile no VARCHAR(10));
Previous Next
No comments: