1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70
|
CREATE TABLE MA_TABLE (
PRI_ID INTEGER NOT NULL,
MY_DATE TIMESTAMP NOT NULL,
MY_VALUE DOUBLE NOT NULL,
MY_STATUS TINYINT NOT NULL,
MONPAR_ID INTEGER NOT NULL,
CHKCOND_ID INTEGER NOT NULL,
SITE_NAME VARCHAR(40) NULL,
CHAIN_NAME VARCHAR(40) NULL,
MEAS_ANT_NAME VARCHAR(40) NULL,
RF_POLAR TINYINT NULL,
RF_TYPE TINYINT NULL,
MY_RESTORATION_DATE TIMESTAMP NULL,
MY_RESTORATION_USE BOOLEAN NOT NULL DEFAULT FALSE
) TYPE=INNODB;
--
-- Foreign keys
--
ALTER TABLE MY_TABLE
ADD CONSTRAINT fk_my1 FOREIGN KEY (SITE_NAME, MEAS_ANT_NAME, RF_POLAR, RF_TYPE)
REFERENCES T_PATH(SITE_NAME, MEAS_ANT_NAME, RF_POLAR, RF_TYPE) ;
ALTER TABLE MY_TABLE
ADD CONSTRAINT fk_my2 FOREIGN KEY (SITE_NAME, CHAIN_NAME)
REFERENCES T_CHAIN(SITE_NAME, CHAIN_NAME) ;
ALTER TABLE MY_TABLE
ADD CONSTRAINT fk_my3 FOREIGN KEY (CHKCOND_ID)
REFERENCES T_CONDITION(CHKCOND_ID) ;
ALTER TABLE MY_TABLE
ADD CONSTRAINT fk_my4 FOREIGN KEY (MONPAR_ID)
REFERENCES T_PARAMETER(MONPAR_ID) ;
--
-- End foreign keys
--
--
-- Primary key auto increment
--
ALTER TABLE MY_TABLE MODIFY COLUMN PRI_ID INTEGER NOT NULL AUTO_INCREMENT;
--
-- End primary key auto increment
--
--
-- Index on MY_TABLE
--
CREATE INDEX IND_MY_TABLE_1 ON MY_TABLE
(
MY_DATE ASC
);
CREATE INDEX IND_MY_TABLE_2 ON MY_TABLE
(
MONPAR_ID ASC,
MY_DATE ASC
);
--
-- End index on MY_TABLE
-- |
Partager