Diwali Deal : Flat 20% off + 2 free self-paced courses + $200 Voucher  - SCHEDULE CALL

sddsfsf

How to Check Constraint in SQL & SQL Default Constraint

 

While working with a database, you might sometimes face two scenarios. In one scenario, you have a field that cannot accept null values. At the same time, you want to make an arrangement such that if you miss out on entering a value in that field the system will automatically take care of it. Or otherwise, it will put a value you set previously on your behalf. The other scenario is that you want to find out or validate the data that is being entered into the system. SQL Server allows both of these to happen using the Default and the Check constraint. Over the following few paragraphs, we will learn about check and default constraints in SQL.

What are SQL Constraints

Rules for a table's data can be specified with the help of SQL constraints.The type of data that can be entered into a table can be restricted with constraints. This guarantees the reliability and accuracy of the data in the table. The action is aborted if there is a violation between the constraint and the data action. Column-level or table-level constraints are possible. Constraints at the table level apply to the entire table, whereas those at the column level apply to a single column.

In SQL, the following constraints are frequently used:

  • NOT NULL:- Ensures that a column cannot contain a NULL value;
  • UNIQUE:- Ensures that each column's values are distinct;
  • PRIMARY KEY:- Enables a combination of UNIQUE and NOT NULL. Identifies each row in a table in a unique way
  • FOREIGN KEY:- Prevents actions that would break links between tables
  • CHECK:- Ensures that the values in a column meet a particular condition
  • DEFAULT:- Sets a default value for a column if none is specified
  • CREATE INDEX:- Used to create and retrieve data from the database quickly

Check Constraint in SQL

Using the MS SQL CHECK constraint, a column's value range can be restricted.A column with a SQL Server CHECK constraint on it will only allow specific values.A CHECK constraint can limit the value specificrtain columns based on values in other columns in the row if it is applied to a table.We will then learn how to use a DBMS check constraint and learn to check constraints in SQL.

Advantages of MS SQL Check Constraint

The following are some of the benefits of SQL Server Check Constraints:

  • The capacity to enforce rules in the database without necessarily requiring additional application logic.
  • The application cannot circumvent the check.
  • Check constraints to provide data integrity. The rule cannot be circumvented during dynamic SQL or ad hoc processing.

Disadvantages of Check Constraint in DBMS

The disadvantages of MS SQL Check Constraints are as follows

CHECK constraints reject FALSE-valued values. A constraint may be overridden by the presence of null values in expressions because they evaluate to UNKNOWN.

SQL CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is created. The CHECK constraint ensures that the age of a person must be 18 or older: The SQL Query is as follows

CREATE TABLE Persons_check (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int CHECK (Age>=18)

);

The output looks like below

To allow the naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Persons_Check1 (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

City varchar(255),

CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')

);

The output is as below

SQL Server Check Constraint on Alter Table

To create a CHECK constraint on the "Age" column when the table is already created, use the following SQL:

ALTER TABLE Persons
ADD CHECK (Age>=18);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');
Drop a SQL Server Check Constraint
To drop a CHECK constraint, use the following SQL:
ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;

The DEFAULT constraint is used to set a default value for a column.The default value will be added to all new records if no other value is specified.

Uses of SQL Default Constraint

The uses of SQL Default Constraint are

  • Prevent bad data from being entered into tables of interest are some of the uses of SQL Default Constraint.
  • Require database-level enforcement of business logic.
  • Detailed documentation of crucial database rules
  • Ensure the integrity of relationships between any number of tables.
  • Make the database work better.
  • Reinforce individuality.

Advantages of SQL Default Constraint

A column's default value can be set with the SQL DEFAULT constraint. If no other value is specified, all new records will have the default value added to them.

Disadvantages of SQL Default Constraint

Disadvantages of SQL Default Constraint are as follows

  • Even if we wanted to, we would not be able to insert a null value into a field in a table if a default constraint was applied to it.

Create Default Constraint in SQL Server

The following SQL sets a DEFAULT value for the "City" column when the "Persons" table is created: The SQL query is as follows

CREATE TABLE Persons_default (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);

The output looks like below

The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE(: The SQL query looks like this below

CREATE TABLE Orders (
ID int NOT NULL,
OrderNumber int NOT NULL,
OrderDate date DEFAULT GETDATE()
);

The output looks like below

SQL Default Constraint on Alter Table

To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:

ALTER TABLE [dbo].[Persons_default]
ADD CONSTRAINT df_City
DEFAULT 'Sandnes' FOR City;
DROP a SQL DEFAULT Constraint
To drop a DEFAULT constraint, use the following SQL:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;

Conclusion

Over the last few paragraphs, we learned about Check and Default Constraints in SQL. We learned their uses, advantages, and disadvantages. Hope this becomes an introductory text for people who want to learn more about these two constraints and if you wander what MySQL check constraint is can do that too.

cta14 icon

SQL Testing Training

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

Trending Courses

Cyber Security icon

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security icon1

Upcoming Class

-0 day 05 Nov 2024

QA icon

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA icon1

Upcoming Class

7 days 12 Nov 2024

Salesforce icon

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce icon1

Upcoming Class

6 days 11 Nov 2024

Business Analyst icon

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst icon1

Upcoming Class

-0 day 05 Nov 2024

MS SQL Server icon

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server icon1

Upcoming Class

10 days 15 Nov 2024

Data Science icon

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science icon1

Upcoming Class

3 days 08 Nov 2024

DevOps icon

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps icon1

Upcoming Class

2 days 07 Nov 2024

Hadoop icon

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop icon1

Upcoming Class

3 days 08 Nov 2024

Python icon

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python icon1

Upcoming Class

4 days 09 Nov 2024

Artificial Intelligence icon

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence icon1

Upcoming Class

3 days 08 Nov 2024

Machine Learning icon

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning icon1

Upcoming Class

10 days 15 Nov 2024

 Tableau icon

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau icon1

Upcoming Class

3 days 08 Nov 2024