Re: how to link different but related tables
From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Sun, 13 Mar 2016 22:11:00 -0400
Message-ID: <nc56dj$1n2$1_at_jstuckle.eternal-september.org>
>
> 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();
>
Date: Sun, 13 Mar 2016 22:11:00 -0400
Message-ID: <nc56dj$1n2$1_at_jstuckle.eternal-september.org>
On 3/13/2016 10:07 PM, Stefan Ram wrote:
> 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();
>
How does this solve his problem?
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Mon Mar 14 2016 - 03:11:00 CET