unique fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

unique fields

Author

Topic

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-05-16 : 13:51:50

have a table with 2 fields i want them to be unique. idont want duplicate.

how will i do it

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-16 : 13:57:13

Make the combination a Primary Key
U can do this by going to Enterprise manager --> tables --> table --> design

Srinika

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-05-16 : 13:59:59

i want to do it from qa

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-16 : 14:17:22

How I did is:

Did that in EM. Then Script it as "Create" in QA


EDIT : CREATE TABLE ttt(x INT , y int PRIMARY KEY)
CREATE TABLE [ttt] (
	[x] [int] NOT NULL ,
	[y] [int] NOT NULL ,
	CONSTRAINT [PK__ttt__38B00FFC] PRIMARY KEY  CLUSTERED 
	(
		[x],
		[y]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

Srinika

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-05-16 : 15:11:00

i mean i dont want to insert duplicate rows in the field.

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-05-16 : 15:54:09

if you don't want to insert dupe and considering the table structure above, you can use an SQL similar to the one below:

INSERT INTO NO_DUPLICATE_TABLE(
x,
y
)
SELECT
DISTINCT
x,
y
FROM NO_DUPLICATE_SOURCE_TABLE ND
WHERE
NOT EXISTS(
SELECT 1
FROM NO_DUPLICATE_TABLE
WHERE X = ND.X
and Y = ND.Y
)
GO

if you want to ENFORCE NO DUPLICATE IN THE TABLE, place a UNIQUE or PRIMARY constraint in the table. You can also use UNIQUE INDEX.

May the Almighty God bless us all!

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-16 : 16:14:59

funketekun,

Whatever u want to do, do in the enterprise manager (in a test system)
Then Script that table to get the Query.
Modify that according to ur real need.
Run it in the QA.

Srinika

X002548
Not Just a Number

15586 Posts

Posted - 2006-05-16 : 16:38:36

quote:
Originally posted by funketekun

i mean i dont want to insert duplicate rows in the field.

What do you consider a duplicate to be?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-05-16 : 19:07:08

quote:
Originally posted by Srinika

Make the combination a Primary Key
U can do this by going to Enterprise manager --> tables --> table --> design

Srinika

I'd put a unique constraint on the columns rather than a PK. You can only have one primary key and most tables will already have it. PKs generally have some kind of other meaning.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-16 : 21:18:12

quote:
Originally posted by LoztInSpace

quote:
Originally posted by Srinika

Make the combination a Primary Key
U can do this by going to Enterprise manager --> tables --> table --> design

Srinika

I'd put a unique constraint on the columns rather than a PK. You can only have one primary key and most tables will already have it. PKs generally have some kind of other meaning.

Agreed :
Read my last answer

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources