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

Rob Williamson wrote:
> 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

The correct syntax is demonstrated in Morgan's Library at www.psoug.org and in numerous other places on the web: Most specifically http://tahiti.oracle.com.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)