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]
