Re: how to link different but related tables
Date: 14 Mar 2016 02:07:16 GMT
Message-ID: <subtables-20160314025141_at_ram.dialup.fu-berlin.de>
mireero <mireero_at_free.fr> writes:
>physical(id_sub_product, weight, width, height, ...)
>dematerialized(id_sub_product, file_size, file_link, ...)
>product(id_product, id_sub_product, product_type, price, ...)
SET foreign_key_checks = 0;
DROP TABLE IF EXISTS PHYSICAL;
DROP TABLE IF EXISTS DATA;
DROP TABLE IF EXISTS PRODUCT;
DROP VIEW IF EXISTS PHYSICAL_PRODUCT;
SET foreign_key_checks = 1;
CREATE TABLE PRODUCT
( PRODUCT SERIAL PRIMARY KEY,
PRICE DECIMAL ( 22, 2 ) );
CREATE TABLE PHYSICAL
( PRODUCT BIGINT UNSIGNED NOT NULL UNIQUE PRIMARY KEY,
WEIGHT DECIMAL ( 12, 5 ),
FOREIGN KEY ( PRODUCT ) REFERENCES PRODUCT ( PRODUCT )
ON DELETE CASCADE );
CREATE TABLE DATA
( PRODUCT BIGINT UNSIGNED NOT NULL UNIQUE PRIMARY KEY,
SIZE BIGINT,
FOREIGN KEY ( PRODUCT ) REFERENCES PRODUCT ( PRODUCT )
ON DELETE CASCADE );
CREATE VIEW PHYSICAL_PRODUCT AS SELECT *
FROM PRODUCT NATURAL JOIN PHYSICAL;
INSERT INTO PRODUCT ( PRICE ) VALUES ( 12.00 );
INSERT INTO PHYSICAL ( PRODUCT, WEIGHT )
VALUES ( LAST_INSERT_ID(), 10.00 );
SELECT * FROM PHYSICAL_PRODUCT;
DELETE FROM PRODUCT WHERE PRODUCT = LAST_INSERT_ID();
Received on Mon Mar 14 2016 - 03:07:16 CET