Constraints In Microsoft SQL Server 2000

Database constraints, database integrity, keys and constraints are explained with code and examples thoroughly in this article.  Ensuring data integrity is emphasized as well. Protect your information investment by using these helpful tips and keys!  “Moving the responsibility for data integrity into the database has been revolutionary to database management. Constraints in Microsoft SQL Server 2000 allow us to define the ways in which we can enforce the integrity of a database.  Using constraints is preferred to using triggers, stored procedures, rules, and defaults, as a method of implementing data integrity rules.”


Moving the responsibility for data integrity into the database has been revolutionary to database management. Constraints in Microsoft SQL Server 2000 allow us to define the ways in which we can automatically enforce the integrity of a database. Constraints define rules regarding permissible values allowed in columns and are the standard mechanism for enforcing integrity. Using constraints is preferred to using triggers, stored procedures, rules, and defaults, as a method of implementing data integrity rules. The query optimizer also uses constraint definitions to build high-performance query execution plans. Today we will be learning about the different types of constraints and their sub classes.  What is the common goal?  Ensuring data integrity!

{mospagebreak title=Types of Constraints}

There are a number of different ways to implement constraints, but each of them falls into one of these three categories: entity, domain, and referential integrity constraints.

Domain Constraints: A Domain constraint deals with one or more columns. It is important to ensure that a particular column or a set of columns meets particular criteria. When you insert or update a row, the constraint is applied without respect to any other row in the table. The focus is on the data that is in the column. These kinds of constraints will resurface when we deal with Check constraints, Default constraints and rules and defaults.

Entity Constraints: Entity constraints are all about individual rows. This constraint ignores the column as a whole and focuses on a particular row. This can be best exemplified by a constraint that requires every row to have a unique value for a column or a combination of columns. This is to ensure that for a particular row, the same value does not already exist in some other row. We’ll see this kind of constraint in dealing with Primary key and Unique constraints.

Referential Integrity Constraints: Referential integrity constraints are created when a value in one column must match the value in another column. It can either be in the same table or more typically, a different table. For example, we are taking orders for a product, and we accept credit payment. But we will accept only a few standard credit card companies like Visa, MasterCard, Discover, and American Express. Referential integrity constraints allow us to build what we would call a domain table. A domain table is table whose sole purpose is to provide a limited list of acceptable values. In our case we have a CreditCard table with CreditCardID, and CreditCard as fields. We can then build one or more tables that reference the CreditCardID column of our domain table. With referential integrity, any table that is defined as referencing our CreditCard table will have to have a column that matches up to the CreditCardID column of our CreditCard table. For each row we insert into the referencing table, it will have a value that is in our domain list. We will see more of this when we learn about Foreign key constraints.

{mospagebreak title=Key Constraints}

Keys are the cornerstone concepts of database design and management. There are four different types of common keys used in almost all database endeavors. These are Primary keys, Foreign keys, Alternate keys and Inversion keys. We will be looking at the first three keys in detail today.  Inversion keys are merely alternative ways of sorting the data.

Primary Key Constraints

Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key. A Primary key ensures uniqueness within the column declared as being part of that Primary key, and that unique value serves as an identifier for each row in that table. There are two ways to create Primary keys: the CREATE TABLE and ALTER TABLE commands. Using a small, integer column as a Primary key is recommended. Each table should have a Primary key.

Now let us see how to use Primary key constraint in a create table command:

[code]
USE Accounting
CREATE TABLE Employees
{
 EmployeeID   int   IDENTITY   NOT NULL PRIMARY KEY,
 EmployeeName   varchar(30)   NOT NULL,
 SSN   varchar(11)   NOTNULL,
 Address1   varchar(25)   NOT NULL,
 Address2   varchar(20)   NOT NULL,
 City   varchar(20)   NOTNULL,
 State   varchar(2)   NOT NULL,
 Zip   varchar(10)   NOT NULL,
 Contact   varchar(25)   NOT NULL,
 Phone   char(15)   NOT NULL,
 Salary   money   NOT NULL,
 HireDate   smalldatetime   NOT NULL,
 TerminationDate   smalldatetime   NULL,
 EmpManagerID   int   NOT NULL
 Department   varchar(25)   NOT NULL
}[/code]

Creating a Primary key on an already existing table, is easy and it looks like follows:

[code]
Use Accounting

  ALTER TABLE Customers
  ADD CONSTRAINT pk_custID
  PRIMARY KEY (CustomerID)
[/code]

A table may have more than one combination of columns that could uniquely identify the rows in a table. Each combination is a Candidate key. We pick one of the Candidate keys to be the Primary key. For example, in the Car_Parts table (Example 3), both Part_No and Part_Name could be Candidate keys, but only Part_No is chosen as a Primary key.

[code]

CREATE TABLE Car_Parts
            (Part_No   int   PRIMARY KEY,
            Part_Name  char(30),
            Part_Weight  decimal(6,2),
            Part_Color   char(15) )
[/code]

The index generated by a Primary key constraint cannot cause the number of indexes on the table to exceed 249 non-clustered indexes and 1 clustered index. If CLUSTERED or NONCLUSTERED is not specified for a Primary key constraint, CLUSTERED is used if there are no clustered indexes specified for UNIQUE constraints.

{mospagebreak title=Foreign Key Constraints}

Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables. When we add a Foreign key to the table, we are creating a dependency between the table for which we define the Foreign key (the referencing table) and the table your Foreign key references (the referenced table). Once we have set up a Foreign key for a table, any record inserted into the referencing table must either have a matching record in the referenced column(s) of the referenced table, or the value of the Foreign key column must be set to NOTNULL. Some examples will help to clarify this.

The syntax for the Foreign key is as follows:

[code]
FOREIGN KEY REFERENCES <table name> (<column name>)
          [ON DELETE {CASCADE | NO ACTION}]
          [ON UPDATE {CASCADE | NO ACTION}]
[/code]

 

Let’s create another table in our Accounting database called Order. Please note:  In this CREATE script we are going to use both Foreign key and Primary key constraints. The table code looks like this: (Example 4).

 

[code]
USE Accounting
CREATE TABLE Orders
(
 OrderID   int IDENTITY   NOT NULL PRIMARY KEY
 CustomerID  FOREIGN KEY
        REFERENCES Customers (CustomerID),
OrderDate   smalldatetime   NOT NULL,
EmployeeID   int   NOT NULL
)
[/code]


Note the actual column being referenced must have either a PRIMARY KEY or UNIQUE constraint defined on it. We will discuss UNIQUE constraints shortly. In order to make sure that we have successfully run the above code, run the following command:

 

[code]
EXEC sp_helpconstraint <table_name>
//So for our Orders it will be:
EXEC sp_helpconstraint orders
[/code]

 

When we run the command we will get information regarding name, criteria, and status for all constraints in the table. For our Orders tables it will be one Primary key and one Foreign key. The word CLUSTERED will appear right after the Primary key, this just means it has a clustered index. Unlike Primary keys, we are not limited to just one Foreign key on a table. We can have 0 to 253 Foreign keys in each table. The only limitation is that a given column can only reference one Foreign key.

 

Adding Foreign keys to an existing table is also simple and similar to adding a Primary key. Take a look at Example 5.

[code]
Use Accounting
ALTER TABLE Orders
ADD CONSTRAINT Fk_EmpCreateOrders
FOREIGN KEY (EmployeeID) REFERENCES Employee (EmployeeID)
[/code]


{mospagebreak title=Making a Self-Referencing Table} 

 
What if the column we want to reference is not in another table, but within the same table?  No problem.  A table can be a referencing and a referenced table. Before we actually create this self-referencing constraint that references (not null) fields based on an identity column, it is critical to get at least one row in the table prior to addition of the Foreign key.  Let’s look at example 6:

[code]
Use Accounting
ALTER TABLE Employees
ADD CONSTRAINT Fk_EmpManager
FOREIGN KEY (EmpMangerID) REFERENCES Employees (EmployeeID)
[/code]

 

We have our EMPLOYEES table, but if we were creating our EMPLOYEES table from scratch it is a self-referencing table and will look like this: (Example 7)

[code]
USE Accounting
CREATE TABLE Employees
{
     EmployeeID   int   IDENTITY   NOT NULL PRIMARY KEY,
     EmployeeName   varchar(30)   NOT NULL,
     SSN   varchar(11)   NOT NULL,
     Address1   varchar(25)   NOT NULL,
     Address2   varchar(20)   NOT NULL,
     City   varchar(20)   NOTNULL,
     State   varchar(2)   NOT NULL,
     Zip   varchar(10)   NOT NULL,
     Contact   varchar(25)   NOT NULL,
     Phone   char(15)   NOT NULL,
     Salary   money   NOT NULL,
     HireDate   smalldatetime   NOT NULL,
     TerminationDate   smalldatetime   NULL,
     EmpManagerID   int   NOTNULL
                   REFERENCES EMPLOYEES(EmployeeID),
     Department   varchar(25)   NOT NULL
}[/code]

 

If we try to delete the EMPLOYEES table at this point, we will get an error. We have established the reference in our ORDERS table to the EMPLOYEES table, the two tables are “schema bound.” We have to drop the Foreign key in the ORDERS table before SQL Server allows us to drop EMPLOYEES table.

 

{mospagebreak title=Cascading Updates And Deletes}

 

The processing of making automatic deletion and updates is known as Cascading. We cannot insert a row with a Foreign key value (except NULL) if there is no Candidate key with that value. The ON DELETE clause controls what actions are taken if we attempt to delete a row to which existing Foreign keys point. The ON DELETE clause has the following two options:

  1. NO ACTION specifies that the deletion fails by producing an error.
  2. CASCADE specifies that all the rows with Foreign keys pointing to the deleted row are also deleted.

The ON UPDATE clause defines the actions that are taken if you attempt to update a candidate key value to which existing Foreign keys point. It also supports the NO ACTION and CASCADE options.

 

The following code (Example 8) illustrates theses concepts. The Order_Parts table establishes a Foreign key referencing the Car_Parts table defined earlier. Usually, Order_Parts would also have a Foreign key against an order table, but this is a simple example.

 

[code]
CREATE TABLE Order_Parts
(Order_NO int,   Part_NO   int; FOREIGN KEY REFERENCES Car_Parts(Part_NO)
ON DELETE NO ACTION, qty_ordered int)
GO[/code]

 

To sum it all up: when a value other than NULL is entered into the column of a Foreign key constraint, the value must exist in the referenced column; otherwise, a Foreign key violation error message is returned. Foreign key constraints can reference only tables within the same database on the same server. Cross-database referential integrity must be implemented through triggers. Foreign key constraints can reference another column in the same table (a self-reference). The References clause of a column-level Foreign key constraint can list only one reference column, which must have the same data type as the column on which the constraint is defined.

The references clause of a table-level Foreign key constraint must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list. Cascade may not be specified if a column of type timestamp is part of either the Foreign key or the referenced key. It is possible to combine Cascade and No Action on tables that have referential relationships with each other. If SQL Server encounters No Action, it terminates and rolls back related Cascade actions. When a Delete statement causes a combination of Cascade and No Action actions, all the Cascade actions are applied before SQL Server checks for any No Action. Foreign key constraints are not enforced on temporary tables. Foreign key constraints can reference only columns in Primary key or Unique constraints in the referenced table or in a Unique index on the referenced table.

 

{mospagebreak title=Unique Constraints}

 

The easiest to handle, Unique constraints are essentially the younger siblings to Primary keys. They require unique value throughout the named column or combination of columns in the table. Often, Unique constraints are referred to as Alternate Keys. Alternate keys are not considered to be the unique identifier of a record in a table. Unique constraints can be multiple. Once we establish a Unique constraint, every value in the named column must be unique. SQL Server will show an error if you try to update or insert a row with a value that already exists in a column with a Unique constraint.

 

Unlike Primary key, a Unique key will not automatically prevent us from entering NULL values, we have to explicitly state the null value of the column. Keep in mind though, if we do allow NULL values, we will still be able to insert only one of them. Although a NULL does not equal another NULL, it is a duplicate value to the Unique constraint.

 

Let us create another table in our Accounting database called Shipment. Example 9:

 

[code]
USE Accounting
CREATE TABLE Shipment
(
  ShipperID   int   IDENTITY   NOT NULL   PRIMARY KEY,
  ShipperName   varchar(30)   NOT NULL,
  Address   varchar(25)   NOT NULL,
  City   varchar(25)   NOT NULL,
  State   varchar(2)   NOT NULL,
  Zip   varchar(10)   NOT NULL,
  Phone   varchar(14)   NOT NULL
 UNIQUE
)[/code]

Creating A Unique Constraint on an Existing Table is simple and similar to adding a Primary key. Look at Example 10:

[code]
Use Accounting
ALTER TABLE Employees
ADD CONSTRAINT Ak_EmpSSN
UNIQUE (SSN)
[/code]

{mospagebreak title=Check Constraints}

The nice thing about Check constraints is that we are not restricted to a particular column. We can also relate them to a column, but in essence they are table-related, which means they can check one column against another as long as all the columns belong to the same table and the values must be for the same row being updated or inserted.  Here are some typical cases where Check constraint will come in handy:

  1. Limit Month column – use SQL command “BETWEEN 1 AND 12”
  2. SSN  format – use SQL command LIKE ‘[0-9][0-9][0-9]- [0-9][0-9][0-9]- [0-9][0-9][0-9][0-9]‘
  3. For a city column check against a specified list, use CHECK(city) IN (“NY”, “FL”, “CA”, “MA”, )

In the following example (11) we are using Check constraint to check if the column min_lvl is greater than or equal to 10 and max_lvl less than or equal to 250.

[code]CREATE TABLE Jobs
(job_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED,job_desc varchar(50)NOT NULL, min_lvl tinyint NOT NULL CHECK (min_lvl >= 10),max_lvl tinyint NOT NULL CHECK (max_lvl <= 250))
[/code]

So while we try to insert any values in the min_lvl column less than 10, it will show an error. Similarly for max_lvl column any value greater than 250 will raise an error.

Creating A Check Constraint on an Existing Table  

We have a table called Customer and it has a field already defined as “DateInSystem GETDATE()” this field gets its date based on the current system date. Alter it to include a check constraint.

Example 12: If we try to enter any date less than the current date it will show an error.

[code]
Use Accounting
ALTER TABLE Customers
ADD CONSTRAINT Chk_DateSys
CHECK
(DateInSystem <= GETDATE())
[/code]

A column can have any number of Check constraints, and the condition can include multiple logical expressions combined with AND and OR. Multiple Check constraints for a column are validated in the order created. The search condition must evaluate to a Boolean expression and cannot reference another table. A column-level Check constraint can reference only the constrained column, and a table-level Check constraint can reference only columns in the same table. Check constraints and rules serve the same function of validating the data during Insert and Delete statements. When a rule and one or more Check constraints exist for a column or columns, all restrictions are evaluated.

{mospagebreak title=Default Constraints}

Like all constraints, Default constraints become an integral part of the table definition. It defines what to do when a row is inserted with data for the column on which you have not determined a Default constraint. We can set the default value to be either as a literal string say default salary $3000 or as a one of the several system values such as GETDATE().

Look at Example 13:

[code]
USE Accounting
CREATE TABLE Shippers
(
  ShipperID  int  IDENTITY NOT NULL PRIMARY KEY,
  ShipperName  varchar(30)  NOT NULL,
  Address  varchar(25)  NOT NULL,
  DateInSystem  smalldatetime  NOT NULL
 DEFAULT GETDATE()
)[/code]

Insert values into Shippers like follows:

[code]
Insert into Shippers (shipperName)
  Values
     (‘UPS’)
[/code]

When we run a select statement on Shippers:

[code]
Select * from Shippers;
[/code]

The default value is generated for DateInSystem column we if we did not supply the value because its default value is set to current system date.

ShipperID       ShipperName       DateInSystem

1                  UPS                    2003-12-25:23:26:00

Creating Default Constraint on an Existing Table  

 

The Jobs table is altered so that, a character string default supplies a description (column job_desc) when the actual description is not entered explicitly. (Example 14)

[code]
ALTER TABLE Jobs
ADD CONSTRAINT CnDef_jobs
DEFAULT 'New Position - title yet to be assigned’ FOR job_desc;
[/code]

Let us write a sample table (example 15) which looks more like the Employee table in the pubs database to test the skills we have learned so far.

[code]
CREATE TABLE employee2
(emp_id varchar(7) CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED CONSTRAINT
CK_emp_id CHECK (emp_id LIKE'[A-Z][A-Z][A-Z][1-9][0-9][0-9][FM]' or emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][FM]',fname varchar(20) NOT NULL, minit char(1) NULL, lname varchar(30) NOT NULL, job_id smallint NOT NULL DEFAULT 1
REFERENCES jobs (job_id), job_lvl tinyint DEFAULT 10,
hire_date datetime NOT NULL DEFAULT (getdate())
)[/code]


In the above table employee2 emp_id is the Primary key column. Each employee ID consists of three characters that represent the employee’s initials, followed by a three-digit number ranging from 100 through 999 and then the employee’s gender (M or F). A (hyphen) – is acceptable for the middle initial. The job_id column of the employee2 table for new hires refers to the job_id column of the Jobs table, so a Foreign key constraint is set up here. Default constraint is used for job_lvl column to set its value to 10 if no value is supplied at the time of insert. Similarly by default, the current system date is entered for the hire_date column.

 

So we have dealt with types of constraints in SQL Server 2000, its sub classes, creating tables with specific constraints, and modifying existing tables. Now the job is up to you to experiment with a few codes of your own to master this basic yet essential part of database designing and data integrity.

2 thoughts on “Constraints In Microsoft SQL Server 2000

  1. The article is well informative. It has placed various attributes with well designed examples.
    One doubt I have while covering CHECK constraint. How to enter one of the some specific pre-defined values in a column? – for ex:if I would enter one of the two values (say) ‘NSE’, ‘BSE’ against the column ‘TypeOfShare’, then how can I restrict a user to doing so?

    Regards,
    Manas K. Malla

[gp-comments width="770" linklove="off" ]