Error - Could not execute statement. No primary key value for foreign key 'proditem001' in table 'proditemlinks'

0

Hi,

I am relatively new to writing SQL so I apologise if this is something I should know.

I have the following statement;

declare @prod varchar(25)
set @prod = (select top 1 prodref from dba.proditem where prodcateg = '84' and created >= today() and ((charindex('/W/',proddesc) > 0)) or (charindex('/G/',proddesc) > 0))
declare @cnt INT
set @cnt = 1
while @cnt <= (select count(prodref) from dba.proditem where prodcateg = '84' and created >= today() and ((charindex('/W/',proddesc) > 0)) or (charindex('/G/',proddesc) > 0))
BEGIN
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'601210GST',100,1,3)
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'601215GST',100,1,3)
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'601220GST',100,1,3)
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'601230GST',100,1,3)
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'601240GST',100,1,3)
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'601250GST',100,1,3)
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'601260GST',100,1,3)
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'601270GST',100,1,3)
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'601280GST',100,1,3)
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'601190GST',100,1,3)
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'6012299GST',100,1,3)
set @cnt = @cnt+1
END

My code seems to do as I expected, but I get the following error appear;

Could not execute statement. No primary key value for foreign key 'proditem001' in table 'proditemlinks'

My first questions is what is proditem001? It doesn't exist in our system. Also what is the error in my code?

asked 25 May '16, 12:04

Jongee's gravatar image

Jongee
217●17●17●22
accept rate: 0%

2

Here is a SQL example to accompany Mark's answer...

CREATE TABLE x ( 
   a INTEGER PRIMARY KEY,
   b INTEGER NOT NULL );
CREATE TABLE proditemlinks ( 
   c INTEGER PRIMARY KEY,
   d INTEGER NOT NULL,
   FOREIGN KEY proditem001 ( d ) REFERENCES x ( a ) );
INSERT x VALUES ( 1, 2 );
INSERT proditemlinks VALUES ( 3, 4 );

Could not execute statement.

No primary key value for foreign key 'proditem001' in table
'proditemlinks'
SQLCODE=-194, ODBC 3 State="23000"
Line 12, column 1

INSERT proditemlinks VALUES ( 3, 4 )
(25 May '16, 15:25) Breck Carter
One Answer:
active answersoldest answersnewest answerspopular answers
2

There is nothing wrong with your code, it is your data that is not satisfying the schema of your database. You have not shown your schema but from the error message I can infer that there is a foreign key (named "proditem001") from table proditemlinks to some other table (which I cannot determine from the information that you have given), and the other table does not contain a row with a primary key value is equal to one of the rows that you are trying to insert.

The 'proditem001' is the name of the foreign key in table proditemlinks.

HTH

permanent link

answered 25 May '16, 12:16

Mark%20Culp's gravatar image

Mark Culp
24.8k●9●139●296
accept rate: 41%

Thanks Mark, I have just found the hidden 'proditem001' when looking to give you my schema.

(26 May '16, 03:44) Jongee
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

learn more about Markdown

Question tags:

×34

question asked: 25 May '16, 12:04

question was seen: 4,017 times

last updated: 26 May '16, 03:44

Related questions

insert comma seperated values in table

Update/Insert data during synchronization

"Right truncation of string data" error on multiple inserts with prepared statements

Combination of "insert ... on existing update ..." and computed column in pk

Generate Script for Table Data

Multirow inserts

How to insert timestamp to sybase table using insert query?

How can I use INSERT...ON EXISTING when I can't use the primary key as match?

Insert two rows at the end on every...

ASA 10.0.1 with Servoy 6.1 Insert Into flips TIME Values to 12:00am