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

Rob Williamson wrote:
> 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.
>
> I am a little behind on my patches so I thought I would ask out here.
>
> Thanks
> Rob

Why is every syntax error considered a bug?

SQL> ALTER TABLE shopping_cart

   2 add constraint shopping_cart_fk_inventory    3 foreign key (item)
   4 references (inventory.item_number);

references (inventory.item_number)

           *
ERROR at line 4:
ORA-00903: invalid table name

SQL> ALTER TABLE shopping_cart
  2 add constraint shopping_cart_fk_inventory   3 foreign key (item)
  4 references inventory(item_number);

Table altered.

References cannot use table.column syntax, it requires table(column) syntax as shown above. I expect your other alter table statements, creating foreign keys, were written properly.

David Fitzjarrell Received on Tue Nov 15 2005 - 17:29:21 CST