- 5 years ago
- Zaid Bin Khalid
- 2,993 Views
-
5
A SQL Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with an ALTER TABLE statement.
The following constraints are commonly used in SQL.
- NOT NULL
- PRIMARY KEY
- UNIQUE
- DEFAULT Constraint
- FOREIGN KEY
- CHECK
Now, discuss each of these constraints in detail.
Not Null Constraint
The Not Null constraint is used to specify that the column will
not accept the NULL Values.
The mention below SQL example will create a table with named persons with four other columns, out of which three columns, like id, name, and phone do not accept NULL values.
CREATE TABLE persons (
id INT NOT NULL,
name VARCHAR(30) NOT NULL,
birth_date DATE,
phone VARCHAR(15) NOT NULL
);CREATE TABLE persons (
PRIMARY KEY Constraint
The PRIMARY KEY constraint finds out the set or column whose values uniquely identify a row in a table. Primary Key Constraint not allowed two rows in a table that have the same primary key value. Moreover, you will not enter the NULL value in a primary key column.
CREATE TABLE persons (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
birth_date DATE,
phone VARCHAR(15) NOT NULL
);
UNIQUE Constraint
The UNIQUE constraint not allowed one or more columns that contain unique values in a table.
However, both unique and primary key constraint enables forced uniqueness. Hence to enforce the uniqueness of the column or combination of columns, you must use Unique constraint.
The mention below SQL statement creates a table with persons and specifies the phone column as unique. It means that it does not allow duplicate values.
CREATE TABLE persons (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
birth_date DATE,
phone VARCHAR(15) NOT NULL UNIQUE
);
DEFAULT Constraint
Since the column default has inserted some value in the column that
would be inserted by database engine especially during Insert statement does not
assign any particular value.
The mention below SQL Example will create a default Constraint in the country column.
CREATE TABLE persons (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
birth_date DATE,
phone VARCHAR(15) NOT NULL UNIQUE,
country VARCHAR(30) NOT NULL DEFAULT 'Australia'
);
FOREIGN KEY Constraint
A foreign key is a column or combination of columns that is used to make a relationship between the data into two tables.
The mention below MySQL Example will create a relation with data and two tables by using FOREIGN KEY. When you create a table as follows. The following command creates a foreign key on the dept_id column of the employee’s table that references the dept_id column of the department’s table.
CREATE TABLE employees (
emp_id INT NOT NULL PRIMARY KEY,
emp_name VARCHAR(55) NOT NULL,
hire_date DATE NOT NULL,
salary INT,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
CHECK Constraint
The Check constraint will not allow more values that can be
placed in a column.
Let’s suppose, the range of values in a column entered limited by using check Constraint which only allows them to show 3,000 to 10,000 and prevent the salaries being entered by the regular salary ranges. Here is an example.
CREATE TABLE employees (
emp_id INT NOT NULL PRIMARY KEY,
emp_name VARCHAR(55) NOT NULL,
hire_date DATE NOT NULL,
salary INT NOT NULL CHECK (salary >= 3000 AND salary <= 10000),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
- 5 years ago
- Zaid Bin Khalid
- 2,993 Views
-
5