May 17, 2011 at 6:14 am
I'm getting really really frustrated. I have a table with a single column Primary Key. I have a mapping table where I'm supposed to create new products on this table. So if the Product was ABC1, I'm supposed to create Product DEF001. If the product was XYZ2, I'm supposed to create Product MPN002, etc.
The new products don't exist in the destination table. I've double and triple checked with INNER JOINS and SELECT... WHERE Product IN... code. But when I do my INSERT INTO, I keep getting Primary Key violations.
Does anyone know why SQL might be screaming about PK violations when the new value I'm inserting doesn't exist in the table?
May 17, 2011 at 6:24 am
No offense but have you checked :
That you are inserting in the correct table (server, db, schema...)
Checked the inner join to make sure they don't double the keys somehow??
Do you have triggers?
May 17, 2011 at 6:40 am
Ninja's_RGR'us (5/17/2011)
Checked the inner join to make sure they don't double the keys somehow??
What Ninja's stated is most likely the cause.
Most of PK violations I come across are due to duplicate values in the input rather than existing key and I have to sometimes use DISTINCT or GROUP BY to prevent it.
Far away is close at hand in the images of elsewhere.
Anon.
May 17, 2011 at 6:42 am
p.s. I normally run the SELECT without the INSERT and analyse the output for duplicates and start backwards from there.
Far away is close at hand in the images of elsewhere.
Anon.
May 17, 2011 at 6:44 am
Found the problem.
The mapping table has duplicate new product numbers. I didn't see it because there are so many new products, and so few dups. And the dups each have multiple (unique) old product numbers.
DOH.
Thanks, all.
May 17, 2011 at 6:46 am
It's one of the errors in ss that leaves very little room for interpretation :hehe:.
The only other option I was considering was an untrusted constraint that previously had the error and now was being found. is it possible to have an untrustred PK ???? (I'm sure for fks and check but I never heard about that one).
May 17, 2011 at 6:55 am
Ninja's_RGR'us (5/17/2011)
It's one of the errors in ss that leaves very little room for interpretation :hehe:.The only other option I was considering was an untrusted constraint that previously had the error and now was being found. is it possible to have an untrustred PK ???? (I'm sure for fks and check but I never heard about that one).
Well I guess that answers it...
USE tempdb
GO
SET IMPLICIT_TRANSACTIONS ON
GO
CREATE TABLE dbo.t (id INT NOT NULL)
INSERT INTO dbo.t (id) SELECT 1 UNION ALL SELECT 2
SELECT * FROM dbo.t
ALTER TABLE dbo.t ADD CONSTRAINT PK_Test PRIMARY KEY CLUSTERED (id)
ALTER TABLE dbo.t NOCHECK CONSTRAINT PK_Test
INSERT INTO dbo.t (id) SELECT 1 UNION ALL SELECT 2
SELECT * FROM dbo.t
ROLLBACK
May 17, 2011 at 6:56 am
Oh, learning time.
I've never heard the phrase "untrusted constraint." Please explain.
May 17, 2011 at 7:00 am
When you disable a constraint like a FK or check, the server marks it as untrusted because there can be some bad data in there.
If you reenable it with NOCHECK, the server doesn't revalidate the whole table so you have "untrusted" data in there.
Since those constraints can be used by the optimiser to pick better plans it can negatively influence performance (above and beyond returning bad data).
That should give you an idea at glance... I also remember reading something on that topic in an article here... I'll let you dig around.
May 17, 2011 at 7:04 am
Thank you, Ninja. I appreciate the new info. I will definitely dig around.
May 17, 2011 at 9:46 am
ooops...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 18, 2011 at 5:01 am
And if the constraint is not valid, the optimizer won't use it for simplification, eliminating tables from joins when it can because it knows the data will be available in a multi-table hop. Foreign key constraints help performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 18, 2011 at 6:18 am
Grant Fritchey (5/18/2011)
And if the constraint is not valid, the optimizer won't use it for simplification, eliminating tables from joins when it can because it knows the data will be available in a multi-table hop. Foreign key constraints help performance.
Do you have the link to that article? I can't find it anymore.
May 18, 2011 at 6:20 am
Ninja's_RGR'us (5/18/2011)
Grant Fritchey (5/18/2011)
And if the constraint is not valid, the optimizer won't use it for simplification, eliminating tables from joins when it can because it knows the data will be available in a multi-table hop. Foreign key constraints help performance.Do you have the link to that article? I can't find it anymore.
Ask and ye shall receive.[/url]
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 18, 2011 at 6:23 am
Grant Fritchey (5/18/2011)
Ninja's_RGR'us (5/18/2011)
Grant Fritchey (5/18/2011)
And if the constraint is not valid, the optimizer won't use it for simplification, eliminating tables from joins when it can because it knows the data will be available in a multi-table hop. Foreign key constraints help performance.Do you have the link to that article? I can't find it anymore.
Ya that's it. I was under the impression that it was either posted on ssc or by K. Tripp or Paul Randal.
You can see my confusion by the quality of the post :-D.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply