jackjohnson44
asked on
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?
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?
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
create unique index on those columns in that you wish to be unique values.
ASKER
Thanks, I guess I was asking how do I create a unique index on those columns using entity framework code first
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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(
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?
(I declared the table to have a column called strategy with definition char(50))
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.