Solved

sql server prevent duplicate combination of two columns

Posted on 2013-01-12
7
911 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
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 500 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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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 500 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now