CODEWITHSUNDEEP
sqlsql-servertagsindexing
Neilski
Neilski

Reputation: 4415

How do I enforce uniqueness against four columns

In SQL Server 2012, I have a 'cross-reference' table containing four columns. The combination of the four columns must be unique. My initial thought was to simply create a a primary key containing all four columns, but some research has suggested that this might not be a good idea.

Background to question... I am trying to implement a tagging service on a legacy web application. Some of the objects that need tagging use a uniqueidentifier as their primary key, whilst others use a simple integer id. I have approached this using a 'two-table' approach. One table contains the tags, whilst the other table provides a reference between the objects to be tagged and the tag table. This table I have name TagList...

CREATE TABLE TagList (
    TagId          nvarchar(40)     NOT NULL,
    ReferenceGuid  uniqueidentifier NOT NULL,
    ReferenceId    int              NOT NULL,
    ObjectType     nvarchar(40)     NOT NULL
)

For example, to tag an object with a uniqueidentifier primary key with the word 'example', the TagList record would look like this:

TagList (
    TagId          'example',
    ReferenceGuid  '1e93d578-321b-4f86-8b0f-32435d385bd7',
    ReferenceId    0,
    ObjectType     'Customer'
)

To tag an object with an integer primary key with the word 'example', the TagList record would look like this:

TagList (
    TagId          'example',
    ReferenceGuid  '00000000-0000-0000-0000-000000000000',
    ReferenceId    5639,
    ObjectType     'Product'
)

In practice, either the TagId and the ReferenceGuid column must be unique or, if an int primary key object is being defined, the TagId, ReferenceId and ObjectType must be unique.

To simplify(?) things, making the combination of all four columns to be unique would also serve the same functional purpose.

Any advice would be appreciated.

Upvotes: 0

Views: 68

Answers (3)

Dipendu Paul
Dipendu Paul

Reputation: 2753

I do not have all the information of the entities involved, but with the limited visibility I have I would try to suggest following first cut design :

Create two separate tables, one with TagId and ReferenceGuid and the other with TagId and ReferenceId. I am not sure of ObjectType though. If ObjectType is not implicit then this too can be maintained in both of these tables. Then a view can be created on top of these tables to shoot queries on which can contain all the required columns. This way we can get around the issue of space wastage in current design. Please give your input if this design doesn't solve the problem in hand.

Upvotes: 1

Jorge Candeias
Jorge Candeias

Reputation: 720

If you only require a unique constraint, and not a primary key, this can be used:

ALTER TABLE TagList
ADD CONSTRAINT UK_TagList_1 UNIQUE
(
    TagId,
    ReferenceGuid,
    ReferenceId,
    ObjectType
)

Upvotes: 1

Lee Mandeville
Lee Mandeville

Reputation: 96

Having a multi column primary key should do the trick

CREATE TABLE TagList (
    TagId          nvarchar(40)     NOT NULL,
    ReferenceGuid  uniqueidentifier NOT NULL,
    ReferenceId    int              NOT NULL,
    ObjectType     nvarchar(40)     NOT NULL,
    CONSTRAINT pk_TagList PRIMARY KEY (TagId,ReferenceGuid,ReferenceId,ObjectType)
)

Upvotes: 2

Related Questions