[libreoffice-users] mysterious foreign key integrity constraint violation

I've 2 tables:

Table:Products with ProductName as primary key.
ProductName Inventory
----------- ---------
product1      100
product2      200

Table:Suppliers with SupplierName as primary key.
SupplierName PhoneNumber
------------ -----------
supplier1      111-1111
supplier2      222-2222

when I attempt to create a relationship:
  SupplierSuppliesProduct
between them, using the Tools>Relationship menu selection,
I get a "Relation Design" window with
both Tables and their fields displayed in the upper left.
In that window, I select the Insert>New Relation, menu item
resulting in a Relations window with:

   Tables Involved
     Products   Suppliers
   Fields Involved
     Products   Suppliers
     --------   ---------

   [OK] [Cancel] [Help]

I enter ProductName under the Products column
and SupplierName under the Suppliers column, then press
the OK button, resulting in an error window containing
error message:

  SQL Status: 23000
  Error code: -177

  Integrity constraint violation -no parent
  product1, table: Products in statement
  [ALTER TABLE "Products" ADD FOREIGN KEY
  ("ProductName") REFERENCES "Suppliers"
  ("SupplierName")]

Googling sql foreign key got hit:

http://en.wikipedia.org/wiki/Foreign_key#Defining_foreign_keys

which contained:

  a foreign key is a field (or collection of fields) in one table
  that uniquely identifies a row of another table.

which is exactly what I want.  That hit also contained:

ALTER TABLE <TABLE identifier>
   ADD [ CONSTRAINT <CONSTRAINT identifier> ]
      FOREIGN KEY ( <COLUMN expressionexpression>}... )
      REFERENCES <TABLE identifier> [ ( <COLUMN expression> {, <COLUMN 
expression>}... ) ]
      [ ON UPDATE <referential action> ]
      [ ON DELETE <referential action> ]

which contains some of the same key words as the error message.
However, it doesn't help with the error message because I'm
not trying to alter the Products table, as the error message
suggests, I'm trying to create a new table, SupplierSuppliesProduct, 
containing a relationship between two already existing tables.  The
new table would look something like:

  Table: SupplierSuppliesProduct
     with primary keys SupplierName,ProductName
  showing which Supplier supplies which Product.

How should I do that?

TiA.

-regards,
Larry




--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted