生成テーブルDDL群サンプル

-- DROP FOREIGN KEY -------------------------------------------------------
ALTER TABLE T0002 DROP FOREIGN KEY FK_T0002_01
;

-- [Get Foreign Key Id.]
SELECT DISTINCT
       REPLACE(T1.ID,'sampledb/','') '[Get Foreign Key Id.]'
  FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN T1
       INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS T2
    ON T1.ID = T2.ID
 WHERE T1.FOR_NAME = 'sampledb/T0008'
   AND T2.FOR_COL_NAME in ('ORDER_NO')
   AND T1.REF_NAME = 'sampledb/T0001'
   AND T2.REF_COL_NAME in ('ORDER_NO')
;
--   If [Get Foreign Key Id.] is '[TABLE ID.]_ibfk_x', it is the system automatic numbering ID.
--   No need to specify ID in DDL(CREATE/ALTER TABLE).

ALTER TABLE T0008 DROP FOREIGN KEY [Get Foreign Key Id.]
;

-- TABLE RENAME ( T0001 -> T0001_TEMP ) -----------------------------------
SELECT COUNT(*) FROM T0001
;
ALTER TABLE T0001 RENAME TO T0001_TEMP
;
SELECT COUNT(*) FROM T0001_TEMP
;

-- DROP TABLE ( T0001 ) ---------------------------------------------------
DROP TABLE IF EXISTS T0001
;

-- CREATE TABLE ( T0001 ) -------------------------------------------------
CREATE TABLE T0001
(
       ORDER_NO CHAR(7) NOT NULL COMMENT '注文番号<[`@|>>&"_*(col.csv)'
     , ORDER_STATUS CHAR(2) COMMENT '注文ステータス(col.csv)'
     , ORD_DATE DATETIME COMMENT '注文日時'
     , USER_ID CHAR(6) COMMENT 'ユーザーID'
     , SHIP_NAME VARCHAR(50) COMMENT '配送先氏名'
     , SHIP_POST_CD VARCHAR(8) COMMENT '配送先,郵便番号'
     , SHIP_ADDR VARCHAR(256) COMMENT '配送先住所'
     , SHIP_TEL VARCHAR(16) COMMENT '配送先電話番号'
     , TOTAL_UNIT INT(11) NOT NULL DEFAULT 0 COMMENT '数量合計'
     , TOTAL_GROSS BIGINT(20) NOT NULL DEFAULT 0 COMMENT '合計金額'
     , DELIV_FEE MEDIUMINT(9) NOT NULL DEFAULT 0 COMMENT '配送料'
     , DELIV_MAXDATE DATETIME COMMENT '配送指定日'
     , DELIV_DATE DATETIME COMMENT '配送日'
     , DELIV_COMP_DATE DATETIME COMMENT '配達完了日'
     , DEL_FLG TINYINT(4) DEFAULT 0 COMMENT '削除フラグ(table-col.csv)'
     , CRE_REC DATETIME(6) DEFAULT current_timestamp(6) COMMENT '登録日(col.csv)'
     , CRE_EMP_CD VARCHAR(4) COMMENT '登録社員コード'
     , UPD_REC DATETIME(6) DEFAULT current_timestamp(6) COMMENT '更新日時'
     , UPD_EMP_CD VARCHAR(4) COMMENT '更新社員コード'
     , PRIMARY KEY (ORDER_NO)
) COMMENT = '注文テーブル'
;

-- ALL DATA COPY ( T0001_TEMP -> T0001 ) AND DATA CHECK -------------------
INSERT INTO T0001 (
       ORDER_NO
     , ORDER_STATUS
     , ORD_DATE
     , USER_ID
     , SHIP_NAME
     , SHIP_POST_CD
     , SHIP_ADDR
     , SHIP_TEL
     , TOTAL_UNIT
     , TOTAL_GROSS
     , DELIV_FEE
     , DELIV_MAXDATE
     , DELIV_DATE
     , DELIV_COMP_DATE
     , DEL_FLG
     , CRE_REC
     , CRE_EMP_CD
     , UPD_REC
     , UPD_EMP_CD
)
     SELECT ORDER_NO
          , ORDER_STATUS
          , ORD_DATE
          , USER_ID
          , SHIP_NAME
          , SHIP_POST_CD
          , SHIP_ADDR
          , SHIP_TEL
          , TOTAL_UNIT
          , TOTAL_GROSS
          , DELIV_FEE
          , DELIV_MAXDATE
          , DELIV_DATE
          , DELIV_COMP_DATE
          , DEL_FLG
          , CRE_REC
          , CRE_EMP_CD
          , UPD_REC
          , UPD_EMP_CD
       FROM T0001_TEMP
;
SELECT COUNT(*) FROM T0001_TEMP
;
SELECT COUNT(*) FROM T0001
;
COMMIT
;

-- CREATE INDEX ( T0001 ) -------------------------------------------------
CREATE INDEX IDX_T0001_01
ON T0001
(USER_ID)
;
CREATE INDEX IDX_T0001_02
ON T0001
(ORD_DATE)
;
CREATE INDEX IDX_T0001_03
ON T0001
(ORDER_STATUS)
;

-- ADD FOREIGN KEY (OTHER TABLE) ------------------------------------------
ALTER TABLE T0002 ADD CONSTRAINT FK_T0002_01 FOREIGN KEY (ORDER_NO)
  REFERENCES T0001 (ORDER_NO)
    ON DELETE CASCADE
    ON UPDATE CASCADE
;
ALTER TABLE T0008 ADD FOREIGN KEY (ORDER_NO)
  REFERENCES T0001 (ORDER_NO)
;

-- DROP TABLE OR RECOVERY ( T0001_TEMP ) ----------------------------------
DROP TABLE IF EXISTS T0001_TEMP
;
ALTER TABLE T0001_TEMP RENAME TO T0001
;