unique and primary key Constraints

You can declare unique or primary key constraints to ensure that no two rows in a table have the same values in the specified columns.

Both constraints create unique indexes to enforce this data integrity. However, primary key constraints are more restrictive than unique constraints. Columns with primary key constraints cannot contain a NULL value. You normally use a table’s primary key constraint with referential integrity constraints defined on other tables.

The definition of unique constraints in the SQL standard specifies that the column definition shall not allow null values. By default, SAP ASE defines the column as not allowing null values (if you have not changed this using sp_dboption) when you omit null or not null keywords in the column definition. In Transact-SQL, you can define the column to allow null values along with the unique constraint, since the unique index used to enforce the constraint allows you to insert a null value.

Note: Do not confuse the unique and primary key integrity constraints with the information defined by sp_primarykey, sp_foreignkey, and sp_commonkey. The unique and primary key constraints actually create indexes to define unique or primary key attributes of table columns. sp_primarykey, sp_foreignkey, and sp_commonkey define the logical relationship of keys (in the syskeys table) for table columns, which you enforce by creating indexes and triggers.

unique constraints create unique nonclustered indexes by default; primary key constraints create unique clustered indexes by default. You can declare either clustered or nonclustered indexes with either type of constraint.

For example, the following create table statement uses a table-level unique constraint to ensure that no two rows have the same values in the stor_id and ord_num columns:

create table my_sales
(stor_id     char(4),
ord_num      varchar(20),
date         datetime,
unique clustered (stor_id, ord_num))

There can be only one clustered index on a table, so you can specify only one unique clustered or primary key clustered constraint.

You can use the unique and primary key constraints to create unique indexes (including the with fillfactor, with max_rows_per_page, and on segment_name options) when enforcing data integrity. However, indexes provide additional capabilities.