Constraints in SQL - Question in Data Science Interview


Constraints in SQL 


SQL constraints are nothing but rules in a table applied to maintain data. Constraints are used to maintain the quality of data and limit the type of data that can be moved into a table. Constraints  ensures the accuracy and reliability of the data in the table. If there is any violation of the constraint, the action is aborted.

1. PRIMARY KEY  CONSTRAINT


The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values. It is normal to just use running numbers, like 1, 2, 3, 4, 5, … as values in Primary Key column. It is a good idea to let the system handle this for you by specifying that the Primary Key should be set to identity(1,1). IDENTITY(1,1) means the first value will be 1 and then it will increment by 1.

Each table should have a primary key, and each table can have only ONE primary key.

If we take a closer look at the CUSTOMER table created earlier:

 

CREATE TABLE [CUSTOMER]
(
CustomerId int IDENTITY(1,1) PRIMARY KEY, 
CustomerNumber int NOT NULL UNIQUE,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
AreaCode int NULL, 
Address varchar(50) NULL,
Phone varchar(50) NULL,
)
GO


As you see we use the “Primary Key” keyword to specify that a column should be the Primary Key.


Primary Keys

Setting Primary Keys in the Designer Tools: If you use the Designer tools in SQL Server, you can easily set the primary Key in a table just by right-click and select “Set primary Key”.

Primary Key, Identity in SQL algaestudy , SQL, Microsoft SQL server, MYSQL Algae Services


2. FOREIGN KEY  CONSTRAINT


A FOREIGN KEY in one table points to a PRIMARY KEY in another table. 
Example:
Foreign Key, Identity in SQL algaestudy , SQL, Microsoft SQL server, MYSQL Algae Services

We will create a CREATE TABLE script for these tables:

SCHOOL:
CREATE TABLE SCHOOL 
SchoolId int IDENTITY(1,1) PRIMARY KEY,
SchoolName varchar(50) NOT NULL UNIQUE,
Description varchar(1000) NULL, 
Address varchar(50) NULL,
Phone varchar(50) NULL,
PostCode varchar(50) NULL,
PostAddress varchar(50) NULL,
)
GO

CLASS:

CREATE TABLE CLASS
(
ClassId int IDENTITY(1,1) PRIMARY KEY,
SchoolId int NOT NULL FOREIGN KEY REFERENCES SCHOOL (SchoolId),
ClassName varchar(50) NOT NULL UNIQUE,
Description varchar(1000) NULL,
)
GO 

 

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

The FOREIGN KEY constraint also prevents that invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

Setting Foreign Keys in the Designer Tools: 

If you want to use the designer, right-click on the column that you want to be the Foreign Key and select “Relationships…”


Foreign Key Identity in SQL algaestudy , SQL, Microsoft SQL server, MYSQL Algae Services

The following window pops up (Foreign Key Relationships):



Foreign Key Identity in SQL algaestudy , SQL, Microsoft SQL server, MYSQL Algae Services

Here you specify the primary Key Column in the Primary Key table and the Foreign Key Column in the Foreign Key table.

3. NOT NULL  CONSTRAINT


The NOT NULL constraint enforces a column to NOT accept NULL values.

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

If we take a closer look at the CUSTOMER table created earlier:

CREATE TABLE [CUSTOMER]

(
CustomerId int IDENTITY(1,1) PRIMARY KEY,
CustomerNumber int NOT NULL UNIQUE,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
AreaCode int NULL,
Address varchar(50) NULL,
Phone varchar(50) NULL,
)
GO


We see that “CustomerNumber”, “LastName” and “FirstName” is set to “NOT NULL”, this means these columns needs to contain data. While “AreaCode”, “Address” and “Phone” may be left empty, i.e, they don’t need to be filled out. Note! A primary key column cannot contain NULL values.


Setting NULL/NOT NULL in the Designer Tools

 In the Table Designer you can easily set which columns that should allow NULL or not: 

Foreign Key

4. UNIQUE  CONSTRAINT


The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it. 

Note! You can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

If we take a closer look at the CUSTOMER table created earlier:

CREATE TABLE [CUSTOMER]
CustomerId int IDENTITY(1,1) PRIMARY KEY,
CustomerNumber int NOT NULL UNIQUE,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
AreaCode int NULL, 
Address varchar(50) NULL,
Phone varchar(50) NULL, 
GO

We see that the “CustomerNumber” is set to UNIQUE, meaning each customer must have a unique Customer Number. Example:



Setting UNIQUE in the Designer Tools: 

If you want to use the designer, right-click on the column that you want to be UNIQUE and select “Indexes/Keys…”: 
Foreign Key

Then click “Add” and then set the “Is Unique” property to “Yes”:

Foreign Key



 

 

5. CHECK CONSTRAINT


The CHECK constraint is used to limit the value range that can be placed in a column. 

If you define a CHECK constraint on a single column it allows only certain values for this column. 

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

Example:

CREATE TABLE [CUSTOMER] 
(
CustomerId int IDENTITY(1,1) PRIMARY KEY,
CustomerNumber int NOT NULL UNIQUE CHECK(CustomerNumber>0),
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
AreaCode int NULL, 
Address varchar(50) NULL,
Phone varchar(50) NULL,
)
GO


In this case, when we try to insert a Customer Number less than zero we will get an error message. 

Setting CHECK constraints in the Designer Tools:

If you want to use the designer, right-click on the column where you want to set the constraints and select “Check Constraints…”: 

SQL in Check

Then click “Add” and then click “…” in order to open the Expression window:


Check in SQL

Check in SQL


6. DEFAULT CONSTRAINT


The DEFAULT constraint is used to insert a default value into a column.

The default value will be added to all new records, if no other value is specified.

Example:

CREATE TABLE [CUSTOMER]
(
CustomerId int IDENTITY(1,1) PRIMARY KEY,
CustomerNumber int NOT NULL UNIQUE,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
Country varchar(20) DEFAULT 'Norway',
AreaCode int NULL, Address varchar(50) NULL,
Phone varchar(50) NULL,
)
GO


Setting DEFAULT values in the Designer Tools: 

Select the column and go into the “Column Properties”: 


Default in SQL



No comments:
Write comments

Please do not enter spam links

Meet US

Services

More Services