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

);

alter table t_pk7 add constratint pk_t_pk7_sno primary key(sno) using index tablespace example;