Login

Username:

Password: 

Join us Now |  Forgot Password? | Forgot UserName?

SQL

Learn step by step



Sql Constraints

SQL constraints allow us to enforce rules in our database. These rules may affect business logic, database integrity and/or table structure. Each one plays an important role in our database architecture. The 6 types of constraints supported by Microsoft SQL Server include:

  • UNIQUE constraints
  • CHECK constraints
  • DEFAULT constraints
  • NOT NULL constraints
  • PRIMARY KEY constraints
  • FOREIGN KEY constraints

UNIQUE constraints

UNIQUE constraints allow SQL Server administrators to specify that column may not contain duplicate values. When we create a new UNIQUE constraint, the SQL Server checks the column in question to determine whether it contains any duplicate values. If the table contains any preexisting duplicates, the UNIQUE constraint creation command fails. Similarly, once we have a UNIQUE constraint on a column, attempts to add or modify the data that would cause duplicates to exist also fail.


CREATE TABLE EMPLOYEE(
ID INT NOT NULL,
NAME VARCHAR (200) NOT NULL,
AGE INT NOT NULL UNIQUE ,
ADDRESS CHAR (25) ,
SALARY DECIMAL (25, 2),
PRIMARY KEY (ID)
);
ADD UNIQUE constraints

ALTER TABLE EMPLOYEE
MODIFY AGE INT NOT NULL UNIQUE;
CHECK constraints

CHECK constraints allow us to limit the types of data that users may insert in a database. They go beyond data types and allow us to define the specific values that may be included in a column.


CREATE TABLE EMPLOYEE(
ID INT NOT NULL,
NAME VARCHAR (200) NOT NULL,
AGE INT NOT NULL CHECK (AGE >= 18) ,
ADDRESS CHAR (25) ,
SALARY DECIMAL (25, 2),
PRIMARY KEY (ID)
);
ADD CHECK constraints

ALTER TABLE EMPLOYEE
MODIFY AGE INT NOT NULL CHECK (AGE >= 18 );
DEFAULT constraints

DEFAULT constraints allow us to specify a value that the database will use to populate fields that are left blank in the input source. They are a replacement for the use of NULL values that provide a great way to predefine common data elements.


CREATE TABLE EMPLOYEE(
ID INT NOT NULL,
NAME VARCHAR (200) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (25, 2) DEFAULT 10000.00,
PRIMARY KEY (ID)
);
ADD DEFAULT constraints

ALTER TABLE EMPLOYEE
MODIFY SALARY DECIMAL (25, 2) DEFAULT 10000.00;
NOT NULL constraints

NOT NULL constraints allow us to specify that a column may not contain NULL values. When we create a new NOT NULL constraint on a database column, the SQL Server checks the column's current contents for any NULL values. If the column currently contains any NULL values, the constraint creation fails. Otherwise, SQL Server adds NOT NULL constraint and any future INSERT or UPDATE commands that would cause the existence of NULL value fail.


CREATE TABLE EMPLOYEE(
ID INT NOT NULL,
NAME VARCHAR (200) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (25, 2),
PRIMARY KEY (ID)
);
ADD NOT NULL constraints

ALTER TABLE CUSTOMER
MODIFY SALARY DECIMAL (25, 2) NOT NULL;
PRIMARY KEY constraints

PRIMARY KEY constraints specify fields that uniquely identify each record in the table. It can be normal attribute that is guaranteed/compulsory to be unique (such as Social Security Number in a table with no more than one record per person) or it can be generated by the DBMS (such as a GUID, or globally unique identifier, in Microsoft SQL Server). A Primary keys may consist of a single attribute or multiple attributes in combination.


CREATE TABLE EMPLOYEE(
ID INT NOT NULL,
NAME VARCHAR (200) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (25, 2),
PRIMARY KEY (ID)
);

CREATE TABLE EMPLOYEE(
ID INT NOT NULL,
NAME VARCHAR (200) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (25, 2),
PRIMARY KEY (ID, NAME)
);
ADD PRIMARY KEY constraints

ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID);
FOREIGN KEY constraints

FOREIGN KEY constraints are fields in a relational database table that match the primary key column of another table. A Foreign keys can be used to cross-reference different tables


CREATE TABLE EMPLOYEE(
ID INT NOT NULL,
NAME VARCHAR (200) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (25, 2),
PRIMARY KEY (ID)
);

CREATE TABLE DEPARTMENT(
ID INT NOT NULL,
NAME VARCHAR (200) NOT NULL,
EMPLOYEE_ID INT references EMPLOYEE(ID),
PRIMARY KEY (ID)
);
ADD FOREIGN KEY constraints

ALTER TABLE DEPARTMENT ADD FOREIGN KEY (Employee_ID) REFERENCES EMPLOYEE(ID);


Related Videos