February 6, 2012 at 4:57 am
I have a table
CREATE TABLE [dbo].[Airline](
[airlne_cd] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[carrier_cd] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[descr] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Airline] PRIMARY KEY CLUSTERED
)
Airlne_cd is a primary key
below is my insert command
INSERT INTO [Airline]([airlne_cd],[carrier_cd],[descr]) VALUES(a64,YY,Adjustment Airline)
INSERT INTO [Airline]([airlne_cd],[carrier_cd],[descr]) VALUES(A64,YY1,Adjustment Airline1)
I am getting "Violation of PRIMARY KEY constraint Cannot insert duplicate key in object 'dbo.Airline'.
How to ignore this error?
February 6, 2012 at 5:03 am
Question to pose is:
- Why would you ignore a duplicate key ?
- is case sensitivity needed ? ( does it matter ? ) ( and would you want it only for this column, for this table, for this database ? )
- With SQL2008 you could use merge to see what needs to be done.
Check books online http://technet.microsoft.com/en-us/library/bb510625.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 6, 2012 at 5:06 am
The PK constraint across the airline_cd column will require unique values. You could drop the PK constraint, but I think you would be better off designing the table and it's constraints correctly for the range of data you would like it to hold!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 6, 2012 at 5:09 am
Hi,
As you're using a case-insensitive collation (SQL_Latin1_General_CP1_CI_AS) then a64 and A64 are seen as identical, and therefore violate the PK.
Using a case-sensitive one (e.g. SQL_Latin1_General_CP1_CS_AS) will allow you to insert both values as unique.
Of course, this depends on whether you actually want case sensitivity in your db design!
February 6, 2012 at 9:06 am
duplicate
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 6, 2012 at 9:07 am
Case sensitivity is not the way to go. Based on the data provided the OP would only be able to insert 2 duplicate records, what if they want to insert more than 2.
Another solution would be to create a compound PK across the airline_cd and carrier_cd columns.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply