how to enforce multiple unique fields in database table

i have a table called Cars and the primary key of the table is 'id'. I also have a field called 'name'. I wo开发者_JS百科uld 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?

create unique index cars_unique_name on cars(name)

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.

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

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)