p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Oracle (http://p2p.wrox.com/forumdisplay.php?f=105)
-   -   FOREIGN KEY and PRIMARY KEY Constraints (http://p2p.wrox.com/showthread.php?t=14006)

junemo June 10th, 2004 01:13 AM

FOREIGN KEY and PRIMARY KEY Constraints
 
Hi,I hope someone can solve my problem.
When I run my system at browser, it always display error msg about the primary and foreign key constraint.:(

How I want to solve this problem [?]

Error:
Warning: ociexecute(): OCIStmtExecute: ORA-00001: unique constraint (PROD.PK_LINE) violated

Warning: ociexecute(): OCIStmtExecute: ORA-02291: integrity constraint (PROD.FK_NO) violated - parent key not found

Attach code:

<?php
require_once("DB.php");
require_once ("recordset.php");

$connection = OCILogon("prod","prod");

// Create SQL statement and issue query
$stmt = OCIparse($connection,"insert into bill (bill, ship, invdate, invno, po, dn, cur, pri, term) ".
      "values ('$bill', '$ship', '$invdate', '$invno', '$po', '$dn', '$cur', '$pri', '$term')");

$stmt = OCIParse($connection,"insert into item (line, invno, po1, stock1, qty1, uom1, pri1, amo1, total) ".
     "values ('$line', '$invno', '$po1', '$stock1', '$qty1', '$uom1', '$pri1', '$amo1', '$total')");

OCIExecute($stmt,OCI_DEFAULT);
OCILogOff($connection);
?>

Thanks in advance.


Justin Cave June 10th, 2004 09:45 AM

First, it would appear that you are parsing the statement that INSERT's data into the bill table, but that you are never executing it. If there is a foreign key constraint that one of the columns in the item table (invno?) reference a row in the bill table, the fact that you are not inserting data into bill could be a problem.

Based on the name of the primary key, I would suspect that the line column of the item table must be unique. Thus, you have to ensure that you pass in a unique line number each time (or that you populate the line column from a BEFORE INSERT trigger on the table).

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

junemo June 10th, 2004 07:23 PM

Hi,you mean must separate to execute the query like this?

// Create SQL statement and issue query
$stmt = OCIparse($connection,"insert into bill (bill, ship, invdate, invno, po, dn, cur, pri, term) ".
      "values ('$bill', '$ship', '$invdate', '$invno', '$po', '$dn', '$cur', '$pri', '$term')");
OCIExecute($stmt,OCI_DEFAULT);

$stmt = OCIParse($connection,"insert into item (line, invno, po1, stock1, qty1, uom1, pri1, amo1, total) ".
     "values ('$line', '$invno', '$po1', '$stock1', '$qty1', '$uom1', '$pri1', '$amo1', '$total')");

OCIExecute($stmt,OCI_DEFAULT);

This also display same error msg.
Pass in a unique line number each time? But I must start line number 1 for new invoice each time.

item table(invno)foreign key-bill table.
My table:
CREATE TABLE bill
    (bill VARCHAR2 (512),
     ship VARCHAR2 (512),
     invdate DATE,
     invno VARCHAR2 (12) CONSTRAINT pk_invno PRIMARY KEY,
     po VARCHAR2 (12),
     dn VARCHAR2 (15),
      cur VARCHAR2 (15),
      pri VARCHAR2 (35),
      term VARCHAR2 (30) );

CREATE TABLE item
    (line VARCHAR2 (15) CONSTRAINT pk_line PRIMARY KEY,
     invno VARCHAR2 (12) CONSTRAINT fk_no REFERENCES bill,
     po1 VARCHAR2 (10),
     stock1 VARCHAR2 (2000),
     qty1 NUMBER (10),
     uom1 VARCHAR2 (5),
     pri1 VARCHAR2 (10),
     amo1 VARCHAR2 (10),
     total VARCHAR2 (10) );

Have any way to solve this problem?How?

Thanks in advance.


Justin Cave June 11th, 2004 02:32 AM

The line column of the item table is defined as the primary key of the item table. By definition, that means that line must be unique. Your table definition is at odds with your requirement to start each invoice at line 1.

Also, it seem like a lot of your columns are defined as VARCHAR2's when they should be defined as NUMBER's. line, invo, qty1, amo1, and total, for example, look like they ought to contain numeric data, not character strings.

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

junemo June 13th, 2004 09:06 PM

Then, how to change my table definition-item table that can start each invoice at line 1?
If line column is defined as the primary key, it can store the same number(numeric data) at dbase?

invn column defined as NUMBER can't store symbol "-", isn't? eg. "1002-12-5"

Thanks in advance.


Justin Cave June 14th, 2004 12:16 AM

If you want each invoice to start fresh with line 1, line cannot be the primary key in your item table. Perhaps line and invoice together would make a good primary key (a natural primary key), perhaps you should add a column to the item table like "item_id" with is the primary key and which is populated by a sequence (synthetic primary key). Whether the natural or the synthetic primary key is the better approach depends on a number of factors.

- Do you generally use natural or synthetic primary keys?
- Would you ever envision changing the invoice number or line of a particular item? Or would those columns, once set, remain static for the life of the item?
- Is there benefit to having a single column be the primary key from a data model standpoint? If lots of tables or particularly large tables need to have a foreign key constraint to the item table, it may be easier to have a single column rather than having a composite primary key for data storage reasons as well as for clarity of the model.

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

junemo June 14th, 2004 01:11 AM

Oracle still new for me so I don't know what is mean by natural or synthetic primary key. This system just have two table (bill and item). I write in SQL and execute the script in TOAD.
Invoice number and line can't been changing.

TABLE bill
    invno CONSTRAINT pk_invno PRIMARY KEY

TABLE item
    line CONSTRAINT pk_line PRIMARY KEY
    invno CONSTRAINT fk_invno REFERENCES bill

What solution is the best to make it easier and understanding?

thanks in advance.





Justin Cave June 14th, 2004 01:52 AM

One possible solution would be

Code:

CREATE TABLE bill (
  invno NUMBER CONSTRAINT pk_bill PRIMARY KEY,
  <<more columns>>
);

CREATE TABLE item (
  invno NUMBER CONSTRAINT fk_item_bill REFERENCES bill( invno ),
  line  NUMBER,
  <<more columns>>
);

ALTER TABLE item
  ADD CONSTRAINT pk_item PRIMARY KEY( invno, line );

This will force the combinatino of the invoice number and the line number to uniquely identify a row in the item table. Multiple invoice numbers can have the same line number (i.e. every invoice could have a line 1) but no two items for the same invoice number can have the same line number.

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

junemo June 14th, 2004 11:12 PM

Thanks Justin.

I want insert new field- in_line into table ITEM, defined as PRIMARY KEY and auto increment. How do I write the SQL script?

Isn't like this:

CREATE TABLE item (
   in_line NUMBER CONSTRAINT pk_item PRIMARY KEY AUTO INCREMENT,
   line NUMBER,
   <<more columns>>
);

Thanks in advance.




Justin Cave June 14th, 2004 11:20 PM

You need to have a sequence and a BEFORE INSERT trigger in Oracle to replicate the "auto increment" functionality in SQL Server.

I have an example of setting this up in another forum http://www.ddbcinc.com/askddbc/topic.asp?TOPIC_ID=239 (free registration required).

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC


All times are GMT -4. The time now is 07:17 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.