mysterious foreign key integrity constraint violation

users
#1

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

}... ) ]

       [ 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

#2

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.

#3

Hi Larry,

Larry Evans schrieb:

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

[..]

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?

First define this third table as new table and then set the relationships. It is a n:m structure.

Kind regards
Regina

#4

You need to add a field to table Products that contains the name
contained in Suppliers.SupplierName such that
Products.NewField = Suppliers.SupplierName

Table:Products with ProductName as primary key.
ProductName Inventory Supplier
----------- --------- --------
product1 100 supplier1
product2 200 supplier1
product3 125 supplier2

The database has no way of knowing which supplier/product are related to
each other.

You will also need to add a field to the Products table that indicates
which entry is current. When you first set up the products table there
will be only one supplier for each product, but over time, the supplier
will change. If you don't want to keep a history of past suppliers then
you will not need the current field.

#5

Ah! Yes. Tried it and it works.
Seems obvious now.
Thanks.

-regards,
Larry