Saturday, August 28, 2021

What are SQL constraints? Explain with examples.

 SQL constraints are a set of rules implemented on tables in relational databases to dictate what data can be inserted, updated or deleted in its tables. This is done to ensure the accuracy and the reliability of information stored in the table. Constraints enforce limits to the data or type of data that can be inserted/updated/deleted from a table. The purpose of constraints is to maintain the data integrity during an update/delete/insert into a table.  Once the constraint is placed, if any operation in the database does not follow the rules specified by the constraint, the particular operation is aborted. In this article, we will go through what SQL constraints are, what are the different kinds of SQL constraints are commonly used and how to implement and get rid of them. First, however, we will take a brief look into why they are needed.

Need for SQL Constraints

Before we get into the details of what SQL constraints are, let’s take a look at why they are necessary. In order to arrive at that answer, we need to first understand the way in which information is stored in relational databases and why it is of primal importance to ensure that frameworks are in place governing what information can be entered or altered in a way that that information in the tables are not corrupted.

At a high level, information in relational databases is segregated into two types, facts and dimensions. Fact tables contain information relating to mathematical information, as a thumb rule, any information on which mathematical operations can be applied, are facts. Sales numbers, costs and revenues are examples of facts. Whereas information like name, date of birth, geographic location etc are examples of dimensional information. In an optimally normalised database, facts and dimensions are stored in separate tables. In order to facilitate rapid extraction of information, relations are built between particular columns in the fact and dimension tables.  This is where relational databases get their name from and it is on the basis of these relationships that multiple tables are joined and the data in the tables extracted. Fact and dimension tables are organized in particular structures known as schemas.  Star schema and snowflake schema are popular ways of organising this information.

Star Schema 

The below diagram is an example of a star schema where a central fact table is associated with multiple dimensional tables via relations between columns in the fact and dimension tables. 

Difference Between Star and Snowflake Schema (with Example, Diagram and Comparison Chart) - Tech Differences

Snowflake Schema 

In snowflake schemas, the central fact table is associated with multiple dimension tables which in their turn are then associated with multiple other dimension tables.

Difference Between Star and Snowflake Schema (with Example, Diagram and Comparison Chart) - Tech Differences

Generally, the association between fact and dimension tables happen via something called a PRIMARY KEY – FOREIGN KEY relationship. A PRIMARY KEY is a unique identifier of a particular row of information in a fact table, which connects to the FOREIGN KEY in a dimension table to identify dimension information for the same row in the dimension table. Since PRIMARY KEYS are unique identifiers for particular sets of information, for example, a particular sale in the Sales table, it is imperative that the PRIMARY KEY column in the table do not contain duplicates, that is, multiple sales cannot have the same unique ID. Also, values in the PRIMARY KEY column cannot be blank.  Otherwise, we wouldn’t be able to identify that particular Sale. So generally, there are rules placed on PRIMARY KEY columns in fact tables that ensure the column doesn’t contain duplicates and no row in the column is blank. This is a classic example of a SQL constraint!  Relational databases are quite fragile in the way that very small changes in the information stored in tables can upset the proper functioning of data storage and data extraction from the database.  SQL constraints are placed to ensure that insertions, updates, and deletion of information in a database occur in a way that the smooth functioning of the database is maintained.

Below is a list of common reasons why SQL constraints are applied on databases:

  • Prevent bad data from being entered into tables of interest.
  • Enforce business logic at the database level.
  • Documentation of important database rules.
  • Enforce relational integrity between any number of tables.
  • Improve database performance.
  • Enforce uniqueness.

Types of SQL Constraints:

SQL constraints can be at a column or a table level. Column level constraints apply to specific columns in a table and do not specify a column name except the check constraints. They refer to the column that they follow. The names are specified by the Table-level constraints of the columns to which they apply.

Following is a list of the most commonly used column and table level SQL constraints:

Column Level Constraints include:

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

Column Level Constraints include:

  • UNIQUE Constraint
  • CHECK Constraint
  • PRIMARY KEY Constraint
  • FOREIGN KEY Constraint
  • INDEX Constraint

Let us now dive into the world of SQL constraints! We will browse in detail what each constraint is, why we use them and how to apply and remove them.

The NOT NULL Constraint

A NOT NULL constraint specifies that no cell value for any row in this column can be blank. Generally, this rule is applied to columns that capture information that is absolutely vital to identify and extract data from a table. Continuing the Sales table example, Sale_Id and the Sales_Amount would be potential columns for applying the NOT NULL constraint.

Applying the NOT NULL constraint:

The NOT NULL constraint can be defined either during the creation of the table or can be put in place later via an alter statement.

Declaring a NOT NULL Constraint during the Creation of a Table:

The following SQL creates a NOT NULL constraint in the columns ‘Sale_Id’, ‘Sale_Amount’ and ‘Vendor_Name’ when the table ‘Sales’ is created:

1
2
3
4
5
6
7
CREATE TABLE Sales (
    Sale_Id int NOT NULL,
    Sale_Amount int NOT NULL,
    Vendor_Name varchar(255) NOT NULL,
    Sale_Date date,
    Profit int
);

By specifying the words NOT NULL after the column definition, we create a ‘Sales’ table where the ‘Sale_Id’, ‘Sale_Amount’ and ‘Vendor_Name’ columns cannot be blank. The column ‘Profit’ can have null values.

Altering a NOT NULL Constraint after the Creation of a Table:

Consider, that after the creation of the ‘Sales’ table and storing information in the same, business logic changes and now we’re instructed that no sales can be recorded in the ‘Sales’ table without recording the amount of profit that was earned on the sale. In that case, we will now have to add a constraint that the profit column cannot be null. This is how we would do it:

1
2
ALTER TABLE Sales
MODIFY Profit int NOT NULL;

The UNIQUE Constraint 

The UNIQUE constraint specifies that no cell value in a column can be repeated throughout the table.  That is, each row for this column in the table has to be unique and non-repetitive. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint has a UNIQUE constraint automatically. However, you can have many UNIQUE constraints in a table, but only one PRIMARY KEY constraint can be there in one table.

Applying the UNIQUE constraint:

The UNIQUE constraint can be defined either during the creation of the table or can be put in place later via an alter statement.

Declaring a UNIQUE Constraint during the Creation of a Table:

The following SQL creates a UNIQUE constraint in the columns ‘Sale_Id’ when the table ‘Sales’ is created in various relational databases:

SQL Server / Oracle / MS Access:

1
2
3
4
5
6
7
CREATE TABLE Sales (
    Sale_Id int NOT NULL UNIQUE,
    Sale_Amount int NOT NULL,
    Vendor_Name varchar(255) NOT NULL,
    Sale_Date date,
    Profit int
);

MySQL:

1
2
3
4
5
6
7
8
CREATE TABLE Sales (
    Sale_Id int NOT NULL,
    Sale_Amount int NOT NULL,
    Vendor_Name varchar(255) NOT NULL,
    Sale_Date date,
    Profit int,
    UNIQUE(Sale_Id)
);

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:

SQL Server / Oracle / MS Access:

1
2
3
4
5
6
7
CREATE TABLE Sales (
    Sale_Id int NOT NULL,
    Sale_Amount int NOT NULL,
    Vendor_Name varchar(255) NOT NULL,
    Sale_Date date,
    Profit int,
    CONSTRAINT UC_Sales UNIQUE (Sale_Id,Sale_Amount));

Altering a UNIQUE Constraint after the Creation of a Table:

To create a UNIQUE constraint on the “Sale_Id” column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

1
2
ALTER TABLE Sales
ADD UNIQUE(Sale_Id);

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

1
2
ALTER TABLE Sales
ADD CONSTRAINT UC_Sales UNIQUE(Sale_Id,Sale_Amount);

Dropping a UNIQUE Constraint:

To drop a UNIQUE constraint, we will need to specify the naming convention that was used during the creation of the constraint:

MySQL:

1
2
ALTER TABLE Sales
DROP INDEX UC_Sales;

SQL Server / Oracle / MS Access:

1
2
ALTER TABLE Sales
DROP CONSTRAINT UC_Sales;

The CHECK Constraint 

The CHECK constraint is used to ensure that all the records in a certain column follow a specific rule. Generally, this constraint is used to enforce business logic on values in a column to make sure that no corrupt information is entered. For example, in the ‘Sales’ table,  let’s say that the business has specified a rule that sales made to a certain vendor ‘ABC’ should not be entered into the ‘Sales’ table. To ensure that sales to this vendor are not entered into our table, we add a CHECK constraint on the ‘Vendor_Name’ column which will reject any operation that tries to insert the value ‘ABC’ in the ‘Vendor_Name’ column.

Applying the CHECK Constraint:

The CHECK constraint can be defined either during the creation of the table or can be put in place later via an alter statement.

Declaring a CHECK Constraint during the Creation of a Table:

The following SQL creates a CHECK constraint on the column ‘Vendor_Name’ when the table ‘Sales’ is created in various relational databases:

MySQL:

1
2
3
4
5
6
7
8
CREATE TABLE Sales (
    Sale_Id int NOT NULL UNIQUE,
    Sale_Amount int NOT NULL,
    Vendor_Name varchar(255),
    Sale_Date date,
    Profit int,
CHECK (Vendor_Name<> ’ABC’)
);

SQL Server / Oracle / MS Access:

1
2
3
4
5
6
7
CREATE TABLE Sales (
    Sale_Id int NOT NULL UNIQUE,
    Sale_Amount int NOT NULL,
    Vendor_Name varchar(255) CHECK (Vendor_Name<> ’ABC’),
    Sale_Date date,
    Profit int
);

Consider now that along with the rule for the vendor ‘ABC’, the business only wants sales with a profit greater than 500 to be recorded in the ‘Sales’ table. 

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

MySQL / SQL Server / Oracle / MS Access:

1
2
3
4
5
6
7
8
CREATE TABLE Sales (
    Sale_Id int NOT NULL,
    Sale_Amount int NOT NULL,
    Vendor_Name varchar(255),
    Sale_Date date,
    Profit int,
CONSTRAINT Chk_Sales CHECK (Vendor_Name <> ’ABC’ and Profit>500)
);

Altering a CHECK Constraint after the Creation of a Table:

To create a CHECK constraint on the ‘Vendor_Name’ column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access

1
2
ALTER TABLE Sales
ADD CHECK (Vendor_Name<> ‘ABC’);

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

MySQL / SQL Server / Oracle / MS Access:

1
2
ALTER TABLE Sales
ADD CONSTRAINT Chk_Sales CHECK (Vendor_Name <> ’ABC’ and Profit>500)

Dropping a CHECK Constraint:

To drop a CHECK constraint, we will need to specify the naming convention that was used during the creation of the constraint:

MySQL:

1
2
ALTER TABLE Persons
DROP CHECK CHK_Sales;

SQL Server / Oracle / MS Access:

1
2
ALTER TABLE Persons
DROP CONSTRAINT CHK_Sales;

The DEFAULT Constraint

The DEFAULT constraint is used to specify a default value that is to be entered in any record in a particular column is left blank. The default value will be added to all new records if no other value is specified.

Applying the DEFAULT Constraint:

The DEFAULT constraint can be defined either during the creation of the table or can be put in place later via an alter statement.

Declaring a DEFAULT Constraint during the Creation of a Table:

The following SQL sets a DEFAULT value for the ‘Vendor_Name’ column when the ‘Sales’ table is created:

My SQL / SQL Server / Oracle / MS Access:

1
2
3
4
5
6
7
CREATE TABLE Sales (
    Sale_Id int NOT NULL UNIQUE,
    Sale_Amount int NOT NULL,
    Vendor_Name varchar(255) DEFAULT ‘Unknown Vendor’,
    Sale_Date date,
    Profit int
);

The DEFAULT constraint can also be used to populate columns with system values, for example, GETDATE().

1
2
3
4
5
6
7
CREATE TABLE Sales (
    Sale_Id int NOT NULL UNIQUE,
    Sale_Amount int NOT NULL,
    Vendor_Name varchar(255),
    Sale_Date date DEFAULT GETDATE(),
    Profit int
);

Altering a DEFAULT Constraint after the Creation of a Table:

To create a DEFAULT constraint on the ‘Vendor_Name’ column when the table is already created, use the following SQL:

MySQL:

1
2
ALTER TABLE Sales
ALTER Vendor_Name SET DEFAULT 'Unknown Vendor';

SQL Server:

1
2
3
ALTER TABLE Sales
ADD CONSTRAINT df_Vendor
DEFAULT 'Unknown Vendor' FOR Vendor_Name;

MS Access:

1
2
ALTER TABLE Sales
ALTER COLUMN Vendor_Name SET DEFAULT 'Unknown Vendor';

Oracle:

1
2
ALTER TABLE Sales
MODIFY Vendor_Name DEFAULT 'Unknown Vendor';

Dropping a DEFAULT Constraint:

To drop a DEFAULT constraint, use the following SQL:

MySQL:

1
2
ALTER TABLE Sales
ALTER Vendor_Name DROP DEFAULT;

SQL Server / Oracle / MS Access:

1
2
ALTER TABLE Sales
ALTER COLUMN Vendor_Name DROP DEFAULT;

The INDEX Constraint

The INDEX constraint is used to create indexes on a table in a relational database. Tables in a relational database can grow to be extremely long with a great number of rows present in each table, under the circumstances, retrieving information via SQL can sometimes be a very time taking process. By creating an index, the performance of data retrieval queries can be greatly improved. The users cannot see the indexes, they are just used by the SQL engine to speed up searches/queries.

Applying the INDEX Constraint:

We have the option of creating an INDEX that allows duplicates, or we can create a unique INDEX. Indexes can be created or dropped at any point in time and do not have to be a part of the table definition at the time of table creation.

Creating an INDEX Constraint:

Creates an INDEX on a table with duplicate values allowed:

1
2
CREATE INDEX idx_id
ON Sales (Sale_Id);

Creating aUNIQUEINDEX Constraint:

Creates a unique INDEX on a table. Duplicate values are not allowed:

1
2
CREATE UNIQUE INDEX idx_id
ON Sales (Sale_Id);

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

1
2
CREATE UNIQUE INDEX idx_sale
ON Sales (Sale_Id, Sale_Amount);

Dropping an INDEX Constraint:

The DROP INDEX statement is used to delete an index in a table:

MS Access:

1
DROP INDEX idx_id ON Sales;

SQL Server:

1
DROP INDEX Sales.idx_id;

DB2/Oracle:

1
DROP INDEX idx_id;

MySQL:

1
2
ALTER TABLE Sales
DROP INDEX idx_id;

The PRIMARY KEY Constraint

PRIMARY KEYS are unique identifiers for each row present in a table. They can be values present in a single column of a table or a combination of multiple columns in the table. The PRIMARY KEY column cannot be NULL and has to be UNIQUE. The value of the PRIMARY KEY in the table is a unique identifier for a particular row in the parent table which connects the row of the table to further information available in another table (the child table), where the same unique identifier exists as a FOREIGN KEY. Every FOREIGN KEY value in the second table has to exist in the first as a PRIMARY KEY. This is how information is kept consistent in relational databases when they are broken down into multiple Fact and Dimension tables. The PRIMARY KEY – FOREIGN KEY columns are used as the join condition between two tables and the contained information in the tables are extracted.

Applying the PRIMARY KEY Constraint:

The PRIMARY KEY constraint can be defined either during the creation of the table or can be put in place later via an alter statement.

Declaring a PRIMARY KEY Constraint during the Creation of a Table:

The following SQL creates a PRIMARY KEY on the ‘Sale_Id’ column when the ‘Sales’ table is created in various relational databases:

MySQL:

1
2
3
4
5
6
7
8
CREATE TABLE Sales (
    Sale_Id int NOT NULL,
    Sale_Amount int NOT NULL,
    Vendor_Name varchar(255),
    Sale_Date date,
    Profit int,
    PRIMARY KEY (Sale_Id)
);

SQL Server / Oracle / MS Access:

1
2
3
4
5
6
7
CREATE TABLE Sales (
    Sale_Id int NOT NULL PRIMARY KEY,
    Sale_Amount int NOT NULL,
    Vendor_Name varchar(255),
    Sale_Date date,
    Profit int,
);

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

MySQL / SQL Server / Oracle / MS Access:

1
2
3
4
5
6
7
8
CREATE TABLE Sales (
    Sale_Id int NOT NULL,
    Sale_Amount int NOT NULL,
    Vendor_Name varchar(255),
    Sale_Date date,
    Profit int,
    CONSTRAINT PK_Sales PRIMARY KEY (Sale_Id, Sale_Amount)
);

Note: In the example above there is only ONE PRIMARY KEY (PK_Sales). However, the VALUE of the primary key is made up of TWO COLUMNS (Sale_Id + Sale_Amount).

Altering a PRIMARY KEY Constraint after the Creation of a Table:

To create a PRIMARY KEY constraint on the ‘Sale_Id’ column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

1
2
ALTER TABLE Sales
ADD PRIMARY KEY (Sale_Id);

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

MySQL / SQL Server / Oracle / MS Access:

1
2
ALTER TABLE Sales
ADD CONSTRAINT PK_ Sales PRIMARY KEY (Sale_Id,Sale_Amount);

Note: If you use ALTER TABLE to add a primary key, the primary key column(s) must have been declared to not contain NULL values (when the table was first created) and have only UNIQUE values, otherwise the SQL statement will not be executed.

Dropping a PRIMARY KEY Constraint:

To drop a PRIMARY KEY constraint, use the following SQL:

MySQL:

1
2
ALTER TABLE Sales
DROP PRIMARY KEY;

SQL Server / Oracle / MS Access:

1
2
ALTER TABLE Sales
DROP CONSTRAINT PK_Sales;

The FOREIGN KEY Constraint

The foreign key constraint is used to prevent operations in a relational database that would destroy links between tables. The FOREIGN KEY is a column (or a group of columns) in one table, that refers to the PRIMARY KEY of another table. The table with the FOREIGN KEY is called the child table while the referenced table with the PRIMARY KEY is called the parent table. 

Consider the two following tables:

Sales table:

Sale_IdSale_AmountVendor_NameSale_DateProfit
123100ABC01-12-201820
234200BCD14-06-201955
345500CDE22-03-202032
456100EFG25-04-202140

Sales_Person table:

Sales_Person_IdSales_Person_NameSales_Person_LocationSale_Id
1RahulKolkata234
2SwetaMumbai456
3AtulNew Delhi123
4ShrutiMumbai345

The ‘Sale_Id’ column in the ‘Sales_person’ table refers to the ‘Sale_Id’ in the ‘Sales’ table. 

The ‘Sale_Id’ in the ‘Sales’ table is the PRIMARY KEY.

The ‘Sale_Id’ in the ‘Sales_Person’ table is the FOREIGN KEY.

Notice that every value for ‘Sale_Id’ present in the ‘Sales_Person’ table is also available in the ‘Sale_Id’ column in the ‘Sales’ table. This is so due to the PRIMARY KEY – FOREIGN KEY relationship defined between the two tables. No value for a ‘Sale_Id’ can be entered into the ‘Sales_Person’ table that does not already exist in the ‘Sales’ table. If we try to insert such a value, because of the PRIMARY KEY – FOREIGN KEY constraint, the insertion will be rejected.

Applying the FOREIGN KEY Constraint:

The FOREIGN KEY constraint can be defined either during the creation of the table or can be put in place later via an alter statement.

Declaring a FOREIGN KEY Constraint during the Creation of a Table:

The following SQL creates a FOREIGN KEY on the ‘Sale_Id’ column when the ‘Sales_Person’ table is created:

MySQL:

1
2
3
4
5
6
7
8
CREATE TABLE Sales_Person (
    Sales_Person_Id int NOT NULL,
    Sales_Person_Name varchar(255),
    Sales_Person_Location varchar(255),
    Sale_Id int NOT NULL,
    PRIMARY KEY (Sales_Person_Id),
    FOREIGN KEY (Sale_Id) REFERENCES Sales(Sale_Id)
);

SQL Server / Oracle / MS Access:

1
2
3
4
5
6
CREATE TABLE Sales_Person (
    Sales_Person_Id int NOT NULL PRIMARY KEY,
    Sales_Person_Name varchar(255),
    Sales_Person_Location varchar(255),
    Sale_Id int FOREIGN KEY REFERENCES Sales(Sale_Id)
);

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

MySQL / SQL Server / Oracle / MS Access:

1
2
3
4
5
6
7
8
9
CREATE TABLE Sales_Person (
    Sales_Person_Id int NOT NULL,
    Sales_Person_Name varchar(255),
    Sales_Person_Location varchar(255),
    Sale_Id int NOT NULL,
    PRIMARY KEY (Sales_Person_Id),
    CONSTRAINT FK_Sales_Sales_Person FOREIGN KEY (Sale_Id)
    REFERENCES Persons Sales(Sale_Id)
);

Altering a FOREIGN KEY Constraint after the Creation of a Table:

To create a FOREIGN KEY constraint on the ‘Sale_Id’ column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

1
2
ALTER TABLE Sales_Person
ADD FOREIGN KEY (Sale_Id) REFERENCES Sales(Sale_Id);

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

MySQL / SQL Server / Oracle / MS Access:

1
2
3
ALTER TABLE Sales_Person
ADD CONSTRAINT FK_Sales_Sales_Person
FOREIGN KEY (Sale_Id) REFERENCES Sales(Sale_Id);

Dropping a FOREIGN KEY Constraint:

To drop a FOREIGN KEY constraint, use the following SQL:

MySQL:

1
2
ALTER TABLE Sales_Person
DROP FOREIGN KEY FK_Sales_Sales_Person;

SQL Server / Oracle / MS Access:

1
2
ALTER TABLE Sales_Person
DROP CONSTRAINT FK_Sales_Sales_Person;
at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Get max value for identity column without a table scan

  You can use   IDENT_CURRENT   to look up the last identity value to be inserted, e.g. IDENT_CURRENT( 'MyTable' ) However, be caut...