Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > Oracle
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 10th, 2004, 01:13 AM
Authorized User
 
Join Date: Apr 2004
Location: malacca, malacca, Malaysia.
Posts: 69
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to junemo
Default 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.

  #2 (permalink)  
Old June 10th, 2004, 09:45 AM
Authorized User
 
Join Date: Apr 2004
Location: Boise, ID, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #3 (permalink)  
Old June 10th, 2004, 07:23 PM
Authorized User
 
Join Date: Apr 2004
Location: malacca, malacca, Malaysia.
Posts: 69
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to junemo
Default

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.

  #4 (permalink)  
Old June 11th, 2004, 02:32 AM
Authorized User
 
Join Date: Apr 2004
Location: Boise, ID, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #5 (permalink)  
Old June 13th, 2004, 09:06 PM
Authorized User
 
Join Date: Apr 2004
Location: malacca, malacca, Malaysia.
Posts: 69
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to junemo
Default

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.

  #6 (permalink)  
Old June 14th, 2004, 12:16 AM
Authorized User
 
Join Date: Apr 2004
Location: Boise, ID, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #7 (permalink)  
Old June 14th, 2004, 01:11 AM
Authorized User
 
Join Date: Apr 2004
Location: malacca, malacca, Malaysia.
Posts: 69
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to junemo
Default

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.




  #8 (permalink)  
Old June 14th, 2004, 01:52 AM
Authorized User
 
Join Date: Apr 2004
Location: Boise, ID, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #9 (permalink)  
Old June 14th, 2004, 11:12 PM
Authorized User
 
Join Date: Apr 2004
Location: malacca, malacca, Malaysia.
Posts: 69
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to junemo
Default

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.



  #10 (permalink)  
Old June 14th, 2004, 11:20 PM
Authorized User
 
Join Date: Apr 2004
Location: Boise, ID, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Design: Foreign Key to Multiple Primary Keys? kalel_4444 BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 10 May 8th, 2008 04:14 PM
Foreign key not updating with Primary key xavier1945 BOOK: Access 2003 VBA Programmer's Reference 2 July 4th, 2007 09:48 PM
foreign key constraints - referential integrity DTSDeveloper SQL Server DTS 0 September 6th, 2005 01:10 PM
Foreign Key Problem re_anu SQL Server 2000 1 September 26th, 2003 10:58 AM



All times are GMT -4. The time now is 03:26 PM.


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