Re: [libreoffice-users] mysterious foreign key integrity constraint violation

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


--
Dale Erwin
Jr. 28 de Julio 657, Depto. 03
Magdalena del Mar, Lima 17 PERU
http://leather.casaerwin.org


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