• Latest
  • Trending
Exploring Constraints in SQL Server

Exploring Constraints in SQL Server

July 4, 2022
Co-Creation Hub’s edtech accelerator puts $15M towards African startups

Co-Creation Hub’s edtech accelerator puts $15M towards African startups

February 20, 2023
Data Leak Hits Thousands of NHS Workers

Data Leak Hits Thousands of NHS Workers

February 20, 2023
EU Cybersecurity Agency Warns Against Chinese APTs

EU Cybersecurity Agency Warns Against Chinese APTs

February 20, 2023
How Your Storage System Will Still Be Viable in 5 Years’ Time?

How Your Storage System Will Still Be Viable in 5 Years’ Time?

February 20, 2023
The Broken Promises From Cybersecurity Vendors

Cloud Infrastructure Used By WIP26 For Espionage Attacks on Telcos

February 20, 2023
Instagram and Facebook to get paid-for verification

Instagram and Facebook to get paid-for verification

February 20, 2023
YouTube CEO Susan Wojcicki steps down after nine years

YouTube CEO Susan Wojcicki steps down after nine years

February 20, 2023
Inaugural AfCFTA Conference on Women and Youth in Trade

Inaugural AfCFTA Conference on Women and Youth in Trade

September 6, 2022
Instagram fined €405m over children’s data privacy

Instagram fined €405m over children’s data privacy

September 6, 2022
8 Most Common Causes of a Data Breach

5.7bn data entries found exposed on Chinese VPN

August 18, 2022
Fibre optic interconnection linking Cameroon and Congo now operational

Fibre optic interconnection linking Cameroon and Congo now operational

July 15, 2022
Ericsson and MTN Rwandacell Discuss their Long-Term Partnership

Ericsson and MTN Rwandacell Discuss their Long-Term Partnership

July 15, 2022
  • Consumer Watch
  • Kids Page
  • Directory
  • Events
  • Reviews
Wednesday, 29 March, 2023
  • Login
itechnewsonline.com
  • Home
  • Tech
  • Africa Tech
  • InfoSEC
  • Data Science
  • Data Storage
  • Business
  • Opinion
Subscription
Advertise
No Result
View All Result
itechnewsonline.com
No Result
View All Result

Exploring Constraints in SQL Server

by ITECHNEWS
July 4, 2022
in Data Science, Leading Stories
0 0
0
Exploring Constraints in SQL Server

Introduction on SQL Server

SQL Server is an RDBMS developed and maintained by Microsoft to support a wide variety of transaction processing, data storage and retrieval, etc. Constraints are used in SQL Server to prevent the insertion of unwanted data in the tables. In this article, we will study the constraints and different types of constraints available in SQL Server with the help of examples.

What are Constraints?

Constraints in SQL Server are predefined rules that you can enforce on a column or more than one column or table so that the undesirable does not get inserted into the tables. Constraints help to ensure data integrity, data accuracy, and data reliability of the values stored in the tables.

YOU MAY ALSO LIKE

Co-Creation Hub’s edtech accelerator puts $15M towards African startups

Data Leak Hits Thousands of NHS Workers

If you perform an SQL operation in SQL Server that meets specified constraint rule criteria, then the required operation gets performed successfully. However, in case of constraint violation by data, the SQL operation will be aborted with an error message.

For example, if you want to create a student table where the age of students must be greater than 18, you can use the CHECK Constraint as shown in the below query:

CREATE TABLE STUDENT(std_id INT PRIMARY KEY, std_name varchar(50),age INT CHECK (Age>18));
What are Constraints 1

Now, if you try to run the below query you will not be able to insert the data due to the CHECK constraint.

INSERT INTO STUDENT VALUES (1, 'Rahul',12)

It gives us the below output.

What are Constraints Image 2

Now, we’ll study about the different types of constraints in SQL Server.

Types of Constraints

1. NOT NULL Constraint

By default, columns in tables are allowed to store NULL values in SQL Server. The NOT NULL constraint in a column ensures that NULL values are not accepted as an input for that column. It means that if NOT NULL constraint is specified on a column, then users cannot insert a new record or update the already existing record without adding a value to that column.

For example, if you want to create a student table where the data in any column must not be NULL, you can use the NOT NULL Constraint as shown in the below query:

CREATE TABLE STUDENT(std_id INT NOT NULL, std_name varchar(50) NOT NULL, age INT NOT NULL);
NOT NULL Constraint| SQL Server

Now, if you try to run the below query you will not be able to insert the data due to NOT NULL constraint.

INSERT INTO STUDENT(std_id, age) VALUES (1,24)

It gives us the below output.

NOT NULL Constraint Image 2| SQL Server

2. CHECK Constraint

The CHECK constraint specifies a valid range of input values that can be inserted into the specified table columns in SQL Server. When any SQL statement is provided to insert or update a value, the statement gets executed successfully only if the CHECK constraint is satisfied.

For example, if you want to create a student table where only the student data having valid zip codes must be stored in the table, you can use the CHECK Constraint as shown in the below query:

CREATE TABLE STUDENT(
std_id INT PRIMARY KEY, 
std_name varchar(50) NOT NULL, 
res_address varchar(50) NOT NULL,
zip_code INT CHECK (zip_code LIKE REPLICATE ('[0-9]', 5)));
CHECK Constraint

Now, if you try to run the below query you will not be able to insert the data due to CHECK constraint.

INSERT INTO STUDENT VALUES (1, 'Rahul','49, JAY Nagar',900234);

It gives us the below output.

CHECK Constraint 2

3. UNIQUE Constraint

The UNIQUE constraint ensures that you do not have duplicate values in the specified table columns in SQL Server. Whenever UNIQUE constraints are defined on the table, then the index is automatically created by SQL Server. Only one NULL value is acceptable in a column having a UNIQUE constraint.

For example, if you want to create a student table where only the student data having unique names must be stored in the table, you can use the UNIQUE Constraint as shown in the below query:

CREATE TABLE STUDENT(
std_id INT PRIMARY KEY, 
std_name varchar(50) UNIQUE, 
res_address varchar(50) NOT NULL,
);
UNIQUE Constraint| SQL Server

Now, if you try to run the below query you will not be able to insert the data when std_id is 4, due to UNIQUE constraint.

INSERT INTO STUDENT VALUES (3, 'Raj','49, JAY Nagar');
INSERT INTO STUDENT VALUES (4, 'Raj','12, Prem Nagar');

It gives us the below output.

 

UNIQUE Constraint 2

4. DEFAULT Constraint

The DEFAULT constraint is used to provide the default value for a column or more than one column in SQL tables. If we do not specify a value in the INSERT statement for the column with the DEFAULT constraint, SQL Server will insert its default assigned value in the table.

For example, if you want to create a student table where by default year_of_birth is taken as 1998 while inserting new student data, you can use the DEFAULT Constraint as shown in the below query:

CREATE TABLE STUDENT(
std_id INT PRIMARY KEY IDENTITY(1,1), 
std_name varchar(50), 
year_of_birth bigint DEFAULT 1998,
);
DEFAULT Constraint| SQL Server

Now, if you try to run the below year_of_birth is automatically taken as 1998 due to the DEFAULT constraint.

INSERT INTO STUDENT(std_name) VALUES ('Ram');
DEFAULT Constraint 2| SQL Server

It gives us the below output.

5. PRIMARY KEY Constraint

The PRIMARY KEY constraint is used to uniquely identify each row in a table in SQL Server. When we define a PRIMARY KEY constraint on a table, SQL Server automatically creates clustered index for that table. This constraint is used to ensure data integrity in a table.

For example, if you want to create a student table with std_id as PPRIMARY KEY then you can use the below query:

CREATE TABLE STUDENT(
std_id INT PRIMARY KEY IDENTITY(1,1), 
std_name varchar(50), 
year_of_birth bigint DEFAULT 1998,
);

 

 PRIMARY KEY Constraint| SQL Server

6. FOREIGN KEY Constraint

To create relationship between two tables in SQL Server, FOREIGN KEY constraint is used. This constraint is used for enforcing referential integrity in SQL Server.

The column specified as foreign key in one table should have a corresponding entry in the referenced table primary key column. You cannot insert a value in a column with FOREIGN KEY constraint in the child table without inserting it in the parent table first.

For example, if you want to create a student table with std_id as PPRIMARY KEY and other table grades having std_id as FOREIGN KEY, then you can use the below queries:

CREATE TABLE STUDENT(
std_id INT PRIMARY KEY IDENTITY(1,1), 
std_name varchar(50), 
year_of_birth bigint DEFAULT 1998,
);
CREATE TABLE grades(
  std_id int not null FOREIGN KEY references STUDENT (std_id),
  grade varchar(3),
 primary key(std_id)
);

 

FOREIGN KEY Constraint| SQL Server
FOREIGN KEY Constraint 2

Conclusion

Constraints are predefined rules to be applied at column-level or table-level for restricting values that are allowed in the columns.

To summarize, the following were the major takeaways about the SQL Server Constraints:

  1. We learned about how we can maintain data accuracy and data integrity using constraints.
  2. We also got an understanding about what are the various types of constraints available in SQL Server.
  3. We have seen how we can use CHECK constraint to specify a valid range of input value, to uniquely identify table rows using PRIMARY KEY constraint, etc.
  4. Apart from this, we also learned about how to specify default value, not allow nulls in column values, etc.
Source: Chaitanya Shah
Via: analyticsvidhya
Tags: Exploring Constraints in SQL Server
ShareTweetShare
Plugin Install : Subscribe Push Notification need OneSignal plugin to be installed.

Search

No Result
View All Result

Recent News

Co-Creation Hub’s edtech accelerator puts $15M towards African startups

Co-Creation Hub’s edtech accelerator puts $15M towards African startups

February 20, 2023
Data Leak Hits Thousands of NHS Workers

Data Leak Hits Thousands of NHS Workers

February 20, 2023
EU Cybersecurity Agency Warns Against Chinese APTs

EU Cybersecurity Agency Warns Against Chinese APTs

February 20, 2023

About What We Do

itechnewsonline.com

We bring you the best Premium Tech News.

Recent News With Image

Co-Creation Hub’s edtech accelerator puts $15M towards African startups

Co-Creation Hub’s edtech accelerator puts $15M towards African startups

February 20, 2023
Data Leak Hits Thousands of NHS Workers

Data Leak Hits Thousands of NHS Workers

February 20, 2023

Recent News

  • Co-Creation Hub’s edtech accelerator puts $15M towards African startups February 20, 2023
  • Data Leak Hits Thousands of NHS Workers February 20, 2023
  • EU Cybersecurity Agency Warns Against Chinese APTs February 20, 2023
  • How Your Storage System Will Still Be Viable in 5 Years’ Time? February 20, 2023
  • Home
  • InfoSec
  • Opinion
  • Africa Tech
  • Data Storage

© 2021-2022 iTechNewsOnline.Com - Powered by BackUPDataSystems

No Result
View All Result
  • Home
  • Tech
  • Africa Tech
  • InfoSEC
  • Data Science
  • Data Storage
  • Business
  • Opinion

© 2021-2022 iTechNewsOnline.Com - Powered by BackUPDataSystems

Welcome Back!

Login to your account below

Forgotten Password?

Retrieve your password

Please enter your username or email address to reset your password.

Log In
Go to mobile version