Alter table & Add UNIQUE key results in an error

sql, sql-server, sql-server-2008

Solution

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.

Problem

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 '.'.

Original source