Re: Is this a bug in 9i ( FK question )

From: Rob Williamson <robw_at_physics.umd.edu>
Date: Thu, 17 Nov 2005 17:48:23 -0500
Message-ID: <437D08B7.ADB49EA2@physics.umd.edu>

fktest.sql :

ALTER TABLE shopping_cart

   ADD CONSTRAINT shopping_cart_fk_inventory

         FORIEGN KEY (item)
         REFERENCES inventory;

commit;

SQL> @fktest

         FORIEGN KEY (item)
                      *

ERROR at line 3:
ORA-01735: invalid ALTER TABLE option

Change the fktest.sql to:

ALTER TABLE shopping_cart

   ADD CONSTRAINT shopping_cart_fk_inventory

         FORIEGN KEY (item)
         REFERENCES inventory (item_number);

commit;

SQL> @fktest

         FORIEGN KEY (item)
                      *

ERROR at line 3:
ORA-01735: invalid ALTER TABLE option

Commit complete.

I then tried inventory.item_number which I have been ridiculed for and got the same response.

Here are some of the other Alter tables that actually worked:

ALTER TABLE cost_history

   ADD CONSTRAINT cost_history_fk_inventory

         FOREIGN KEY (item_number)
         REFERENCES inventory ;

ALTER TABLE adjustment

   ADD CONSTRAINT adjustment_fk_inventory

         FOREIGN KEY (item_number)
         REFERENCES inventory ;

Here are the create table scripts:

create table inventory
(

        item_number smallint not null,
        quantity decimal(10,2) not null,
        unit_of_measure_number smallint not null,
        description varchar(128) not null,
        manufacturer varchar(64),
        manufacturer_part_number varchar(64),
        reorder_threshold decimal(10,2) not null,
        cost decimal(10,2) not null,
        individual_price_adjustment decimal(10,2) not null,
        location smallint not null,
        notes clob,
        fractional_sale smallint not null,
        inactive smallint not null,
        discontinued smallint not null,
                constraint inventory_pk primary key (item_number)

);
commit;

CREATE TABLE shopping_cart
(

        acct varchar(10) not null,
        fname varchar(64) not null,
        lname varchar(64) not null,
        item smallint not null,
        qty decimal(10,2) not null,
        unit_price decimal(10,2) not null,
        total_price decimal(10,2) not null,
        instock INT not null,

    constraint shopping_cart_pk primary key (acct, fname, lname, item) );
commit;

Ultimately this one worked:

CREATE TABLE shopping_cart
(

        acct varchar(10) not null,
        fname varchar(64) not null,
        lname varchar(64) not null,
        item  smallint not null,
        qty decimal(10,2) not null,
        unit_price decimal(10,2) not null,
        total_price decimal(10,2) not null,
        instock INT not null,

    constraint shopping_cart_pk primary key (acct, fname, lname,item),     constraint shopping_cart_fk_inventory foreign key (item) references invent$
);
commit;

Sorry for not putting this in my original. Thanks for looking at it though.
I made it work so I am not too worried I just want to learn from the experience
so that one day I can be as arrogant as David Fitzjarrell who also responded.

We are trying to learn from each other here not ridicule the less knowledgable.
I suppose some people like to kick their dogs too.

Thanks in Advance
Rob
My apologies for my novice postings.
I am a DBA because nobody else will touch the job. At least its a niche.

"Mark C. Stock" wrote:

> "Rob Williamson" <robw_at_physics.umd.edu> wrote in message
> news:437A560D.FEB9356_at_physics.umd.edu...
> >I am running 9i with a slightly outdated patch level.
> > I have two tables Inventory and Shopping_Cart
> > The Inventory has a primary key ( item_number ) smallint not null
> > The Shopping_cart table has a composite pk key
> > acct varchar(10) not null
> > fname varchar(64) not null
> > lname varchar(64) not null
> > item smallint not null
> >
> >
> > I tried to use the code:
> > ALTER TABLE shopping_cart
> > add constraint shopping_cart_fk_inventory
> > foreign key (item)
> > references (inventory.item_number);
> >
> > This failed
> >
> > I could only get the FK to work if I put it in the CREATE table
> > statement;
> >
> > Anyone seen this.
> >
> > I have used other Alter table add constraints in my code even
> > referencing the same table inventory.item_number
> > and they worked.
> > The only difference seems to be that shopping_cart has a composite key.
> >

>

> what was the error message?
> what was the syntax of the statements that worked?

>

> ++ mcs
> > I am a little behind on my patches so I thought I would ask out here.
> >
> > Thanks
> > Rob
> >
Received on Thu Nov 17 2005 - 16:48:23 CST