ANSI Constraints and Declarative Integrity Mechanisms

You can use ANSI constraints and declarative integrity to ensure the accuracy of your data. These include the use of the IDENTITY property and the following constraints: default, check, primary key, foreign key, and unique.

The IDENTITY Property

The IDENTITY property was introduced in SQL Server 6.0. It enables you to use system-generated values in your tables. This is similar to the auto-number data type in Microsoft Access and Microsoft FoxPro databases. You are allowed a single column in each table with the IDENTITY property.

Typically, identity columns are used to generate what are known as system-assigned keys. To enforce entity integrity, you must have a way to uniquely identify every row in a table. If there is no natural column or set of columns that do this for you, you might want to create an identity column.

You can use the IDENTITY property only if the column to which it's being assigned is an integer or is compatible with an integer. Therefore, you can use the following data types:

  • Tinyint
  • Smallint
  • Integer
  • Numeric
  • Decimal

You can use numeric and decimal only if they have a scale of 0 (such as numeric (12,0)). It must also not allow nulls. You might want to use these data types that scale a bit more because the IDENTITY property doesn't reuse values by default and won't wrap around. After it has been "filled up," no more inserts are allowed.

Each time you perform an insert into a table with the IDENTITY property enabled for a column, the next available value will be automatically inserted into that column.

IDENTITY [(seed, increment)]


  • seed is the value identifying the starting value for the identity column. It is used for the first row inserted into the table.
  • increment is the value that identifies the amount of change (expressed as an integer) that will occur between each value. This value can be positive or negative.

If you don't specify a seed or increment, they each default to 1. Hence, the first row would have a value of 1, the next 2, and so on.

The IDENTITY property is used during a CREATE TABLE or an ALTER TABLE statement, as discussed on Day 9, "Creating Tables." For example, the following would result in a value of 1 for col1 when the first row is added, then 101, then 201, and so on:

(col1 int not null IDENTITY(1,100),
 col2 char(5) not null)

Because identity columns don't wrap, you can see that you might run out. Because you can't change data types in SQL Server after you've created a table, make sure you select a data type large enough to handle any conceivable values you might have for this column.

If you had used IDENTITY without the optional parameters, it would be set to (1,1).

You can refer to the column using the keyword IDENTITYCOL instead of the proper column name. Because there can be only one column in each table with the IDENTITY property set, SQL Server will always be able to figure out which column you're referring to. In the previous example, you could run SELECT IDENTITYCOL FROM mytable5 and SQL Server would return only the data for col1.

To get information about an identity column on a table, you can run the sp_help system-stored procedure, specifying the table name as a parameter. You can also use the system functions IDENT_INCR or IDENT_SEED.



  • IDENT_SEED returns the seed parameter used during the create or alter table
  • IDENT_INCR returns the increment parameter used during the create or alter table

From a programming perspective, an important question comes up right away. How would you know what value was last inserted? In comes the global value @@IDENTITY. Every time you insert a value into a table that has an identity column, the @@IDENTITY value is updated.

Try the code in Listing 14.2 to see how this works. You'll work with myidenttab, which was created earlier when you ran these commands as you learned about them.

Example 14.2. Using @@IDENTITY

CREATE TABLE myidenttab
(col1 int not null IDENTITY(1,100),
 col2 char(5) not null)
INSERT myidenttab (col2) VALUES ('howdy')
SELECT @@identity

Note: It should be 1.

INSERT myidenttab(col2) VALUES ('Movie')
SELECT @@identity

Note: Now it should be 101.

When you run the truncate table statement, it resets the identity value back to the initial seed. Ordinary deletes—even deletes of every row in the table—will not have this effect, however. Try it here:

DELETE myidenttab
INSERT myidenttab (col2) VALUES ('Zebra')
SELECT @@identity

Note: It should be 201, even though it's the only row in the table.

INSERT myidenttab (col2) VALUES ('howdy')
SELECT @@identity

Note: It should be 1 again.

Identity values are kept as a pool in memory. It's not guaranteed that every one will be used because some transactions might be canceled, and sometimes server crashes occur. If you want your identity columns to be guaranteed to be unique, you must have a unique index on that column. Some people want to reuse identity values. By default, however, you cannot manually insert into identity columns. You can use the identity_insert option, however, to override that for a single table from within a single session.



SET identity_insert [database.[owner.]]tablename ON|OFF

The preceding turns on the capability to directly insert into a table's identity column. You must be a member of the sysadmin role, the db_owner or db_ddladmin roles, or the table owner to turn this option on.

To use this on myidenttab, run this Transact-SQL code. You must run it all from within the same SQL Server Query Analyzer because of the session-specific set statement.

SET identity_insert myidenttab ON
INSERT myidenttab (col1, col2) VALUES (2,'jolly')
SET identity_insert myidenttab OFF
SELECT * FROM myidenttab

This will insert the row you've requested into the table. You must specify the column list, even if you are specifying a value for every column in the table.

ANSI Constraints

ANSI constraints are functionally very similar to the traditional objects you looked at earlier. However, they are not separate objects; they are part of the definition of the tables in your database. They can be used to enforce domain integrity with default and check constraints, as with defaults and rules, or referential integrity, with primary keys and foreign keys. You can also enforce entity integrity with unique constraints or primary keys.

This can be a major improvement over defaults and rules. There's no need to keep a separate set of objects to be used with your tables and no need to keep track of bindings. Constraints are stored in the sysreferences, syscomments, and sysobjects system tables, and possibly the sysforeignkeys table, in each database.

Now examine the syntax of constraints as an extension to the CREATE TABLE and ALTER TABLE statements.

CREATE TABLE [database.[owner].]table_name

({col_name column_properties [constraint]

[[,] {next_col_name | next_constraint}...])
[ON filegroup]
[TEXTIMAGE_ON filegroup]

This is the same code you worked with on Day 9, except now you will focus on where it says constraint. Constraints are of the form

[CONSTRAINT <name>] <Type of Constraint> [<Constraint Options>]

Here's the full syntax of the constraint option. (Don't worry—it's broken down in great detail later in today's lesson.)

<column_constraint> ::= [CONSTRAINT constraint_name]
[WITH [FILLFACTOR = fillfactor] ]
[ON {filegroup | DEFAULT} ]]
| [ [FOREIGN KEY] REFERENCES ref_table [(ref_column) ]
| CHECK [NOT FOR REPLICATION] (logical_expression)
 [ ...n]
<table_constraint> ::= [CONSTRAINT constraint_name]
{ ( column[,...n] ) }
[ WITH [FILLFACTOR = fillfactor] ]
[ON {filegroup | DEFAULT} ]]
| FOREIGN KEY [(column[,...n])] REFERENCES ref_table [(ref_column[,...n])]
{ [ALTER COLUMN column_name
{[ new_data_type [ (precision[, scale] ) ] [ NULL | NOT NULL ] ]
| ADD { [ <column_definition> ]| column_name AS computed_column_expression
| [ <table_constraint> ] } [,...n]
| DROP { [CONSTRAINT] constraint_name | COLUMN column } [,...n]
| {CHECK | NOCHECK} CONSTRAINT {ALL | constraint_name[,...n]}
| {ENABLE | DISABLE} TRIGGER {ALL | trigger_name[,...n]} } }

Note that this is a simplified syntax.

There are two forms of constraints: column level and table level. Column-level constraints are applied at the column level of the create table, and table-level constraints are added as if they were additional columns. Examples are the easiest way to differentiate between them.

Column-Level Constraints

(col1 int not null CONSTRAINT DF_a_col1 DEFAULT (0))

Table-Level Constraints

(col1 int not null)


Notice the FOR col1 option, specifying to which column the default applies. This is implied during a column-level constraint.

Default Constraints

Default constraints are very much like SQL Server defaults. However, default constraints only apply to columns—never to user-defined data types. You cannot apply default constraints to columns that are also identity columns. You also cannot use default constraints with columns defined with the timestamp data type. The difference here is that the default is "part" of the column, as opposed to having to be bound to the column. They are enforced during inserts only—just as SQL Server default objects are.

Column level:

[CONSTRAINT constraint_name] DEFAULT {constant_expression}

Table level:

[CONSTRAINT constraint_name] DEFAULT {constant_expression} FOR col_name

The CONSTRAINT constraint_name part of the syntax is optional. This is the part that identifies that you are adding a constraint explicitly (always a good idea from a documentation perspective), as well as giving the constraint a name. If you do not name a constraint, it ends up with a name like this:


Therefore, it's a good idea to name them because you'll see in a bit there are some operations you might want to run that require you to name the constraint you're working with.

The keyword DEFAULT is next, then either a constant appropriate for the data type, NULL, or a niladic function. Niladic functions include

  • CURRENT_TIMESTAMP— Gets the current date and time; equivalent to SELECT getdate()
  • SYSTEM_USER— Gets the current login name; equivalent to SELECT suser_sname()
  • CURRENT_USER— Gets the current database username; equivalent to SELECT user_name()
  • USER— The same as CURRENT_USER

Functionally speaking, there is no difference here between table-level and column-level default constraints other than that default constraints must be specified as column level during a create table, and table- or column-level constraints during an alter table.

Here are a couple of examples to show default constraints:

CREATE TABLE defaulttab1
( intcol int NOT NULL CONSTRAINT df_intcol DEFAULT 0,
  char5col char(5) NOT NULL DEFAULT 'Hello',
  anumber numeric(10,0) NOT NULL

Note that the first constraint is named, but the second one is not; therefore it will have a system-assigned name:

ALTER TABLE defaulttab1
ADD moneycol money NULL CONSTRAINT df_moneycol DEFAULT $2.00,
CONSTRAINT df_anumber DEFAULT 100 FOR anumber

Run sp_help defaulttab1 to verify that the constraints are properly on the table, and you will see something like this in the constraint section of the report:

constraint_type            constraint_name               constraint_keys
DEFAULT on column char5col DF__defaultta__char5__6F9499E4     ('Hello')
DEFAULT on column anumber  df_anumber                         (100)
DEFAULT on column intcol   df_intcol                          (0)
DEFAULT on column moneycol df_moneycol                        (2.00)

Notice the system-assigned name for the default constraint you didn't name.

Check Constraints

Check constraints function very much like rules. They provide a mechanism to enforce domain integrity for your columns. Unlike other ANSI constraints, you can have as many check constraints as you want on a single column. They have many of the same restrictions as default constraints, such as with columns' timestamp data type or the identity property. They are checked during inserts and updates, just as rules are.

However, check constraints can do something that rules cannot. Check constraints can refer to other columns as part of their enforcement of conditions. You can do this only with table-level constraints, however.

Column level:

[CONSTRAINT constraint_name]

Table level:

[CONSTRAINT constraint_name]


  • CONSTRAINT constraint_name is optional, just as with default constraints.
  • CHECK specifies that you are creating a check constraint. The expression can be any expression—just as with rules. However, now you can also reference other columns within the same table.
  • NOT FOR REPLICATION prevents the check constraint from being enforced when the internal replication process inserts or updates the table.

As noted previously, only table-level constraints can have references to multiple columns.

Examples are probably the best way to see how they would work.

CREATE TABLE checktable
(col1 int not null CONSTRAINT ck_col1
     CHECK (col1 between 1 and 100),
 col2 char(5) null,
 zip_code char(5) null,
 col4 int not null,
 CONSTRAINT ck_col4 CHECK (col4 > col1),
(zip_code like '[0-9][0-9][0-9][0-9][0-9]')

ALTER TABLE checktable
ADD CONSTRAINT ck_col2 CHECK (col2 like 'H%')

Note that the rules will now be enforced. For example,

INSERT checktable VALUES (1,'Howdy','99901',2)

works, but

INSERT checktable VALUES (2,'Howdy','8834A',3)

will fail with the message

Server: Msg 547, Level 16, State 1
INSERT statement conflicted with COLUMN CHECK
constraint 'ck_zip_code'. The conflict occurred in database 'master',
table 'checktable', column 'zip_code'. The statement has been aborted.

Primary-Key Constraints

Primary-key constraints are used for a combination of referential integrity and entity integrity. Every column used for a primary key must be defined with the NOT NULL attribute, and only one primary-key constraint can exist on a single table. The primary-key constraint might be referenced by foreign-key constraints. Some processes, such as replication or open database connectivity (ODBC) applications, might require declared ANSI primary keys.

Primary-key constraints are an implied creation of a unique index. By default, a unique clustered index is created.

Column level:


    = fillfactor] ][ON { filegroup | DEFAULT} ]

Table level:

[CONSTRAINT constraint_name] [  PRIMARY KEY [ CLUSTERED | NONCLUSTERED] { ( column[,...n]

    ) } [ WITH [FILLFACTOR = fillfactor] ]  [ON { filegroup | DEFAULT} ]


  • CONSTRAINT constraint_name is again optional.
  • PRIMARY KEY creates the primary key (unique) index. Any index options are valid here, including changing the index to nonclustered, applying fill factors, and so on. By default, this option creates a clustered index.

There is no functional difference here per se between column-level and table-level constraints. However, with a column-level constraint the col_name parameter is optional. If you don't specify it, it's assumed to be on the column you put the constraint with.

You can create a primary key on a single column or on up to 16 columns, as long as the total width of the columns doesn't exceed 900 bytes.

(col1 int not null CONSTRAINT pk_col1 PRIMARY KEY,
 col2 char(5) null

This creates a unique clustered index on col1 of table pktable.

(col1 int not null CONSTRAINT pk2_col1
     PRIMARY KEY nonclustered (col1),
 col2 char(5) null

This creates a unique nonclustered index on col1 of table pktable2.

(col1 int not null,
 col2 char(2) not null,
 col3 int null,
 CONSTRAINT pk3_col1col2 PRIMARY KEY (col1, col2)

This creates a unique clustered index on (col1, col2) of table pktable3.

In all instances, you will be able to view the index but won't be able to manipulate it directly. If you attempt to drop the index, for example, on the last table, you will get an error such as this:

Server: Msg 3723, Level 16, State 1
An explicit DROP INDEX is not allowed on index 'pktable3.pk3_col1col2'. It is being used

    for PRIMARY KEY constraint enforcement.

Unique Constraints

Unique constraints enable you to create unique indexes, just as primary keys can, but with a bit more flexibility. You would typically create unique constraints if you have more than one column or set of columns that could be valid primary keys. This serves two purposes: it documents the potential key choices and allows foreign keys on other tables to reference the unique constraints (in addition to being allowed to reference primary-key constraints).

Unique constraints can also be created on columns that allow nulls. You can also have more than one unique constraint on a table.

Column level:


    fillfactor] ] [ON { filegroup | DEFAULT} ]

Table level:

[CONSTRAINT constraint_name] [  PRIMARY KEY [ CLUSTERED | NONCLUSTERED] { ( column[,...n]

    ) } [ WITH [FILLFACTOR = fillfactor] ]  [ON { filegroup | DEFAULT} ]

Just as before, the name of the constraint is optional. Also, just as with primary-key constraints, at the column level you don't have to list any columns. It will assume just the column you create the constraint on otherwise.

Here's a quick example:

CREATE TABLE myuniquetable
(col1 int not null CONSTRAINT pk_myuniquetable PRIMARY KEY,
 col2 char(20) NOT NULL CONSTRAINT u_myuniquetable UNIQUE

This creates a primary key as well as a unique constraint. Both are unique indexes on table myuniquetable.

Foreign-Key Constraints

Foreign-key constraints protect referential integrity between tables. You create a foreign key on a table, which references another table's primary-key or unique constraint. This restricts data modifications against the table with the primary key as long as there are related rows in the tables with the foreign keys. It also prevents data from being added (or updated) on the table with the foreign-key constraint that would not contain valid data from the referenced tables.

Creating a foreign key does not create an index on the table; however, it's likely that this is a good candidate for an index. Therefore, you will typically need to follow your creation of tables with foreign keys with CREATE INDEX statements. You can refer to tables in the same database only when creating foreign-key constraints. You must have the appropriate permission (select or references) on the table you refer to, and any single table can have a maximum of 63 foreign keys pointing to it. There is no way to extend this limit.

Column level:

[CONSTRAINT constraint_name] [FOREIGN KEY] REFERENCES ref_table     [ ( ref_column ) ]


Table level:

[CONSTRAINT constraint_name]  FOREIGN KEY [(column[,...n])]     REFERENCES ref_table [

   (ref_column[,...n])]     [NOT FOR REPLICATION]
    | CHECK [NOT FOR REPLICATION] (search_conditions)

Again, the constraint name is optional. As with the other referential constraints, you don't have to have the column name referenced locally if it's a single-column constraint. Also, you don't have to name the column on the other table if the columns have the same name.

If you reference a multiple-column primary-key/unique constraint, you must be careful to reference it in the same order between your column list in the FOREIGN KEY list and the REFERENCES list. Self-references are supported, so you could reference the table to itself (with another column).

In Listing 14.3, you will create an employee table and an order table (which was entered by an employee). To verify that a valid employee entered the order, you could either program the functionality or declare it with foreign keys. Then, when someone tries to delete an employee, the individual wouldn't be allowed to do so as long as there are orders for that employee.

Example 14.3. Setting Constraints on a New Table

(emp_id int not null CONSTRAINT pk_emp PRIMARY KEY,
 emp_name char(30) not null)
(order_id int not null CONSTRAINT pk_order PRIMARY KEY,
 emp_id int not null CONSTRAINT fk_order
FOREIGN KEY (emp_id) REFERENCES emp (emp_id)
INSERT emp VALUES (1,'Joe Smith')
INSERT emp VALUES (2,'Ann Jones')
INSERT orders VALUES (1,1)
INSERT orders VALUES (2,2)

All this works fine so far. Now try to insert an order for an employee that doesn't exist.

INSERT orders VALUES (3,3)

Server: Msg 547, Level 16, State 1
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'fk_order'. The conflict occurred in database 'pubs',
table 'emp', column 'emp_id'.
The statement has been aborted.

OK, now try to delete an employee that has an order.

   DELETE emp WHERE emp_id = 1


Server: Msg 547, Level 16, State 1
DELETE statement conflicted with COLUMN REFERENCE constraint
'fk_order'. The conflict occurred in database 'pubs',
table 'orders', column 'emp_id'. The statement has been aborted.

An example of the self-referencing behavior is something like this:

CREATE TABLE emp_manager
(emp_id int not null CONSTRAINT pk_emp_mgr PRIMARY KEY,
 mgr_id int not null CONSTRAINT fk_emp_mgr FOREIGN KEY
REFERENCES emp_manager (emp_id),
  emp_name char(30) not null)

This means that every manager must also be a valid employee.

INSERT emp_manager VALUES (1,1,'Ann Jones')
INSERT emp_manager VALUES (2,1,'Tom Smith')

This works fine, but now try to reference someone who doesn't exist yet

INSERT emp_manager VALUES (3,4,'Bob Newett')

and you get a similar message as before—that the foreign-key constraint was violated. This can be very useful in many real-world scenarios.

As you can see, foreign keys are quite powerful. However, they force you to use a database in a particular fashion—the delete restrict functionality. Delete restrict means that deletes of primary-key (or unique-constraint) rows are not allowed if any foreign keys point to them. You must first delete any rows on the tables with the foreign-key references before you can delete the primary-key rows.

Another way you might want to deal with data is with what is known as delete-cascade functionality. This functionality would imply that the previous DELETE from the emp table would have also deleted all related rows in the orders table. It's somewhat unlikely that you would use this functionality in most business environments, and far more likely that you'd do something like set an inactive status flag for the employee and not delete all orders taken by that employee when the individual leaves the company. SQL Server does not implement this functionality with declarative referential integrity; you must program it yourself.

Dropping Constraints

You can drop a constraint with the ALTER TABLE statement. For example, to drop the foreign-key constraint in our last example, run the following and the foreign-key constraint is dropped:

ALTER TABLE emp_manager DROP CONSTRAINT fk_emp_mgr

However, if you try to drop a primary-key constraint (or unique constraint) that still has foreign-key references, you will not be able to do so. For example, on your emp_manager table, if you try to drop the primary-key constraint with this code (without having dropped the foreign key):

ALTER TABLE emp_manager DROP CONSTRAINT pk_emp_mgr

you would get this error message:

Server: Msg 3725, Level 16, State 1
The constraint 'pk_emp_mgr' is being referenced by table 'emp_manager',  foreign key

    constraint 'fk_emp_mgr'.
Could not drop constraint. See previous errors.

+ Share This