-- 作業単価マスタ
drop table if exists constructionspecunitprice_OLD cascade;
CREATE TABLE constructionspecunitprice_OLD As Select * from constructionspecunitprice;

drop table if exists constructionspecunitprice cascade;

create table constructionspecunitprice (
  ConstructionTypeCode mediumint(8) unsigned not null comment '工事種別コード'
  , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
  , ItemCode decimal(5,0) unsigned not null comment '工種キー'
  , SpecCode decimal(5,0) unsigned not null comment '作業キー'
  , UnitPrice decimal(9,2) not null comment '単価'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint constructionspecunitprice_PKC primary key (ConstructionTypeCode,ComponentCode,ItemCode,SpecCode)
) comment '作業単価マスタ' ;

create index constructionspecunitprice_Index1
  on constructionspecunitprice(ConstructionTypeCode,ComponentCode,ItemCode);

create index constructionspecunitprice_Index2
  on constructionspecunitprice(ItemCode,SpecCode);

Insert INTO constructionspecunitprice
SELECT
  ConstructionTypeCode
  , ComponentCode
  , ItemCode
  , SpecCode
  , UnitPrice
  , EntryDate
  , UpdateDate
FROM constructionspecunitprice_OLD
;
commit;