Unique and Primary-Key Constraints

Considerations when using unique and primary-key constraints.

  • You can declare unique constraints at the column level or the table level. unique constraints require that all values in the specified columns be unique. No two rows in the table can have the same value in the specified column.

  • A primary key constraint is a more restrictive form of unique constraint. Columns with primary key constraints cannot contain null values.

    Note: The create table statement’s unique and primary key constraints create indexes that define unique or primary key attributes of columns. sp_primarykey, sp_foreignkey, and sp_commonkey define logical relationships between columns. These relationships must be enforced using indexes and triggers.
  • Table-level unique or primary key constraints appear in the create table statement as separate items and must include the names of one or more columns from the table being created.

  • unique or primary key constraints create a unique index on the specified columns. The unique constraint in Example 3 creates a unique, clustered index, as does:
    create unique clustered index salesind
        on sales (stor_id, ord_num)

    The only difference is the index name, which you could set to salesind by naming the constraint.

  • The definition of unique constraints in the SQL standard specifies that the column definition cannot allow null values. By default, the SAP ASE server defines the column as not allowing null values (if you have not changed this using sp_dboption) when you omit null or not null 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.

  • unique constraints create unique, nonclustered indexes by default; primary key constraints create unique, clustered indexes by default. There can be only one clustered index on a table, so you can specify only one unique clustered or primary key clustered constraint.

  • The unique and primary key constraints of create table offer a simpler alternative to the create index statement. However:
    • You cannot create nonunique indexes.

    • You cannot use all the options provided by create index.

    • You must drop these indexes using alter table drop constraint.