Skip to main content Skip to docs navigation
Views 269

sql-constraints-keys

Advertisments

Constraints in SQL Server: SQL NOT NULL, UNIQUE and SQL PRIMARY KEY

Constraints in SQL Server are predefined rules and restrictions that are enforced in a single column or multiple columns, regarding the values allowed in the columns, to maintain the integrity, accuracy, and reliability of that column’s data. In other words, if the inserted data meets the constraint rule, it will be inserted successfully. If the inserted data violates the defined constraint, the insert operation will be aborted.Constraints in SQL Server can be defined at the column level, where it is specified as part of the column definition and will be applied to that column only, or declared independently at the table level. In this case, the constraint rules will be applied to more than one column in the specified table. The constraint can be created within the CREATE TABLE T-SQL command while creating the table or added using ALTER TABLE T-SQL command after creating the table. Adding the constraint after creating the table, the existing data will be checked for the constraint rule before creating that constraint.

There are six main constraints that are commonly used in SQL Server that we will describe deeply with examples within this article and the next one. These constraints are:

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

NOT NULL Constraint

By default, the columns are able to hold NULL values. A NOT NULL constraint in SQL is used to prevent inserting NULL values into the specified column, considering it as a not accepted value for that column. This means that you should provide a valid SQL NOT NULL value to that column in the INSERT or UPDATE statements, as the column will always contain data.

USE kodingnotes
CREATE TABLE kodingnotesdemo
(
       ID INT NOT NULL,
       Name VARCHAR(50) NULL
)

If we try to perform the below three insert operations: 

INSERT INTO kodingnotesdemo ([ID],[NAME]) VALUES (1,'Ali')
INSERT INTO kodingnotesdemo ([ID]) VALUES (2)
INSERT INTO kodingnotesdemo ([NAME]) VALUES ('Fadi')

You will see that the first record will be inserted successfully, as both the ID and Name column’s values are provided in the INSERT statement. Providing the ID only in the second INSERT statement will not prevent the insertion process from being completed successfully, due to the fact that the Name column is not mandatory and accepts NULL values. The last insert operation will fail, as we only provide the INSERT statement with a value for the Name column, without providing value for the ID column that is mandatory and cannot be assigned NULL value, as shown in the error message below:

Checking the inserted data, you will see that only two records are inserted and the missing value for the Name column in the second INSERT statement will be NULL, which is the default value, as shown in the result below:

UNIQUE Constraints

The UNIQUE constraint in SQL is used to ensure that no duplicate values will be inserted into a specific column or combination of columns that are participating in the UNIQUE constraint and not part of the PRIMARY KEY. In other words, the index that is automatically created when you define a UNIQUE constraint will guarantee that no two rows in that table can have the same value for the columns participating in that index, with the ability to insert only one unique NULL value to these columns, if the column allows NULL.

USE kodingnotes
CREATE TABLE ConstraintDemo2
(
     ID INT UNIQUE,
     Name VARCHAR(50) NULL
);

--INSERT DATA IN ABOVE TABLE
INSERT INTO ConstraintDemo2 ([ID],[NAME]) VALUES (1,'Ali');
INSERT INTO ConstraintDemo2 ([ID],[NAME]) VALUES (2,'Ali');
INSERT INTO ConstraintDemo2 ([ID],[NAME]) VALUES (NULL,'Adel');
INSERT INTO ConstraintDemo2 ([ID],[NAME]) VALUES (1,'Faris');

The INFORMATION_SCHEMA.TABLE_CONSTRAINTS system object can be easily used to retrieve information about all defined constraints in a specific table using the T-SQL script below:

SELECT CONSTRAINT_NAME,
     TABLE_SCHEMA ,
     TABLE_NAME,
     CONSTRAINT_TYPE
     FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
   WHERE TABLE_NAME='ConstraintDemo2'
--DROP CONSTRAINTS
ALTER TABLE ConstraintDemo2 DROP CONSTRAINT [UQ__Constrai__3214EC26B928E528]

PRIMARY KEY Constraint

The PRIMARY KEY constraint consists of one column or multiple columns with values that uniquely identify each row in the table. The SQL PRIMARY KEY constraint combines between the UNIQUE and SQL NOT NULL constraints, where the column or set of columns that are participating in the PRIMARY KEY cannot accept a NULL value. If the PRIMARY KEY is defined in multiple columns, you can insert duplicate values on each column individually, but the combination values of all PRIMARY KEY columns must be unique. Take into consideration that you can define only one PRIMARY KEY per each table, and it is recommended to use small or INT columns in the PRIMARY KEY.

In addition to providing fast access to the table data, the index that is automatically created, when defining the SQL PRIMARY KEY, will enforce the data uniqueness. The PRIMARY KEY is used mainly to enforce the entity integrity of the table. Entity integrity ensures that each row in the table is a uniquely identifiable entity.

PRIMARY KEY constraint differs from the UNIQUE constraint in that; you can create multiple UNIQUE constraints in a table, with the ability to define only one SQL PRIMARY KEY per each table. Another difference is that the UNIQUE constraint allows for one NULL value, but the PRIMARY KEY does not allow NULL values.

Assume that we have the below simple table with two columns; the ID and Name. The ID column is defined as a PRIMARY KEY for that table, that is used to identify each row on that table by ensuring that no NULL or duplicate values will be inserted to that ID column. The table is defined using the CREATE TABLE T-SQL script below:

USE kodingnotes
GO
CREATE TABLE ConstraintDemo3
(
     ID INT PRIMARY KEY,
   Name VARCHAR(50) NULL
)

If you try to run the three INSERT statements below:

INSERT INTO ConstraintDemo3 ([ID],[NAME]) VALUES (1,'John');
INSERT INTO ConstraintDemo3 ([NAME]) VALUES ('Fadi');
INSERT INTO ConstraintDemo3 ([ID],[NAME]) VALUES (1,'Saeed');

You will see that the first record will be inserted successfully as both the ID and Name values are valid. The second insert operation will fail, as the ID column is mandatory and cannot be NULL, as the ID column is the SQL PRIMARY KEY. The last INSERT statement will fail too as the provided ID value already exists and the duplicate values are not allowed in the PRIMARY KEY

If you do not provide the SQL PRIMARY KEY constraint with a name during the table definition, the SQL Server Engine will provide it with a unique name as you can see from querying the INFORMATION_SCHEMA.TABLE_CONSTRAINTS system object below:

SELECT CONSTRAINT_NAME,
     TABLE_SCHEMA ,
     TABLE_NAME,
     CONSTRAINT_TYPE
     FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
   WHERE TABLE_NAME='ConstraintDemo3'

The ALTER TABLE…DROP CONSTRAINT T-SQL statement can be used easily to drop the previously defined PRIMARY KEY using the name derived from the previous result:

--DROP
ALTER TABLE ConstraintDemo3
DROP CONSTRAINT PK__Constrai__3214EC27E0BEB1C4;
--ALTER
ALTER TABLE ConstraintDemo3
ADD PRIMARY KEY (ID);

FOREIGN KEY Constraint

A Foreign Key is a database key that is used to link two tables together. The FOREIGN KEY constraint identifies the relationships between the database tables by referencing a column, or set of columns, in the Child table that contains the foreign key, to the PRIMARY KEY column or set of columns, in the Parent table.

 
USE kodingnotes

CREATE TABLE ConstraintDemoParent
(
       ID INT PRIMARY KEY,
	 Name VARCHAR(50) NULL
);

CREATE TABLE ConstraintDemoChild
(
        CID INT PRIMARY KEY,
	 ID INT FOREIGN KEY REFERENCES ConstraintDemoParent(ID)
)
INSERT INTO ConstraintDemoParent ([ID],[NAME]) VALUES (1,'John'),(2,'Mika'),(3,'Sanya')
INSERT INTO ConstraintDemoChild (CID,ID) VALUES (1,1)
INSERT INTO ConstraintDemoChild (CID,ID) VALUES (2,4)

As we did not mention the FOREIGN KEY constraint name while creating the child table, SQL Server will assign it a unique name that we can retrieve from the INFORMATION_SCHEMA.TABLE_CONSTRAINTS system view using the following query:

SELECT CONSTRAINT_NAME,
   TABLE_SCHEMA ,
   TABLE_NAME,
   CONSTRAINT_TYPE
   FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
	 WHERE TABLE_NAME='ConstraintDemoChild'

Then we can easily use the previous result to drop the FOREIGN KEY constraint using the following
ALTER TABEL … DROP CONSTRAINT T-SQL statement:

ALTER TABLE ConstraintDemoChild
DROP CONSTRAINT  FK__ConstraintDe__ID__0B91BA14;

But if we try to create the FOREIGN KEY constraint again on the ID column of the child table, using following ALTER TABLE T-SQL statement:

ALTER TABLE ConstraintDemoChild
ADD CONSTRAINT FK__ConstraintDe__ID
FOREIGN KEY (ID) REFERENCES ConstraintDemoParent(ID);

CHECK Constraint

A CHECK constraint is defined on a column or set of columns to limit the range of values, that can be inserted into these columns, using a predefined condition. The CHECK constraint comes into action to evaluate the inserted or modified values, where the value that satisfies the condition will be inserted into the table, otherwise, the insert operation will be discarded. It is allowed to specify multiple CHECK constraints for the same column.

CREATE TABLE ConstraintDemo4
(
     ID INT PRIMARY KEY,
	    Name VARCHAR(50) NULL,
	    Salary INT CHECK (Salary>0)
)

If you execute the below three INSERT statements:


INSERT INTO ConstraintDemo4 ([ID],[NAME],Salary) VALUES (1,'John',350)
INSERT INTO ConstraintDemo4 ([ID],[NAME],Salary) VALUES (2,'Mike',0)
INSERT INTO ConstraintDemo4 ([ID],[NAME],Salary) VALUES (3,'Nikola',-72)

The CHECK constraint can be dropped using the ALTER TABLE T-SQL statement. Using the CHCEK constraint name we got previously, the below command can be used to drop the CHECK constraint on the ConstraintDemo4 table:

ALTER TABLE ConstraintDemo4
DROP CONSTRAINT  CK__Constrain__Salar__0F624AF8;

DEFAULT Constraint

A DEFAULT constraint is used to provide a default column value for the inserted rows if no value is specified for that column in the INSERT statement. The Default constraint helps in maintaining the domain integrity by providing proper values for the column, in case the user does not provide a value for it. The default value can be a constant value, a system function value or NULL.

CREATE TABLE ConstraintDemo5
(
        ID INT PRIMARY KEY,
	    Name VARCHAR(50) NULL,
	    EmployeeDate DATETIME NOT NULL DEFAULT GETDATE()
)

If we execute the two INSERT statements below:

INSERT INTO ConstraintDemo5 ([ID],[NAME],EmployeeDate) VALUES (1,'Lorance','2016/10/22')
INSERT INTO ConstraintDemo5 ([ID],[NAME]) VALUES (2,'Shady')

The DEFAULT constraint can be easily dropped using the ALTER TABLE … DROP CONSTRAINT T-SQL command below:

ALTER TABLE ConstraintDemo5
DROP CONSTRAINT  DF__Constrain__Emplo__1332DBDC;

And created using the ALTER TABLE …ADD CONSTRAINT T-SQL command below:

ALTER TABLE ConstraintDemo5
Add Constraint DF__Constrain__Emplo  DEFAULT (GETDATE()) FOR EmployeeDate

 

Comments