Alter table & Add UNIQUE key results in an error

问题:

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

回答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.

回答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.

回答3:

I think you are trying to do this:

ALTER TABLE Animal
ADD COLUMN AnimalType_id int;

回答4:

It seems the data in the column is not unique. when you create a unique constraint on a column, you can not have any duplicate entries (you can have at most one null)

回答5:

try this

ALTER TABLE table_name ADD CONSTRAINT UNIQUE (column_name)