How to ensure only one copy of record in db

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

SQL Server 2008 Forums

Transact-SQL (2008)

How to ensure only one copy of record in db

Author

Topic

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2011-01-05 : 10:00:15

I have a table like this:

FK1 int
FK2 int

It is used to link together two records from other tables. I do not want duplicates in the table so in the stored procedure if have this:

EXEC RemoveFromTable @FK1, @FK2
EXEC AddToTable @FK1, FK2

This first removes the FK1, FK2 combination if present, then adds it, thus ensuring only one combination of FK1, FK2 in the table.

Well, it does not work. The database remains unchanged. Best I can figure if the Remove is being called after the add, but I thought the calls happened in order.

So I tried this:

EXEC RemoveFromTable @FK1, @FK2
GO
EXEC AddToTable @FK1, FK2

But got an error saying invalid use of GO.

Here are the the queries in the stored procedures:
RemovFromTable: DELETE FROM Table WHERE FK1=@FK1 AND FK2=@FK2
AddToTable: INSERT INTO Table (FK1, FK2) VALUES (@FK1, @FK2)

So my question:

Is this incorrect or do I need to start looking for other reasons why the data is not getting added?

thanks

kpg

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-05 : 10:25:11

Well...have you considered the use of constraints? ->

ALTER TABLE TableName ADD CONSTRAINT unq_TableName UNIQUE (FKCol1, FKCol2)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2011-01-06 : 16:50:13

Um...no, no I have not.

Looks better than what I'm doing though, would an error be thrown if I try to add a 2nd record (I guess I can try and find out).

BTW - I did have an error elsewhere (doh!) that was causing the problem, I;ll give the restraints a go.

kpg

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-06 : 17:14:14

Yes, violation of a UNIQUE constraint would throw an error.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources