unique ("M" Keywords)

[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]

The unique keyword declares that a particular field or set of fields is unique within a collection. The unique constraint can be placed directly on an entity declaration or it can be placed on an extent that contains the entity.

Syntax

unique field name

or

unique **(**field name1, field name2,…. field nameN)

The unique constraint is specified as one of the clauses in a where constraint. If the unique constraint is the last clause in the where constraint, then it must be terminated with a ;.

Examples

Single Unique Field

The following shows how to specify a single field as unique. It stipulates that the Name field within each member of the Categories collection is distinct.

Categories : Category* where unique Name;

Set of Unique Fields

If multiple fields are not individually unique, but taken together are unique, this can be stated by listing the fields in a unique constraint. In the following, the contacts collection has multiple people with the same name and multiple people can live at the same address, however, it is not possible to store the same address for the same person twice.

module Contacts
{
    type Contact 
    {
        Person : Text#128;
        Address : Text#128;
        Phone: Text;
    }
    Contacts1 : Contact* where unique (Person, Address);
}

Constraint on the Entity

The unique constraint can be placed directly on an entity declaration.

module Contacts
{
    type Contact 
    {
        Person : Text#128;
        Address : Text#256;
    } where unique Person;
    
    Contacts2 : Contact*;
}

This is equivalent to placing the constraint on each specific collection the entity is a member of. Placing the constraint on the entity (rather than on the collections the entity is a member of) only implies uniqueness within individual collections, not uniqueness across all possible collections. So, for example, Contacts2 can contain elements with the same value in the Name property as Contacts1 does.

Differences between the Unique and Identity Constraints

The two constraints both require that no duplicate values exist for the specified field or fields. However, the unique constraint permits one entity in the extent to have a null value for the specified field, whereas this is not allowed for the identity constraint.

Unique Value

Collections in “M” can contain multiple copies of the same element. The constraint unique value specifies that there are no duplicate elements in a collection.

The following code ensures that every member of Contacts is unique, in that there is never more than one name living at an address with the same phone number.

module Contacts
{
    type Contact 
    {
        Name: Text;
        Address : Text;
        Phone: Text;
    }
    Contacts : Contact* where unique value;
}