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]
