• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1204
  • Last Modified:

sql server prevent duplicate combination of two columns

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
jackjohnson44
Asked:
jackjohnson44
2 Solutions
 
Steve WalesSenior Database AdministratorCommented:
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
 
TempDBACommented:
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
 
sognoctCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Alpesh PatelAssistant ConsultantCommented:
create unique index on those columns in that you wish to be unique values.
0
 
jackjohnson44Author Commented:
Thanks, I guess I was asking how do I create a unique index on those columns using entity framework code first
0
 
sognoctCommented:
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
 
jackjohnson44Author Commented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now