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