SQL Server CONSTRAINTS With Examples
SQL Server CONSTRAINTS With Examples: Constraints are the principles enforced on the information columns of a table. This ensures the accuracy and dependability of the information within the info. SQL Server Constraints may well be either on a column level or a table level. The column level constraints are applied solely to 1 column, whereas the table level constraints are applied to the full table. Following are a number of the foremost unremarkably used constraints out there in SQL Server with examples.
Also Read: What is Sql, its applications, advantages and disadvantages
- NOT NULL constraint
- DEFAULT constraint
- UNIQUE constraint
- PRIMARY key
- FOREIGN key
- CHECK constraint
- INDEX
Also Read: Different Keys in DBMS
SQL Server CONSTRAINTS With Examples
-
NOT NULL constraint
Ensures that a column cannot have NULL worth. NOT NULL constraint makes certain that a column doesn’t hold a NULL value. Once we don’t give worth for a specific column whereas inserting a record into a table, it takes NULL worth by default. By specifying a NULL constraint, we are able to take care that a specific column(s) cannot have NULL values.
Example:
CREATE TABLE STUDENT (
ROLL_NO INT NOT NULL,
STU_NAME VARCHAR (20) NOT NULL,
STU_AGE INT NOT NULL,
STU_ADDRESS VARCHAR (600),
PRIMARY KEY (ROLL_NO)
);
Also Read: SQL Functions with Examples
-
DEFAULT Constraint
Provides a default worth for a column once none is specified. The DEFAULT constraint provides a default worth to a column once there’s no worth provided whereas inserting a record into a table.
Example:
CREATE TABLE STUDENT (
ROLL_NO INT NOT NULL,
STU_NAME VARCHAR (20) NOT NULL,
STU_AGE INT NOT NULL,
EXAM_FEE INT DEFAULT 5000,
STU_ADDRESS VARCHAR (20),
PRIMARY KEY (ROLL_NO)
);
-
UNIQUE Constraint
Ensures that each one values in a column are totally different. UNIQUE Constraint enforces a column or set of columns to possess distinctive values. If a column contains a distinctive constraint, it means specific column cannot have duplicate values in a very table.
Example:
CREATE TABLE STUDENT (
ROLL_NO INT NOT NULL,
STU_NAME VARCHAR (20) NOT NULL UNIQUE,
STU_AGE INT NOT NULL,
STU_ADDRESS VARCHAR (20) UNIQUE,
PRIMARY KEY (ROLL_NO)
);
-
PRIMARY Key
unambiguously identifies every row/record during an information table. Primary key unambiguously identifies every record in an exceeding table. It should have distinctive values and can’t contain nulls. Within the below example the ROLL_NO field is marked as primary key, meaning the ROLL_NO field cannot have duplicate and null values.
Example:
CREATE TABLE STUDENT (
ROLL_NO INT NOT NULL,
STU_NAME VARCHAR (20) NOT NULL,
STU_AGE INT NOT NULL,
STU_ADDRESS VARCHAR (20) UNIQUE,
PRIMARY KEY (ROLL_NO)
);
-
FOREIGN Key
unambiguously identifies a row/record in any of the given information tables.
-
CHECK Constraint
The CHECK constraint ensures that each one the values during a column satisfies sure conditions. This constraint is employed for specifying a range of values for a selected column of a table. Once this constraint is assailing a column, it ensures that specific column should have the worth falling within the specified range.
Example:
CREATE TABLE STUDENT (
ROLL_NO INT NOT NULL CHECK (ROLL_NO > 500),
STU_NAME VARCHAR (20) NOT NULL,
STU_AGE INT NOT NULL,
EXAM_FEE INT DEFAULT 5000,
STU_ADDRESS VARCHAR (600),
PRIMARY KEY (ROLL_NO)
);
-
INDEX
accustomed produce and retrieve information from the information terribly quick.
Constraints are specified once a table is formed with the create TABLE statement otherwise you will use the ALTER TABLE statement to make constraints even when the table is formed.
Dropping constraints:
Any constraint that you simply have outlined will be dropped using the ALTER TABLE command with the DROP CONSTRAINT possibility. As an example, to drop the first key constraint within the worker’s table, you’ll use the subsequent command. Some implementations might give shortcuts for dropping certain constraints. As an example, to drop the first key constraint for a table in Oracle, you’ll use the subsequent command. Some implementations enable you to disable constraints. Rather than for good dropping a constraint from the information, you will need to briefly disable the constraint and so modify it later.
Integrity constraints:
Integrity constraints are accustomed guarantee accuracy and consistency of the information in an exceedingly relational database. Information integrity is handled in an exceedingly relational database through the construct of denotative integrity. There are many sorts of integrity constraints that play a job in referential Integrity (RI). These constraints embody Primary Key, Foreign Key, Unique Constraints and alternative constraints that are mentioned above.
So it was all about SQL Server Constrains with examples, if you have any question then please comment below.
Leave a Reply