Avoiding duplicate values in database

[arch_mah]

I want to insert number for City_Id and City_name , i wrote a query for auto generate number for City_Id,working fine but in the city name i want to avoid inserting duplicate value like if Mumbai is a City ihave already saved then system should not allow that name again. neither in small or in capital or mixed letter.

Can anybody help me..

Thanks In Advance
Archana

[hirenpatel53]

u can use primary key or unique key for that

[arch_mah]

Thanks for your reply.

I have tried to do so but It is not allowing me to do two primary key in that table .City_id i have set as a primary key.

And If cities are in lower and upper case then where should i write some constraints.

Acctually i am new to dotnet,so please guide me.
this is my script.
USE [icms]
GO
/****** Object: Table [dbo].[CITY_MASTER] Script Date: 06/30/2010 12:55:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CITY_MASTER](
[CITY_ID] [int] IDENTITY(1,1) NOT NULL,
[CITY_NAME] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_CITY_MASTER] PRIMARY KEY CLUSTERED
(
[CITY_ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

[G_Waddell]

Hi,

What I would do is before your are going to Insert your new city record check for matching records first, if it is clear insert, if not then query the user:

strSQL="SELECT COUNT(*) FROM CITY_MASTER WHERE LOWER(CITY_NAME) = '" &lcase(MyCity) &"'"
.....
If CityCount > 0 then
  msgbox ("Entry already exists for " &MyCity &".")
Else

 strSQL ="INSERT INTO...."

End if

[arch_mah]

Thanks a lot ,My Problem is solved..