南大通用GBase 8s数据库的约束查询

关系模型定义了三类完整性约束：实体完整性、参照完整性和用户定义完整性。通过定义约束保证了关系数据的正确性、有效性和一致性。通过查询数据库中系统表可以查询创建的约束信息。

示例：

创建定义约束的表t_dept和t_employee

drop table if exists t_dept;

create table t_dept(f_deptid int primary key constraint pk_dept_deptid,
f_deptname varchar(20) not null unique constraint uni_dept_deptname);

drop table if exists t_employee;

create table t_employee(f_employeeid int primary key constraint pk_employee_employeeid,
f_deptid int references t_dept(f_deptid),
f_employeename varchar(20) not null,
f_showname varchar(20) not null unique constraint uni_employee_showname,
f_age int default 18 check (f_age >0 and f_age <= 120),
f_employeedate date default today);

访问表sysconstraints查询主键

database <db_name>;

select * from sysconstraints where constrtype = 'P';

> select * from sysconstraints where constrtype = 'P';

constrid    5

constrname  pk_dept_deptid

owner       gbasedbt

tabid       109

constrtype  P

idxname      109_5

collation   zh_CN.57372

constrid    8

constrname  pk_employee_employeeid

owner       gbasedbt

tabid       110

constrtype  P

idxname      110_8

collation   zh_CN.57372

2 row(s) retrieved.

访问表sysconstraints查询外键

database <db_name>;

select * from sysconstraints where constrtype = 'R';

> select * from sysconstraints where constrtype = 'R';

constrid    10

constrname  r110_10

owner       gbasedbt

tabid       110

constrtype  R

idxname      110_10

collation   zh_CN.57372

1 row(s) retrieved.

访问表sysconstraints查询唯一索引

database <db_name>;

select * from sysconstraints where constrtype = 'U';

> select * from sysconstraints where constrtype = 'U';

constrid    6

constrname  uni_dept_deptname

owner       gbasedbt

tabid       109

constrtype  U

idxname      109_6

collation   zh_CN.57372

constrid    9

constrname  uni_employee_showname

owner       gbasedbt

tabid       110

constrtype  U

idxname      110_9

collation   zh_CN.57372

2 row(s) retrieved.

访问表sysconstraints查询NULL

database <db_name>;

select * from sysconstraints where constrtype = 'N';

> select * from sysconstraints where constrtype = 'N';

constrid    1

constrname  n106_1

owner       gbasedbt

tabid       106

constrtype  N

idxname    

collation   zh_CN.57372

constrid    2

constrname  n107_2

owner       gbasedbt

tabid       107

constrtype  N

idxname    

collation   zh_CN.57372

constrid    7

constrname  n109_7

owner       gbasedbt

tabid       109

constrtype  N

idxname    

collation   zh_CN.57372

constrid    17

constrname  n111_17

owner       gbasedbt

tabid       111

constrtype  N

idxname    

collation   zh_CN.57372

constrid    18

constrname  n111_18

owner       gbasedbt

tabid       111

constrtype  N

idxname    

collation   zh_CN.57372

5 row(s) retrieved.

访问表sysdefaults查询默认值

database <db_name>;

select * from sysdefaults where tabid = <tab_id>;

> select * from sysdefaults where tabid = 111;

tabid    111

colno    5

type     L

default  AAAAEg 18

class    T

tabid    111

colno    6

type     T

default 

class    T

2 row(s) retrieved.

访问表sysconstraints、syschecks查询Check

database <db_name>;

select * from sysconstraints where constrtype = 'C';

select * from syschecks where type = 'T' and constrid = <constr_id>;

> select * from sysconstraints where constrtype = 'C';

constrid    13

constrname  c110_13

owner       gbasedbt

tabid       110

constrtype  C

idxname    

collation   zh_CN.57372

1 row(s) retrieved.

> select * from syschecks where type = 'T' and constrid = 13;

   constrid type  seqno checktext                       

       13     T      0   ((f_age > 0 ) AND (f_age <= 120

       13     T      1    ) )                            

2 row(s) retrieved.