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?
Add a comment
|
4 Answers
create unique index cars_unique_name on cars(name)
answered Mar 27, 2010 at 16:16
Pablo Santa Cruz
182k33 gold badges250 silver badges300 bronze badges
Comments
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.
Comments
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
Comments
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)
