oracle 表约束非空,表的约束--非空和检查约束 not null, check

1 列级实现

drop table testcons_lzh_88;

create table testcons_lzh_88(

id number primary

key,

name varchar2(20) not

null,

userid varchar2(18)

unique,

sal number(11,2)

check(sal>2000)

);

insert into testcons_lzh_88(id, userid, sal)

values(1, '1234', 3000)

*

ERROR at line 1:

ORA-01400: cannot insert NULL into

("OPENLAB"."TESTCONS_LZH_88"."NAME")

insert into testcons_lzh_88 values(1, 'li',

'1234', 3000);

insert into testcons_lzh_88(id, name, userid,

sal) values(4, 'zhang', '1234', 1500);

*

ERROR at line 1:

ORA-02290: check constraint

(OPENLAB.SYS_C0027472) violated

2 表级实现

drop table testcons_lzh_88;

create table testcons_lzh_88(

id number unique,

name varchar2(20) not

null,

userid varchar2(18)

unique,

sal number(11,2)

check(sal>2000)

);

insert into testcons_lzh_88 values(1, 'li',

'1234', 3000);

insert into testcons_lzh_88 values(1,

'zhang','123', 3000);

insert into testcons_lzh_88 values(2,

'zhang','123', 3000);

drop table testcons_lzh_88;

create table testcons_lzh_88(

id number ,

name varchar2(20) not

null,

userid

varchar2(18),

sal number(11,2),

unique(id,

userid),

chekc(sal>3000)

);

3

从业务层面，没有多个字段联合非空的需要，所有Oracle没有提供非空约束的表级实现。