not null约束只能在列级定义；可以指定not null约束的名称，如不指定则自动生成名称
SQL> create table t1 (x number not null, y number constraint nn_t111_y not null);
Table created.
SQL> select constraint_name,constraint_type,search_condition from user_constraints where table_name = upper('t1');
CONSTRAINT_NAME C SEARCH_CONDITIO
------------------------------ -- ---------------------
SYS_C0011402 C "X" IS NOT NULL
NN_T111_Y C "Y" IS NOT NULL
SQL> create table student1 (sno number(5) not null, sname varchar2(20) not null constraint uk_student_sname1 unique,idcard varchar2(18),createtime
date default sysdate not null,constraint uk_student_idcard1 unique(idcard));
Table created.
SQL> create table student2 (sno number(5) not null, sname varchar2(20) not null unique,idcard varchar2(18),createtime date default sysdate not null,
constraint uk_student_idcard2 unique(idcard));
Table created.
SQL> select table_name,column_name,constraint_name from user_cons_columns where table_name like upper('student_');
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME
--------------- --------------- ---------------
STUDENT1 SNO SYS_C0011404
STUDENT1 SNAME SYS_C0011405
STUDENT1 CREATETIME SYS_C0011406
STUDENT1 SNAME UK_STUDENT_SNAM
E1
STUDENT1 IDCARD UK_STUDENT_IDCA
RD1
STUDENT2 SNO SYS_C0011409
STUDENT2 SNAME SYS_C0011410
STUDENT2 CREATETIME SYS_C0011411
STUDENT2 SNAME SYS_C0011412
STUDENT2 IDCARD UK_STUDENT_IDCARD2
主键约束
1、行级定义主键，系统分配主键名称
create table t_pk1
(sno number(5) primary key,
sname varchar2(20),
idcard varchar2(18),
createtime date
);
2、表级定义主键，自定义主键名曾
create table t_pk2
(sno number(5),
sname varchar2(20),
idcard varchar2(18),
createtime date,
constraint pk_t_pk2_sno primary key(sno)
);
3、表级定义主键，系统分配主键名称
create table t_pk3
(sno number(5),
sname varchar2(20),
idcard varchar2(18),
createtime date,
primary key(sno)
);
4、行级定义主键，自定义主键名称，并且指定索引使用的表空间
create table t_pk4
(sno number(5) constraint pk_t_pk4_sno primary key using index tablespace example,
sname varchar2(20),
idcard varchar2(18),
createtime date
);
5、表级定义主键，自定义主键名称，并且指定索引使用的表空间
create table t_pk5
(sno number(5),
sname varchar2(20),
idcard varchar2(18),
createtime date,
constraint pk_t_pk5_sno primary key(sno) using index tablespace example
);
SQL> select table_name,column_name,constraint_name from user_cons_columns
2 where table_name like upper('t_pk_');
查看主键情况
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME
--------------- --------------- ---------------
T_PK1 SNO SYS_C0011414
T_PK2 SNO PK_T_PK2_SNO
T_PK3 SNO SYS_C0011416
T_PK4 SNO PK_T_PK4_SNO
T_PK5 SNO PK_T_PK5_SNO
查看主键自动创建索引情况
SQL> select index_name,uniqueness,tablespace_name
2 from user_indexes
3 where table_name like upper('t_pk_');
INDEX_NAME UNIQUENES TABLESPACE_NAME
------------------------------ --------- ------------------------------
SYS_C0011414 UNIQUE USERS
PK_T_PK2_SNO UNIQUE USERS
SYS_C0011416 UNIQUE USERS
PK_T_PK5_SNO UNIQUE EXAMPLE
PK_T_PK4_SNO UNIQUE EXAMPLE
清理环境
drop table t_pk1 purge;
drop table t_pk2 purge;
drop table t_pk3 purge;
drop table t_pk4 purge;
drop table t_pk5 purge;
命令行增加主键，自定义主键名称
table t_pk6
(sno number(5),
sname varchar2(20),
idcard varchar2(18),
createtime date
);
alter table t_pk6 add constraint pk_t_pk6_sno primary key(sno);
命令行增加主键，自定义主键名称，并且指定自动创建的索引表空间
create table t_pk7
(sno number(5),
sname varchar2(20),
idcard varchar2(18),
createtime date
);
添加check约束，保证所有的名字都为大写
alter table t_pk7 add constratint pk_t_pk7_sno primary key(sno) using index tablespace example;
2 (id number(5),
3 name varchar(20) constraint ck_t115_name check(name = upper(name)),
4 createtime date default sysdate
5 );
Table created.
SQL> insert into t115 values (1,'SL',null);
1 row created.
SQL> insert into t115 values (2,'ss',sysdate);
insert into t115 values (2,'ss',sysdate)
*
ERROR at line 1:
ORA-02290: check constraint (HR.CK_T115_NAME) violated
ALTER TABLE t1161 MODIFY LAST_NAME NULL;
ALTER TABLE t1161 MODIFY EMAIL NULL;
ALTER TABLE t1161 MODIFY HIRE_DATE NULL;
ALTER TABLE T1161 MODIFY job_id NULL;
select index_name,index_type,uniqueness from user_indexes where table_name = 't1161';