how to enforce multiple unique fields in database table

Question

i have a table called Cars and the primary key of the table is 'id'. I also have a field called 'name'. I would like to make sure no one enters the same name twice even though it wont break my db integrity.

what is the best way of doing this?

Answer 1

Just create a UNIQUE index on the name field. In SQL it would be something like this:

CREATE UNIQUE INDEX IX_Cars_name 
ON Cars(name);

Another possibility is to create a unique constraint, like this:

ALTER TABLE Cars
ADD CONSTRAINT Uqc_Cars_name 
UNIQUE (name)

Both will do essentially the same thing.

Answer 2

create unique index cars_unique_name on cars(name)

Answer 3

you can specify the unique index constraint both in CREATE TABLE command or ALTER TABLE command.

Its like

CREATE TABLE CARS ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) UNIQUE KEY );

or just create the above table

CREATE TABLE CARS ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) ); and later add the unqiue key index using alter table

Answer 4

As mentioned, you will probably want a unique index, but it is also possible to use a unique constraint, with no index which might be desirable in some situations...

CREATE TABLE cars
(
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    NAME NVARCHAR(100) UNIQUE NOT NULL
)

or

ALTER TABLE cars  
ADD CONSTRAINT UniqueConstraintName UNIQUE (name)