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

Comments embedded.
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
>

As mentioned previously by Mark C.Stock correct spelling is a must.

> 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.
>

It appears to me you're the only person in this thread who perceived my post as 'arrogant'. Why is that?

> We are trying to learn from each other here not ridicule the less
> knowledgable.

Explain where such ridicule exists in my original post, as I cannot find it.

> I suppose some people like to kick their dogs too.
>

And some like to read into a post that which is not there.

> Thanks in Advance
> Rob
> My apologies for my novice postings.

Apologies are still in order for your misreading of my post.

> I am a DBA because nobody else will touch the job.
> At least its a niche.
>
>

David Fitzjarrell Received on Mon Nov 21 2005 - 12:18:46 CST