February 25, 2013 at 6:54 am
How can I accomodate DUP keys where Case Sensitive values should distinguish the 2 rows. Example, I have a table that contains DUP VALUES in PK as:
1234, ABC
1234, abc
In reality (based on business rule), these 2 rows represent 2 distinct business entities. Using the following table DDL and Proc, I am receiving a PK VIOLAION when I attempt to INSERT the 2 rows above.
USE [myDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[myTABLE](
[COL_1] [int] NOT NULL,
[COL_2] [char](3) NOT NULL,
[COL_3] [varchar](12) NULL
CONSTRAINT [PK_myTABLE] PRIMARY KEY CLUSTERED
( [COL_1] ASC,
[COL_2] ASC )
CREATE PROCEDURE [dbo].[myProc] as
INSERT INTO myTABLE ([COL_1],[COL_2],[COL_3])
SELECT t.[COL_1],t.[COL_2],t.[COL_3]
FROM myTABLE_TEMP t
LEFT OUTER JOIN myTABLE o on o.[COL_1] = t.[COL_1] and o.[COL_2]collate Latin1_General_BIN = t.[COL_2] collate Latin1_General_BIN
WHERE o.[COL_1] is null;
How can I allow both rows to be inserted? thanks for your help.
February 25, 2013 at 7:09 am
What is the collation of the database/table/column? As that is what will be driving this to cause an error.
if you set the column Col2 to a Case Sensitive collation then what happens, eg
CREATE TABLE [#myTABLE2](
[COL_1] [int] NOT NULL,
[COL_2] [char](3) COLLATE Latin1_General_CS_AS NOT NULL,
[COL_3] [varchar](12) NULL
CONSTRAINT [PK_myTABLE2] PRIMARY KEY CLUSTERED
( [COL_1] ASC,
[COL_2] ASC ));
Insert into #myTABLE2
Values (1,'abc',NULL)
,(1,'ABC',NULL)
This will not give a duplicate key error, as COL_2 has been defined as Case Sensitive.
I wouldn't recommend doing it this way unless you absolutely have to, and even then I'd be very wary, it would be better to build your database in a Case sensitive collation from the start.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 25, 2013 at 7:27 am
- SQL Server default Collation --> SQL_Latin1_General_CP1_CI_AS
- Database has no explicit colation defined
- Table has no explicit collation defined
The PROC which Inserts into the table does have a collation specified which is: collate Latin1_General_BIN
CREATE PROCEDURE [dbo].[myProc] as
insert into myTABLE ([COL_1],[COL_2],[COL_3])
select t.[COL_1],t.[COL_2],t.[COL_3]
from myTABLE_TEMP t
left outer join myTABLE o on o.[COL_1] = t.[COL_1] and o.[COL_2]collate Latin1_General_BIN = t.[COL_2] collate Latin1_General_BIN
where o.[COL_1] is null;
February 25, 2013 at 7:45 am
If the Database had no collation specified when it was created then it will inherit the Server collation in this case its Case Insensitive so you have to manually set the collation on the column COL2 to make it case sensitive.
Depending on where you are in the lifecycle you might be better re-creating the database with a case sensitive collation, but if its a mature database then that isn't really an option.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 25, 2013 at 7:57 am
thanks for this feedback.. yes, were a few years into this DB's maturity.. heavily used so I'll most like alter the column's collation attribute based on this link:
http://msdn.microsoft.com/en-us/library/ms190920(v=sql.105).aspx
which entails temporarily dropping the PK and probably this tables (article) involvement in replication as well
thx again
February 25, 2013 at 8:07 am
No problem.
My main concern is that this isn't necessarily a small change, as it wil have an impact on any query, SP, Function that uses this as a join, as they will ann need to be refactored, even down to the child table that has the COL2 reference in as that will need to be changed to the same collation.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply