informix-table

/****************************************************************************************/
informix table 操作
/****************************************************************************************/
1 创建表 
CREATE TABLE a100(a INT not null primary key, b INT, c INT ,d int ,e int,YEAR char(4),PROVINCE varchar(30));
CREATE TABLE a101(a INT not null primary key, b INT, c INT ,d int ,e int,YEAR char(4),PROVINCE varchar(30));
CREATE TABLE a102(a INT not null , b INT, c INT ,d int ,e int,f int ,YEAR char(4),PROVINCE varchar(30));
ALTER TABLE a102 ADD CONSTRAINT PRIMARY KEY(a,b) CONSTRAINT pk_a102_a_b;


2 创建分区表
1） 列表分区
CREATE TABLE   b$a200 (a INTEGER ,b INTEGER,c INTEGER, d INTEGER,e INTEGER,f int,g int unique ,
YEAR CHAR(4),province VARCHAR(30),status char(1) default '1',DBVERSION datetime year to second,
 CHECK ((b<1000 )AND (e<2000 ) ) CONSTRAINT chk_b$a200_11,
 CHECK ((c<2000 )AND (e<3000 ) ) CONSTRAINT chk_b$a200_12,
 CHECK ((d<3000 )AND (e<4000 ) ) CONSTRAINT chk_b$a200_3 
 ) FRAGMENT BY LIST (YEAR)
PARTITION p2016 VALUES ('2016') IN frag1,
PARTITION p2017 VALUES ('2017') IN frag1,
PARTITION p2018 VALUES ('2018') IN frag1
;


2）表达式分区
CREATE TABLE   b$a300 (a INTEGER,b INTEGER,c INTEGER, d INTEGER,e INTEGER,f int,g int unique,YEAR CHAR(4),province VARCHAR(30),status char(1) default '1',DBVERSION datetime year to second,
 CHECK ((b<1000 )AND (e<2000 ) ) CONSTRAINT chk_b$a300_11,
 CHECK ((c<2000 )AND (e<3000 ) ) CONSTRAINT chk_b$a300_12,
 CHECK ((d<3000 )AND (e<4000 ) ) CONSTRAINT chk_b$a300_3 
 ) partition by EXPRESSION
PARTITION p8700_y2016 (province='8700' and year='2016') IN frag1,
PARTITION p8700_y2017 (province='8700' and year='2017') IN frag1,
PARTITION p8800_y2016 (province='8800' and year='2016') IN frag1,
PARTITION p8800_y2017 (province='8800' and year='2016') IN frag1
;




3 表的 各种约束
3.1 CHECK
CREATE TABLE   b$a200 (a INTEGER ,b INTEGER,c INTEGER, d INTEGER,e INTEGER,f int,g int unique ,
YEAR CHAR(4),province VARCHAR(30),status char(1) default '1',DBVERSION datetime year to second,
 CHECK ((b<1000 )AND (e<2000 ) ) CONSTRAINT chk_b$a200_11,
 CHECK ((c<2000 )AND (e<3000 ) ) CONSTRAINT chk_b$a200_12,
 CHECK ((d<3000 )AND (e<4000 ) ) CONSTRAINT chk_b$a200_3 
 )  ;


3.2 主键
ALTER TABLE b$a200 ADD CONSTRAINT PRIMARY KEY(a) CONSTRAINT pk_b$a200_a;
3.3 外键
ALTER TABLE b$a200 ADD CONSTRAINT (FOREIGN KEY(b) REFERENCES a100(a) CONSTRAINT fk_b$a200_b  ON DELETE CASCADE ) ;
ALTER TABLE b$a200 ADD CONSTRAINT (FOREIGN KEY(c) REFERENCES a101(a) CONSTRAINT fk_b$a200_c  ON DELETE CASCADE ) ;
ALTER TABLE b$a200 ADD CONSTRAINT (FOREIGN KEY(e,f) REFERENCES a102(a,b) CONSTRAINT fk_b$a200_ef  ) ;




3.4 唯一
CREATE TABLE p$a400 (
chk_id SERIAL PRIMARY KEY,
acct1 MONEY CHECK (acct1 BETWEEN 0 AND 99999),
acct2 MONEY CHECK (acct2 BETWEEN 0 AND 99999),
acct3 int unique,
acct4 int  ,
acct5 int not null,
acct6 int not null,
YEAR CHAR(4),province VARCHAR(30),status char(1),DBVERSION datetime year to second,
 FOREIGN KEY(acct4) REFERENCES a100(a),
 FOREIGN KEY(acct5,acct6) REFERENCES a102(a,b));
 
4 索引
create index idx_p$400_acct4 on p$a400(acct1);
create index idx_b$a200_1 on b$a200(a,b);
create index idx_b$a200_2 on b$a200(a,b,c);
create index idx_b$a200_3 on b$a200(a,b,c,d);


5 视图
create view a200 as select a,b ,c   from b$a200 ;
create view a300 as select a,b ,c   from b$a300 ;


6 序列
create sequence secu_seq_fq;