Solved

sql server prevent duplicate combination of two columns

Posted on 2013-01-12
7
1,043 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 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Containers and Docker for Everyone

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

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

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.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

726 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