南大通用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.