All SQL Constraints for Programmers – Complete List

In this topic, we discuss SQL Server Constraints. We first discuss the theory behind them, i.e. the concepts of Data Integrity and Referential Integrity, and Database Normalization. Then we will learn about the types of constraints in SQL with examples, its rules, and syntax. In particular, we will check table constraints. You can learn SQL queries in another article.

What are the constraints in SQL?

SQL constraints are programmatic constructs that alter the Server’s behavior while it applies INSERT/DELETE/UPDATE operations on data. SQL Constraints are SQL Server’s way of implementing the concepts of Data Integrity, Referential Integrity, and Database Normalization in general.

What is Data Integrity?

Data Integrity is the process of checking the data against real-world rules. For example, a person’s National ID info is unique to that person, and an employee’s Hiring Date must be less than their Last Working Date, etc.

What is Referential Integrity?

Referential Integrity is a set of measurements against a database to ensure the relations between its entities (tables) remain valid after any change to the data. As we will see below, SQL Server provides the Primary Key and Foreign Key constraints features to ensure that the referential integrity state of the database remains valid at all times.

Referential Integrity is a part of a general process called Database Normalization.

What is Database Normalization?

Database Normalization is the process by which we make sure the Database is in good shape in terms of the various integrity measurements and Business Logic rules.

Normalization is important because it eliminates Data redundancy. If a database is poorly designed it will contain the same data duplicated over many places of it. This adds a burden to the application accessing the database to be responsible for maintaining the Data & Referential Integrity for the database.

From a lower-level perspective, a normalized database means a smaller storage space required per row in each table since the row will never contain columns that logically belong to another table. This improves the speed of fetching rows from the disk and the number of rows fetched from each disk page read, hence more performance.

1NF, or First Normal Form

1NF stands for “1^st Normal Form”. It is the most basic step towards making a database normalized.

The first normal form states that:

• Every column in the table must be unique;
• Separate tables must be created for each set of related data;
• Each table must be identified with a unique column or concatenated columns called the primary key;
• No rows may be duplicated;
• Each column should be atomic, i.e. holding a single unit of data.

2NF, or Second Normal Form

2NF states that there must be a mechanism in the Database engine that reflects any dependency between a referencing column and a referenced column. This mechanism must enforce this dependency such that changes to the data in either the referenced column or the referencing column do not corrupt the dependency.

3NF, or Third Normal Form

3NF states that all column references in reference data that are not dependent on the primary key should be removed. Another way of putting this is that only foreign key columns should be used to reference another table, and no other columns from the parent table should exist in the referenced table. 3NF is important in reducing redundancy which improves performance as discussed earlier, but it must be noted that by definition it cannot be enforced by SQL Server using any mechanism as it depends on the Database designer to be aware of it and avoid repeating columns unnecessarily in many places.

Referential Integrity Constraints

Primary Key Constraint in SQL

The Primary Key constraint (PK) implements 1NF by making sure each table has a single column or a small set of columns that uniquely identify each row. Because primary key constraints in SQL are required to be unique, they are usually defined over an identity column, i.e. an integer column that is incremented automatically by SQL Server while inserting a new row.

To declare a Primary Key constraint in SQL Server you can do this in two ways:

• First method: inline as part of the table declaration

CREATE TABLE [dbo].[Regions](
[RegionID] [int] NOT NULL PRIMARY KEY,
[RegionDescription] [nchar](50) NOT NULL
)

(Note that the example above places the constraint over a regular int column rather than an identity column)

• Second method: Adding the constraint to an existing table

ALTER TABLE [dbo].
[Regions] ADD PRIMARY KEY CLUSTERED
(
[RegionID] ASC
)

Note the following:

• For the case of a Primary Key defined over a set of columns instead of a single column: in this case values can be repeated across a single column participating in the key but the combination of all columns in the key must always be unique.
• Primary Keys by definition can only be defined over not-null columns.
• Creating a Primary Key over a table automatically defines a Clustered Index over the table using the column(s) participating in the key. Indexes are system tables that SQL Server maintains to speed-up the process of looking for the required set of rows in a query. They can be discussed in more detail in a separate article.
• While designing the database we may face the case of a many-to-many relationship. This happens for example when we need to define the link between Students and Courses in a university database: a student can take multiple courses and a course can be taken by multiple students. Since placing the FK in either the Courses table to Students or in the Students table to Courses will not reflect this multiplicity, the solution is to create a third table (a.k.a. Junction table) that has FKs for Students and for Courses in addition to its own PK. This way we are defining the many-to-many relationship while maintaining the 1NF rule that no data is stored redundantly.

Foreign Key SQL Constraint

Foreign Key constraints (FK) is a restriction over a column or set of columns that define a relationship between the row they are part of and a unique row in another table. This is achieved by the FK matching exactly the same number and type of columns of the Primary Key (PK) of the table being referenced.

For example, the Regions table below can be used as a reference table for another table that needs to add region information to its data. We usually use the names “parent table” and “child table” to refer to the referenced table and the referencing table respectively. For example, the data in the two tables would look like this:

Regions table

SalesRegions table

The column RegionID in SalesRegions defines the link between a Sales Region info and Region info. By separating the data in this way, the FK constraint satisfies the 1NF rule that each set of related data must have its own table. For example, we have a third table to store info about Marketing Regions, etc.

To define an FK Constraint on a table you should follow this syntax.

ALTER TABLE [dbo].[SalesRegions] ADD CONSTRAINT [FK_SalesRegions_Regions] FOREIGN KEY([RegionID])
REFERENCES [dbo].[Regions] ([RegionID])
GO

Note that

• Since the parent table PK and the child table FK have the same type and number, to make any change to the types or number of the PK columns of the parent table you must first remove the FK constraint on the child table. Then after applying the changes to the PK, you need to apply the same changes to the FK of the child table. If you don’t update the FK definition to match the new parent PK changes SQL Server will reject the operation of redefining the FK.
• An FK column may or may not be nullable but in any row where it has data it has to match a row in the parent table, otherwise, SQL Server will raise an error.
• An update in the PK column value in the parent table will fail if there are dependent rows in the child table that use the old value of the PK. Likewise, an update in the FK column in the child table will fail if the new value does not match an existing value in the PK column. This way SQL Server ensures Referential Integrity is maintained at all times.

Data Integrity Constraints

We will use the following table to demonstrate the concepts below:

CREATE TABLE [dbo].[SalesRegions](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](200) NOT NULL,
[RegionID] [int] NOT NULL,
[StartDate] [date] NOT NULL,
[Code] [nvarchar](50) NOT NULL,
[Budget] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SalesRegions] WITH CHECK ADD CONSTRAINT [FK_SalesRegions_Regions] FOREIGN KEY ([RegionID])
REFERENCES [dbo].[Regions] ([RegionID])
GO
ALTER TABLE [dbo].[SalesRegions] CHECK CONSTRAINT [FK_SalesRegions_Regions]
GO

Note that some developers argue against Data Integrity constraints in SQL saying that the business logic of an application should be retained within the business layer instead of the Database layer. However, this approach makes it possible to have invalid data in the database. This can happen because there are bugs in the business logic or because data is updated via a different route. For example, when a database is used by multiple applications with differing logic. Data Integrity SQL constraints prevent this problem.

Unique Constraint

A Unique Constraint as the name implies is used to ensure that the data inside a single column or a set of columns remain unique across the whole table at all times.

A unique constraint can be defined using the following syntax:

ALTER TABLE dbo.SalesRegions ADD CONSTRAINT
UK_SalesRegions_Code UNIQUE NONCLUSTERED
(
Code
)
GO

This example adds a unique constraint over the column Code in table SalesRegions, since from the application logic perspective a sales region code should be used for only one sales region.

Both Unique Keys and Primary Keys enforce uniqueness, however, note the following differences between them:

Primary Key:

• There can only be one primary key in a table.
• It cannot be null.
• Primary Key is a unique key identifier of the record.

Unique Key:

• Can be more than one unique key in one table.
• The unique key can have NULL values.
• It can be a candidate key (i.e. if we drop the PK we can use the Unique Key as a PK from a behavior. perspective, i.e. it can be used as an FK in another table for instance).
• Since it may have null values, looking for rows having a null value in the Unique key can return multiple rows, making it non-unique in this particular case.

Also note that although it is a “Constraint”, SQL Server Management Studio will place Unique Key objects under the “Indexes” node in Object Explorer rather than the “Constraints” node under the respective table.

The difference between the two is non-existent as both have the same effect. You can reach the same effect for the constraint above by defining an index instead as follows:

CREATE UNIQUE NONCLUSTERED INDEX
UK_SalesRegions_Code  ON  dbo.SalesRegions
-(
Code
)
GO

Default Constraint

A Default Constraint adds more context to the database by making sure that a default value is given to a column whenever it makes sense to use one. For example, if you are adding a new row to a “Files” table which contains a DateCreated column, it makes sense to add a default constraint to this column to be equal to the current time.

For example in the table SalesRegions if you want to set a default constraint on the Budget column such that it is initially 1000 for a new sales region, you can do it inline in the table definition as follows:

CREATE TABLE [dbo].[SalesRegions](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](200) NOT NULL,
[RegionID] [int] NOT NULL,
[StartDate] [date] NOT NULL,
[Code] [nvarchar](50) NOT NULL,
[Budget] [decimal](18, 0) NULL DEFAULT(1000)
) ON [PRIMARY]
GO

You can also do it after the table has been declared as follows:

ALTER TABLE [dbo].[SalesRegions] ADD CONSTRAINT [DF_SalesRegions_Budget] DEFAULT ((1000)) FOR [Budget]
GO

The advantage of the second approach – which can also be used while creating the table as a second statement following the CREATE statement – is that you can control the name of the default constraint. This is useful if you follow a naming standard for your database schema.

Check Constraint

A Check Constraint is a logical rule that makes sure the data entered in one or more columns is consistent with business rules. For example in our SalesRegion table we can limit the budget to be between $15,000 and $100,000 while creating the table as follows:

CREATE TABLE [dbo].[SalesRegions](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](200) NOT NULL,
[RegionID] [int] NOT NULL,
[StartDate] [date] NOT NULL,
[Code] [nvarchar](50) NOT NULL,
[Budget]  [decimal](18, 0) NULL,
Check ([Budget]) between 15000 and 100000),
) ON [PRIMARY]
GO

Similarly, you can add it to the table after it has been created to control the name assigned to the constraint as follows:

ALTER TABLE [dbo].[SalesRegions] ADD CONSTRAINT CC_SalesRegions_Budget CHECK (([Budget]>=(15000) AND [Budget]<=(100000)))
GO

Note that check SQL constraints that require more complex logic, for instance like looking for value across another table will typically be implemented as a user-defined function or as a trigger.

Not Null Constraint

A Not-Null Constraint validates that a certain column can never be left with an unknown value. This can be used to enforce the application logic whenever the application assumes a column must always have a value, for example, a Spare Part must always have a Serial Number.

The way to declare this constraint is to simply add the text “NOT NULL” at the end of the column definition, as you can see in most of the columns in the definition of SalesRegions.

Note that you may decide to make a column not-nullable later in the application development. To apply this you need to update the column’s data first to replace all its null values with a default value and then apply the NOT NULL constraint in a command similar to this:

Alter TABLE [dbo].[SalesRegions] ALTER COLUMN Budget [decimal](18,0) NOT NULL
GO

Conclusion

In this article, we discussed SQL Server constraints as SQL Server’s way of applying Data Integrity and Referential Integrity rules. We also presented some background theory on Database Normalization as this theory is directly related to the Constraints concept in SQL Server.

   

About the Author

ByteScout Team of Writers ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.