I have a table called CustomerMemo:
CustomerMemo
CustomerID
MemoID
Both of these are foreign keys. The columns are not unique because there could be something like this:
CustomerID MemoID
----------- -------
1 1
1 2
1 3
However, what I want to avoid is something like this:
CustomerID MemoID
----------- -------
1 1
1 1
Anyone have a clue how to do this in SQL Server?
If you actually want to ignore duplicate keys upon insert, then you'll need to use the IGNORE_DUP_KEY index option in your index or unique constraint definition.
Here is the documentation on MSDN:
Example from that article (in section D. Using the IGNORE_DUP_KEY option):
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
WITH (IGNORE_DUP_KEY = ON);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO
For your table, this would be the command:
CREATE UNIQUE INDEX UNQ_CustomerMemo ON CustomerMemo (MemoID, CustomerID)
WITH (IGNORE_DUP_KEY = ON);
The disadvantage to using IGNORE_DUP_KEY is that you lose visibility on what data is violating the unique constraint. Generally it is better to ensure the data is unique before inserting and then when you do have something fall through the cracks, you will get the error, along with the values that violated the unique constraint. This will allow for much easier troubleshooting of your insert statement. That being said, I make liberal use of this option when defining table variables because the scope is limited.
As for whether or not you should use a unique key or a unique index, see the following question on stack overflow: Unique key vs. unique index on SQL Server 2008
You want to use the DISTINCT keyword in your select.
Or, if you want to prevent there ever being a record with those same keys, you want a UNIQUE constraint on the CustomerID and MemoID
ALTER TABLE CustomerMemo
ADD CONSTRAINT [uc_CustomerMemo] UNIQUE(CustomerID, MemoID)
Another alternative is to make the primary key on the CustomerMemo a composite primary key on the CustomerID and MemoID
ALTER TABLE CustomerMemo
ADD CONSTRAINT pk_CustomerMemo PRIMARY KEY(CustomerID, MemoID)
Either set up a unique composite key that includes both CustomerID and MemoID, or make your primary key a composite of both CustomerID and MemoID. This will ensure you cannot insert duplicates like that.
Please check out this thread: How can I create a SQL unique constraint based on 2 columns?
f.e. in sql server 2005 you should use:SQL Server 2005 Unique constraint on two columns
You should look for a phrase: UNIQUE CLUSTERED connected with your db implementation.
