Requirement
-------------------
Database: SQL Express 2005.

There are around 7 columns in an SQL database table that can only
contain unique values as long as the value in another column remains
constant.
To elaborate the above line consider the following example.

I create an order for a customer account. A customer account can have
multiple orders in the system.
I have a database table called Orders where the order no, the customer
account no. and details of the order are stored. Total of about 25
columns in this table.
There are around 7 columns in this Orders table that contain the
specifics of the order.
For the same customer account I should not be able to put in multiple
orders into the system where these 7 columns are the same.
Note that 6 out of the 7 columns can be same and it is OK. But all  7
should not be the same.

Well that is not too difficult to achieve. 
I set-up a unique key in the orders table and I select the combination
of  columns that should be unique. The database tests for this
automatically as a new record is inserted into the table.
Works fine till now, as long as I am entering in new orders.

But I run it problems when I have to copy an existing order that
belongs to the same account and make modifications to it.
When I copy, values in the 7 columns already exist and get copied
across. The database throws up a unique constraint violated error as
it tries to insert this new record.
So I am unable to copy. I have to re-enter everything even if I need
to change only a few details in the order.

I am using the database as a back end to a software application and we
are trying to achieve this without doing any coding in the program.
We are hoping we can do something in SQL to resolve this.

I am hoping for not just ideas but some sample/complete code on how it
can be achieved.
I only know the very basics in SQL so the more detailed the response the better.

To summarize, here is a scenario.
I am using my application with SQL as the backend.
I create new orders in my application for a specific customer account.
 I input values into various fields in my application which get
written into the database.
There are a set of  7 values (can be text or numbers) which cannot be
same for 2 orders belonging to the same account.
When I am creating new orders my unique key prevents me from saving an
order where these 7 values match with values in an earlier order.
When I am try to copy an existing order I am unable to do so because
the database does not allow the 7 values to be same. I need the
database to give me a chance to change them in my application before
it throws the error.
Again I realize something in my application can be changed to allow
this but I do not think that wil happen. Which is why I am trying to
find out if any facility in the database can help me with it.

I will not be able to provide additional information on how the
application talks to the database because I do not know.This is
because I am the sales guy and my programmers are telling me there is
no nice way to achieve this and I wanted to just double-check.

If further clarifications are required to understand the requirement
better please let me know.

---

Request for Question Clarification by hammer-ga on 30 Apr 2006 12:59 PDT

What are the steps you do to copy an order?

- Hammer

Could you:
- select the data into a temp table;
- update the data in the temp table;
- select back into the Orders table?

Select field1, field2, etc
into #mytemptablename
from Orders
where match1=match2...

update #mytemptablename
set myfieldthatIwillchangetomakeunique='blah'

Select field1, field2, etc
into Orders
from  #mytemptablename

Hope this helps,
LP