Hello everyone, I am going to share the brief about SQL constraints.
CREATE TABLE Employee
Example
CREATE TABLE Employee
CREATE TABLE Employee
Following is the example to DROP a UNIQUE Constraint
Following is the example to DROP a CHECK Constraint
Following is the example to apply PRIMARY KEY Constraint on ALTER TABLE
Following is the example to DROP a PRIMARY KEY Constraint
SQL constraints
To
specify set of rules to limit the type of data in a table we used
SQL constraints.
Following
are the most used constraints in SQL
- NOTNULL
- UNIQUE
- PRIMARYKEY
- FOREIGNKEY
- CHECK
- DEFAULT
- INDEX
1:- NOT NULL
This constraint is used to void the acceptance of
null value in the column of the table. by default a column has NULL constraints
means that columns can hold the null value.
Example
Following is the example to apply NOT NULL
Constraint on CREATE TABLE
CREATE TABLE Employee
(
Id int
NOT NULL CHECK (Id>0),
Name varchar(255) NOT NULL,
Address varchar(255),
Age int,
)
you can also apply NOT NULL constraint on existing table as below query
ALTER TABLE Employee
)
you can also apply NOT NULL constraint on existing table as below query
ALTER TABLE Employee
MODIFY age INT NOT NULL;
2:- UNIQUE
UNIQUE constraint is used for the uniqueness of the value for the column in the table. UNIQUE constraint can have multiple in a table.
2:- UNIQUE
UNIQUE constraint is used for the uniqueness of the value for the column in the table. UNIQUE constraint can have multiple in a table.
Example
Following is the example to apply UNIQUE Constraint
on single column in CREATE TABLE
CREATE TABLE Employee
(
Id int
NOT NULL UNIQUE,
Name varchar(255) NOT NULL,
Address varchar(255),
Age int,
)
)
Following is the example to apply UNIQUE Constraint
on multiple column in CREATE TABLE
CREATE TABLE Employee
(
Id int
NOT NULL UNIQUE,
MobileNO int NULL UNIQUE,
Name varchar(255) NOT NULL,
Address varchar(255),
Age int,
)
Following is the example to apply UNIQUE Constraint on ALTER TABLE
)
Following is the example to apply UNIQUE Constraint on ALTER TABLE
ALTER TABLE Employee
ADD CONSTRAINT chk_ Id UNIQUE
Following is the example to DROP a UNIQUE Constraint
ALTER TABLE Employee
DROP CONSTRAINT chk_ Id
3:- Check
CHECK constraints CHECK constraint is used to limiting the values that are accepted by one or more columns.
CHECK constraints CHECK constraint is used to limiting the values that are accepted by one or more columns.
Following is the example to apply SQL CHECK
Constraint on CREATE TABLE
CREATE TABLE Employee
CREATE TABLE Employee
(
Id int
NOT NULL CHECK (Id>0),
Name varchar(255) NOT NULL,
Address varchar(255),
Age int,
)
Following is the example to apply SQL CHECK Constraint on ALTER TABLE
)
Following is the example to apply SQL CHECK Constraint on ALTER TABLE
ALTER TABLE Employee
ADD CONSTRAINT chk_Employee CHECK
(Id>0
AND age> 20)
Following is the example to DROP a CHECK Constraint
ALTER TABLE Employee
DROP CONSTRAINT chk_Employee
4:- PRIMARY KEY
Primary key is
cloumn in a table that uniquely identify the earch rows.
A table have can
only one primary key and primary can not be hold the null value.
Following is the example to apply PRIMARY KEY
Constraint on CREATE TABLE
CREATE TABLE Employee
CREATE TABLE Employee
(
Id INT
NOT NULL,
Name varchar(255) NOT NULL,
Address varchar(255),
Age INT,
CONSTRAINT
pk_EmployeeID PRIMARY KEY
(Id)
)
Following is the example to apply PRIMARY KEY Constraint on ALTER TABLE
ALTER TABLE Employee
ADD PRIMARY KEY (Id)
Following is the example to DROP a PRIMARY KEY Constraint
ALTER TABLE Employee
DROP CONSTRAINT pk_EmployeeID
5:- FOREIGN KEY
FOREIGN is column in a table that is the primary key
column in another table. a table can
have multiple foreign column in a table .
It can hold multiple null values.
Example: Below are the EMPLOYEE
and EMPSALARY table.
EMPLOYEE table:
CREATE TABLE EMPLOYEE(
ID
INT NOT
NULL,
NAME VARCHAR (20) NOT NULL,
AGE
INT NOT
NULL,
ADDRESS CHAR (25) ,
SALARY
DECIMAL (18, 2),
CONSTRAINT
pk_EmployeeID PRIMARY KEY
(ID)
);
EMPSALARY table:
CREATE TABLE EMPSALARY (
ID INT NOT NULL,
DATE DATETIME,
EMPLOYEE_ID INT
references EMPLOYEE(ID),
AMOUNT double,
CONSTRAINT
pk_ EMPSALARYID PRIMARY KEY (ID)
);
in above EMPSALARY table EMPLOYEE_ID is the foreign
key that refrenece to the primary key in the EMPLOYEE table.
No comments:
Post a comment