0

I've looked at a few SQL posts already but i'm unsure of their answers.. eg: SQL Unique Key Syntax

I want a Cities table... I want to define my country table whereby the Name column is unique... Using this as the base, how do I define a Unique column?

CREATE TABLE [dbo].[Cities]
(
    [CityID] INT NOT NULL PRIMARY KEY, 
    [Name] NCHAR(100) NULL
)
Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Jimmyt1988
  • 20,466
  • 41
  • 133
  • 233
  • 1
    I don't know any country where the City name is unique (or forced to be unique). You may want to question this requirement. – Raj More Feb 24 '14 at 16:49
  • 2
    Also: please **don't use** `NCHAR(100)` - this will cause you to have **fixed-width**, always 100 characters = 200 bytes long entries - even if you store only 3 or 4 characters.... `(N)CHAR(n)` is great for short codes - like ISO country or currency codes - for anything beyond 5 characters, you should use `(N)VARCHAR(n)` instead! – marc_s Feb 24 '14 at 16:50
  • @Raj More - I have a list of cities going to be added to my table... I don't want multiples added (If there is bad data im taking it from)... That's all :) – Jimmyt1988 Feb 24 '14 at 16:51

2 Answers2

1

You can create a constraint on the table to do this:

ALTER TABLE <tablename> ADD CONSTRAINT
<constraintname> UNIQUE NONCLUSTERED
(
    <columnname>
)
Raj More
  • 47,048
  • 33
  • 131
  • 198
0

You can add the unique keyword:

CREATE TABLE [dbo].[Cities]
(
    [CityID] INT NOT NULL PRIMARY KEY, 
    [Name] NCHAR(100) NULL UNIQUE
);

There are alternative methods. You can add also add a unique index:

create unique index cities_name on dbo.Cities(Name);

Or do it with a unique constraint:

CREATE TABLE [dbo].[Cities]
(
    [CityID] INT NOT NULL PRIMARY KEY, 
    [Name] NCHAR(100) NULL,
    constraint unique_name unique(Name)
);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786