On 12/30/2013 06: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:
> 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
>
>
>
>
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.
--
_
°v°
/(_)\
^ ^ Mark LaPierre
Registered Linux user No #267004
https://linuxcounter.net/
****
--
To unsubscribe e-mail to: [email protected]
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
