Solved

sql server prevent duplicate combination of two columns

Posted on 2013-01-12
7
938 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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 Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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