July 20, 2011 at 9:30 am
I have a column that acts as a PK and FK and when I try to insert into that column, it failes with the following message: Violation of PRIMARY KEY constraint 'PK_code'. Cannot insert duplicate key in object 'dbo.code'.
This is what I have... what is missing..? Please help...
-- Create Tables
CREATE TABLE category (
category_db_id bigint identity(1,1) NOT NULL,
categoryid uniqueidentifier NOT NULL,
category_scheme_db_id bigint NOT NULL,
userid varchar(100) NOT NULL,
label varchar(50) NOT NULL,
version decimal(10,2) NOT NULL,
versiondate datetime NOT NULL
)
;
CREATE TABLE code (
category_db_id bigint NOT NULL,
code_scheme_db_id bigint NOT NULL,
value smallint NOT NULL
)
;
-- Create Primary Key Constraints
ALTER TABLE category ADD CONSTRAINT PK_category
PRIMARY KEY CLUSTERED (category_db_id)
;
ALTER TABLE code ADD CONSTRAINT PK_code
PRIMARY KEY CLUSTERED (category_db_id)
;
-- Create Foreign Key Constraints
ALTER TABLE category ADD CONSTRAINT FK_category_schemes
FOREIGN KEY (category_scheme_db_id) REFERENCES schemes (scheme_db_id)
;
ALTER TABLE code ADD CONSTRAINT FK_code_category
FOREIGN KEY (category_db_id) REFERENCES category (category_db_id)
;
ALTER TABLE code ADD CONSTRAINT FK_code_schemes
FOREIGN KEY (code_scheme_db_id) REFERENCES schemes (scheme_db_id)
;
INSERT INTO code
SELECT b.category_db_id
, c.scheme_db_id AS code_scheme_db_id
, a.value
FROM code_raw a
JOIN category b ON a.categoryid = b.categoryid
JOIN schemes c ON a.codeschemeid = c.schemeid
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_code'. Cannot insert duplicate key in object 'dbo.code'.
The statement has been terminated.
July 20, 2011 at 9:47 am
That error is rally clear. It means that the pk value is already in the table (or twice in the data set you're inserting).
Try doing select PkKey FROM (select stmnt for insert) group by pk having count(*) > 1
If that doesn't return anything try with an exists statement in the base table to find the duplicate.
If that still fails check to see if you have a trigger that does an update or insert that could cause this.
July 20, 2011 at 10:10 am
This is a new table and there is no data in there.
I checked to see whether there were duplicate key from the table that I'm inserting from, and there was none. That's where I'm stuck.
Essentially, when I run this INSERT statement, it fails. There's no duplicate key in [category] table.
INSERT INTO code
SELECT b.category_db_id
, a.value
FROM code_raw a
JOIN category b ON a.categoryid = b.categoryid
July 20, 2011 at 10:14 am
The query that you are using to populate the code table has more than one category_db_id .
You will want to alter it to eliminate duplicates.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 20, 2011 at 10:42 am
Right, I have 964 DISTINCT category_db_id from category table. But since they are all unique, they should be ok for PKs...?
July 20, 2011 at 10:46 am
IMO your code table should have the combination of the two _db_id columns as primary key (or a *_db_id of its own and an unique constraint on the combination of both other _db_id columns)
I don't know about your specs, but maybe even a start date / end date column can be handy with that kind of code tables.
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
July 20, 2011 at 10:58 am
As was suggested category_db_id by itself is not going to be unique and you need a composite key.
I do not know what the definition of categoryid and category_scheme_db_id are but perhaps they could be candidates for part of the composite PK.
I'm not sure that your Database is in 3rd Normal Form. IMHO you may want to consider altering your design but without knowing what the definition of your attributes and requirements that may not be warranted.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 20, 2011 at 11:21 am
So in essence, you cannot have a PK that is getting a FK value from another table?
July 20, 2011 at 11:41 am
airparkroad (7/20/2011)
So in essence, you cannot have a PK that is getting a FK value from another table?
I've done something similar to this before and ti can be done.
I'm not caught up on this thread but assuming your table design is correct you should be able to do it.
July 20, 2011 at 11:42 am
airparkroad (7/20/2011)
So in essence, you cannot have a PK that is getting a FK value from another table?
For example you could have Order_ID as the Primary Key in the Orders Table (Parent) and it would be a Foreign Key in the Order_Detail Table (Child).
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 20, 2011 at 11:47 am
Welsh Corgi (7/20/2011)
airparkroad (7/20/2011)
So in essence, you cannot have a PK that is getting a FK value from another table?For example you could have Order_ID as the Primary Key in the Orders Table and it would be a Foreign Key in the Order_Detail Table.
Well ya, but I had something way more complex in mind (like 4-5 levels deep).
Servers - Databases - Access data Projects - Forms - Data sources - Sql Server Objects - Columns Used
I ended up splitting this a little bit and using an identity PK in Level 3, but from there on out I always copied over all the PK fields from the top tables all the way to the bottom.
July 20, 2011 at 11:56 am
I just tried to provide a very simplistic example of a Primary Key and Foreign Key scenario to convey the concept based on the question.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 20, 2011 at 12:02 pm
Actually, it's the other way.
category_db_id from table is a PK.
It is also a FK, which gets value from [category] table's FK.
So it's a PK that gets its values from another table's FK.
July 20, 2011 at 12:04 pm
Ninja's_RGR'us (7/20/2011)
Welsh Corgi (7/20/2011)
airparkroad (7/20/2011)
So in essence, you cannot have a PK that is getting a FK value from another table?For example you could have Order_ID as the Primary Key in the Orders Table and it would be a Foreign Key in the Order_Detail Table.
Well ya, but I had something way more complex in mind (like 4-5 levels deep).
Servers - Databases - Access data Projects - Forms - Data sources - Sql Server Objects - Columns Used
I ended up splitting this a little bit and using an identity PK in Level 3, but from there on out I always copied over all the PK fields from the top tables all the way to the bottom.
I know, I didn't mean to tell your exemple was bad. I just wanted to remove any doubts that this could be done at any level of complexity ;-).
July 20, 2011 at 12:06 pm
airparkroad (7/20/2011)
Actually, it's the other way.category_db_id from
table is a PK.It is also a FK, which gets value from [category] table's FK.
So it's a PK that gets its values from another table's FK.
Can you post some sample data (insert statements) which shows us where you're having issues. I'm sure we'll understand what you need with that.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply