Alter table & Add UNIQUE key results in an error

Question

I have a table called Animal. AnimalId is the primary key & I wanted to set the column AnimalType_id as UNIQUE (I have an AnimalType table and need to set a foreign key here)

ALTER TABLE Animal
ADD UNIQUE Animal.AnimalType_id int

There already is data in both tables, because of that I can't drop the table.

This however results in an error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.

Answer 1

See the documentation for how to add a table constraint.

ALTER TABLE Animal ADD CONSTRAINT UQ_Animal_AnimalTypeId UNIQUE (AnimalType_id)

It sounds like AnimalType_id is a foreign key so I just wanted to check you understood that by making this column unique, you're making the relationship one-one - you'll only be able to have one animal of each type.

Since you're getting an error adding the unique constraint, I'm going to suggest that you actually want a foreign key instead of a unique constraint:

ALTER TABLE Animal
    ADD CONSTRAINT FK_Animal_AnimalType
    FOREIGN KEY
    (
        AnimalType_id
    )
    REFERENCES AnimalType
    (
        id
    )

I've had to guess at the name of the AnimalType table name and it's primary key column name - please change these if they are incorrect.

Answer 2

If you get into the habit of giving names to all objects (even constraints) that you create, you will have easier time later when you need to disable, drop, or alter the constraint:

ALTER TABLE Animal ADD CONSTRAINT UQ_Animal_Type UNIQUE (AnimalType_id)

It is also possible to get a more flexible constraint-like effect from creating a unique index.