x

INSERT values to a table

I have with me 3 tables PURCHASE, PURCHASE_ERRORS and ERRORS.

PURCHASE table consists of 50 columns with the PRIMARY KEYS being SEND-ID, CONTROL-ID and ORDER-ID

PURCHASE_ERRORS table consists of 4 columns all of which PRIMARY KEYS namely SEND-ID, CONTROL-ID, ORDER-ID and ERROR-NO

ERRORS table consists of 2 columns both PRIMARY KEYS named ERROR-NO and ERROR-DESCRIPTION.

Question:

I am trying to INSERT into PURCHASE_ERRORS (SEND-ID, CONTROL-ID, ORDER-ID, ERROR-NO) VALUES (a,b,c,d);

NOTE: 'a','b' and 'c' are variables derived from PURCHASE table and 'd' from ERRORS table

I receive an error that looks like - The insert value of the FOREIGN KEY "CCPY0208"' is not equal to any value of the parent key of the parent table.

The values 'a', 'b' and 'c' already exist in both PURCHASE and PURCHASE_ERRORS in their respective SEND-ID, CONTROL-ID and ORDER-ID columns. The same goes for 'd' in ERRORS and PURCHASE_ERRORS in ERROR-NO column.

TABLE RELATIONSHIPS as seen in RazorSQL tool

PURCHASE

foreign key column || foreign key table || foreign key table column

CONTROL-ID || CONTROL || CONTROL-ID

CONTROL-ID || CONTROL || CONTROL-ID

SEND-ID || CONTROL || SEND-ID

SEND-ID || CONTROL || SEND-ID

NOTE: CONTROL is another table belonging to the same database.

PURCHASE_ERRORS

foreign key column || foreign key table || foreign key table column

ERROR-NO || ERRORS || ERROR-NO

ERROR-NO || ERRORS || ERROR-NO

CONTROL-ID || PURCHASE || CONTROL-ID

CONTROL-ID || PURCHASE || CONTROL-ID

SEND-ID || PURCHASE || SEND-ID

SEND-ID || PURCHASE || SEND-ID

ORDER-ID || PURCHASE || ORDER-ID

ORDER-ID || PURCHASE || ORDER-ID

How Do I get the INSERT statement to work ??????

more ▼

asked Dec 19, 2013 at 09:24 PM in Default

avatar image

sandman147
30 2 5 6

is "CCPY0208" represented by a, b, c or d?

Dec 25, 2013 at 02:40 AM KenJ

Thats the thing KenJ, I dont have a CCPY0208 value for either a,b,c or d. Atleast I am unable to see it displayed in either. The errors I keep getting for different use cases are:

The insert value of the FOREIGN KEY "CCPY0208" is not equal to any value of the parent key of the parent table(All 4)

The insert value of the FOREIGN KEY "CCPY0108" is not equal to any value of the parent key of the parent table(W/O SEND-ID)

The insert value of the FOREIGN KEY "CCPY0108" is not equal to any value of the parent key of the parent table(W/O CONTROL-ID)

The insert value of the FOREIGN KEY "CCPY0108" is not equal to any value of the parent key of the parent table(W/O ORDER-ID)

The insert value of the FOREIGN KEY "CCPY0208" is not equal to any value of the parent key of the parent table(W/O ERROR_NO)

The insert value of the FOREIGN KEY "CCPY0108" is not equal to any value of the parent key of the parent table(ONLY ERROR_N0)

The insert value of the FOREIGN KEY "CCPY0108" is not equal to any value of the parent key of the parent table(ONLY SEND-ID)

The insert value of the FOREIGN KEY "CCPY0108" is not equal to any value of the parent key of the parent table(ONLY CONTROL-ID)

Dec 26, 2013 at 02:57 PM sandman147

Could there be a trigger in play that is doing something unexpected here?

Do any of the 4 columns have a default value of "CCPY0108" or "CCPY0208" (or some function that would generate one of those values)?

Dec 26, 2013 at 03:14 PM KenJ

When I do an Extended quick browse of SQL Developer. A dialogue box showing foreign key relations with tables. The root node represents the initially selected table. The children of a node are those tables joined to the parent node by foreign key relations.

Subsidiary table entries are displayed with their table name followed by the foreign key name joining the table with the parent node.

A symbol with a yellow key indicates that a foreign key is defined by the parent node, referencing the primary key of the child node (parent:child = n:1). A red key indicates that a foreign key is defined by the child node table and references the primary key of the parent node (parent:child = 1:n).

ROOT: PURCHASE_ERRORS CHILD: yellow key ERRORS(CCPY0208) & yellow key PURCHASE(CCPY0108)

ROOT: PURCHASE CHILD: yellow key CONTROL(CCPY0104) & red key PURCHASE_ERRORS(CCPY0108)

ROOT: ERRORS CHILD: red key PURCHASE_ERRORS(CCPY0208)

Does this mean anything to you Ken ? Im working with a test database not exactly sure how reliable and accurate. Somehow can the fault lie there ?

Dec 27, 2013 at 08:51 PM sandman147

It looks like CCPY0208 is the foreign key name rather than the violating value. I misread the error message.

The value you are trying to insert into PURCHASE_ERRORS.ERROR-NO may not be present in ERRORS.ERROR-NO. Possibly the same issue with PURCHASE_ERRORS.SEND-ID and ORDER-ID

If you take the ERROR-NO value you are trying to insert into PURCHASE_ERRORS can you select it from ERRORS? SELECT * FROM ERRORS WHERE "ERROR-NO" = @error_number_you_want_to_insert_into_purchase_errors

Dec 27, 2013 at 10:28 PM KenJ
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

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

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x144
x43
x42

asked: Dec 19, 2013 at 09:24 PM

Seen: 1015 times

Last Updated: Dec 27, 2013 at 10:28 PM

Copyright 2017 Redgate Software. Privacy Policy