The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
1183416
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

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
22.3k9129262
accept rate: 40%

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

Question tags:

×26

question asked: 25 May '16, 12:04

question was seen: 178 times

last updated: 26 May '16, 03:44