Dear Toro Hill,
Thank you for the gentle explanation,
I believe you mean this:
Hence it is not possible to insert, update or modify anything into
TABLE PRODUCT_ORDER
Because :
No is auto Increment, cannot be directly manipulated,
product_category
product_id
customer_id
is all Foreign Keys
and hence will be actualised through the references indirectly,
when they are changed, the new values will be propagated,
no insert statement is possible into a foreign key or anything
which is auto Increment,
Please correct me, foreign key constraints prevent values to be modified
in variables, it is only possible through references,
When an external variable is inserted, and through references copied
into
TABLE PRODUCT_ORDER, then the auto increment will do what it should.
#INSERT INTO PRODUCT_ORDER(some variable) VALUES( some values );
is not possible, for any variable or value.
Is this true for PRODUCT_ORDER ?
Pleas tell me, what is the purpose of
ON UPDATE CASCADE ON DELETE RESTRICT ?
Is that necessary ?
Why ?
Example please ?
Yours Sincerely
Morten Gulbrandsen
-----Ursprüngliche Nachricht-----
Von: Toro Hill [mailto:[EMAIL PROTECTED]
Gesendet: Mittwoch, 17. September 2003 01:35
An: Morten Gulbrandsen
Cc: [EMAIL PROTECTED]
Betreff: Re: AW: FOREIGN KEY() REFERENCES ON UPDATE CASCADE ON DELETE
RESTRICT
The first foreign key contraints in the following table definition mean
this:
Any record that is inserted into the product_order table must have
values for product_category and
product_id that exist in the category and id fields of a record in the
product table.
The second foreign key contraints means that any record that is inserted
into the product_order
table must have a value for customer_id that exists in the id field of a
record in the customer
table.
CREATE TABLE PRODUCT_ORDER
(
no INT NOT NULL AUTO_INCREMENT,
product_category INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY(no),
INDEX (product_category, product_id),
FOREIGN KEY (product_category, product_id) REFERENCES
product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX (customer_id),
FOREIGN KEY (customer_id) REFERENCES customer(id)
) TYPE=INNODB;
Therefore, when you try and execute the last insert statement below it
fails because the value for
customer_id is not in the id field of any of the records in the customer
table. Hence the foreign
key constraint defined stops you from doing the last insert, which is
what it should do.
INSERT INTO PRODUCT(category, id, price) VALUES(1, 1, 0.1 );
INSERT INTO CUSTOMER(id) VALUES (2);
INSERT INTO PRODUCT_ORDER(customer_id) VALUES(1);
I hope this helps.
Toro
>
>
> -----Ursprüngliche Nachricht-----
> Von: Toro Hill [mailto:[EMAIL PROTECTED]
> Gesendet: Dienstag, 16. September 2003 03:36
> An: Morten Gulbrandsen
> Cc: [EMAIL PROTECTED]
> Betreff: Re: FOREIGN KEY() REFERENCES ON UPDATE CASCADE ON DELETE
> RESTRICT
>
> I believe that your ON UPDATE CASCADE clause should be in the
definition
> for the PRODUCT and
> CUSTOMER table rather than the PRODUCT_ORDER table.
>
> However, I don't think that it will work how you expect.
>
> ON UPDATE CASCADE means that everytime you update a row in this table
> then all rows in other
> tables that reference this table (via a foreign key) will be updated
> also. So if there are no rows
> in PRODUCT_ORDER then ON UPDATE CASCADE will not insert new rows when
> you add rows to the other
> tables. What will happen instead is that any row (that already exists)
> in your PRODUCT_ORDER table
> will be updated with the new data that has been updated in one of the
> other tables.
>
> This is my understanding of how it works anyway. For further
information
> go to
> http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html
>
> Hope this helps.
> Toro
>
> <Morten>
> Dear Toro
> Thank you for your honest comment,
> the statement "ON UPDATE CASCADE"
> does not INSERT, UPDATE or CASCADE anything.
>
> The code needs explanation.
>
> It is from the reference manual as a complex example with minor
> modifications.
> I hope that the company MySQL AB could take a look at it.
>
> Confer 7.5.5.2 FOREIGN KEY Constraints
> http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html
>
> Please correct me,
> if some of you are able to get anything relational out of it.
>
> The first I expect from any database is that the examples from the
> Reference manual compiles and runs.
>
> I do get error messages,
> which are 'correct' I believe due to the constraints.
>
> But no expected relational behaviour.
> Like the parent child example
> from the reference manual.
>
> I believe the tables PRODUCT and CUSTOMER
> are entity types and
> the table PRODUCT_ORDER
> is a relationship type.
>
> However foreign keys are not correctly implemented in MySQL.
> As we all can see from this example.
> </Morten>
>
> USE test;
>
> DROP TABLE IF EXISTS PRODUCT_ORDER, CUSTOMER, PRODUCT;
> CREATE TABLE PRODUCT
> (
> category INT NOT NULL,
> id INT NOT NULL,
> price DECIMAL(1,2),
>
> PRIMARY KEY(category, id)
> ) TYPE=INNODB;
>
> CREATE TABLE CUSTOMER
> (
> id INT NOT NULL,
>
> PRIMARY KEY (id)
> ) TYPE=INNODB;
>
>
> CREATE TABLE PRODUCT_ORDER
> (
> no INT NOT NULL AUTO_INCREMENT,
> product_category INT NOT NULL,
> product_id INT NOT NULL,
> customer_id INT NOT NULL,
> PRIMARY KEY(no),
>
> INDEX (product_category, product_id),
> FOREIGN KEY (product_category, product_id) REFERENCES
> product(category, id)
> ON UPDATE CASCADE ON DELETE RESTRICT,
>
> INDEX (customer_id),
> FOREIGN KEY (customer_id) REFERENCES customer(id)
> ) TYPE=INNODB;
>
>
> INSERT INTO PRODUCT(category, id, price) VALUES(1, 1, 0.1 );
> INSERT INTO CUSTOMER(id) VALUES (2);
> #INSERT INTO PRODUCT_ORDER(customer_id) VALUES(1);
>
> # ERROR 1216 at line 40: Cannot add or update a child row:
> # a foreign key constraint fails
> # it is impossible to directly add or update PRODUCT_ORDER
> # what I find correctly, incorrect is the foreign key constraints,
> # since this table is from the reference manual,
> # I'd like some explanation on how to use the table, please.
>
>
> SELECT * FROM PRODUCT;
> SELECT * FROM CUSTOMER;
> SELECT * FROM PRODUCT_ORDER;
>
>
>
>
>
>> Dear Programmers,
>> At the end of this query,
>> I make a select * from the table product_order,
>> Which happens to be empty,
>>
>> Why ?
>>
>> Is something wrong with my insert statements please ?
>>
>> I inserted something into the tables CUSTOMER and PRODUCT,
>>
>> and I expected it to appear into the table PRODUCT_ORDER
>> which is some kind of relationship between the two entity types
>> CUSTOMER and PRODUCT.
>>
>> What do I please have to insert in order to achieve some
>> evidence for the existence of referential integrity ?
>> please ?
>>
>> ON UPDATE CASCADE I feel means something like that
>> the actual data is propagated due to the references ?
>>
>>
>> Yours Sincerely
>>
>> Morten Gulbrandsen
>>
>>
>>
>
>
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
