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 > 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 > > > > ==== > 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); > > SELECT * FROM PRODUCT; > SELECT * FROM CUSTOMER; > SELECT * FROM PRODUCT_ORDER; > > C:\mysql\bin>mysql -u sampadm -psecret -vvv < Foreign_Key_02.sql > > out.txt > > > -------------- > DROP TABLE IF EXISTS PRODUCT_ORDER, CUSTOMER, PRODUCT > -------------- > > Query OK, 0 rows affected (0.02 sec) > > -------------- > CREATE TABLE PRODUCT > ( > category INT NOT NULL, > id INT NOT NULL, > price DECIMAL(1,2), > > PRIMARY KEY(category, id) > ) TYPE=INNODB > -------------- > > Query OK, 0 rows affected (0.00 sec) > > -------------- > CREATE TABLE CUSTOMER > ( > id INT NOT NULL, > > PRIMARY KEY (id) > ) TYPE=INNODB > -------------- > > Query OK, 0 rows affected (0.00 sec) > > -------------- > 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 > -------------- > > Query OK, 0 rows affected (0.02 sec) > > -------------- > INSERT INTO PRODUCT(category, id, price) VALUES(1, 1, 0.1 ) > -------------- > > Query OK, 1 row affected (0.00 sec) > > -------------- > INSERT INTO CUSTOMER(id) VALUES (2) > -------------- > > Query OK, 1 row affected (0.00 sec) > > -------------- > SELECT * FROM PRODUCT > -------------- > > +----------+----+-------+ > | category | id | price | > +----------+----+-------+ > | 1 | 1 | 0.10 | > +----------+----+-------+ > 1 row in set (0.00 sec) > > -------------- > SELECT * FROM CUSTOMER > -------------- > > +----+ > | id | > +----+ > | 2 | > +----+ > 1 row in set (0.00 sec) > > -------------- > SELECT * FROM PRODUCT_ORDER > -------------- > > Empty set (0.00 sec) > > Bye > ==== > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
