?
Solved

sql server prevent duplicate combination of two columns

Posted on 2013-01-12
7
Medium Priority
?
1,097 Views
Last Modified: 2013-01-27
I have a table with several columns.  I want to prevent a user from entering a duplicate combination of two columns

StartDate, Strategy
1/1/2012, strategy1
1/1/2012, strategy2
1/2/2012, strategy1
1/1/2012, strategy1  -- I want to prevent this because the first one is the same

I did this using code first entity framework.  How can I add this to my code first class and also to an existing table?
0
Comment
Question by:jackjohnson44
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38770909
I won't claim that this is the most elegant way of handling it but the code works.

(I declared the table to have a column called strategy with definition char(50))

declare @start datetime
declare @strat char(50)

set @start = '20120101'
set @strat = 'strategy1'

if not exists (select 1 from taba where StartDate = @start and Strategy = @strat)
   insert into mytable values (@start, @strat)

Open in new window


From a non code perspective, if the columns really are supposed to be unique, why not create a unique index on the fields?  Of course, then you'd have to trap a duplicate key condition on the insert.
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 38771079
You can create a unique key on the column combination i.e. StartDate, Strategy. This will not allow any duplicates at the backend. But you need to handle the error due to unique key in your program. We can help more here if we need your real requirement. I think you might be needing an extra data table here. It would be easier if you explain more on your requirement.
0
 
LVL 9

Accepted Solution

by:
sognoct earned 2000 total points
ID: 38771477
to prevent db duplicates you have to declare on sql server the constraints, it can be a primary key constraint if the two columns represent the keys of the table OR you can define  UNIQUE constraint "when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key."

This is the sql part, for the business logical part instead there can be a control that prevents user from inserting duplicates, also datatable can implement constraint mechanism :
datatable constraint samples

combine the two parts, add a try catch on the insert method for managing errors on dulicates  and everithing should work fine
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 38781708
create unique index on those columns in that you wish to be unique values.
0
 

Author Comment

by:jackjohnson44
ID: 38795641
Thanks, I guess I was asking how do I create a unique index on those columns using entity framework code first
0
 
LVL 9

Assisted Solution

by:sognoct
sognoct earned 2000 total points
ID: 38795998
Entity Framework does not support Unique constraints. You can only add them to your database using a Query

here there is the sintax :
http://msdn.microsoft.com/en-us/library/aa224827(v=sql.80).aspx

it is just an

ALTER TABLE tablename
  ADD CONSTRAINT constraint_name
  UNIQUE (
    Col1,
    Col2
)
0
 

Author Comment

by:jackjohnson44
ID: 38816191
Thanks!

I am currently inserting one record at a time, and I do a query to see if the row already exists.  In other areas of my code, I create a tmp datatable object (C#) then use sql bulk copy for the insert.

So I want to change from
if(checkToSeeIfFileExists(row))
  insertFile(row)

into
datatable.add(row)
datatable.add(row)
datatable.add(row)


sqlBulkCopy(datatable)

if I use constrains, how would I be able to use this with bulk copy?
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question