Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index

You could resolve this with a foreign key in the Products table (would require a new field) in which you store the primary key of the applicable row in the suppliers table. This way one supplier could supply more than one product. However, this would not cover the case where one product can be supplied by more than one supplier. For that you would need a third table just to handle the many-to-many relationship.

On 12/30/2013 6:39 PM, Larry Evans wrote:
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:
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
  ("ProductName") REFERENCES "Suppliers"

Googling sql foreign key got hit:

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



Dale Erwin
Jr. 28 de Julio 657, Depto. 03
Magdalena del Mar, Lima 17 PERU

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.