"ORACLE" Built-in constraints (11g)

The NOT NULL constraint can be defined only at the column level, and the name of the NOT NULL constraint may be specified, and the name is automatically generated if unspecified

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 (a) NOT NULL constraint uk_student_sname1 UNIQUE,IDC ARD VARCHAR2 (+), 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 (a) NOT null Unique,idcard VARCHAR2 (+), Createtime D Ate 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

PRIMARY KEY constraint

1. Row-level definition primary key, System assigns primary key name

CREATE TABLE T_PK1
(Sno number (5) primary key,
Sname varchar2 (20),
Idcard VARCHAR2 (18),
Createtime Date
);

2. Table-level definition primary key, custom primary key name was

CREATE TABLE T_pk2
(Sno number (5),
Sname varchar2 (20),
Idcard VARCHAR2 (18),
Createtime date,
Constraint Pk_t_pk2_sno primary KEY (SNO)
);

3. Table-level definition primary key, System assigns primary key name

CREATE TABLE T_pk3
(Sno number (5),
Sname varchar2 (20),
Idcard VARCHAR2 (18),
Createtime date,
Primary KEY (SNO)
);

4. Row-level definition primary key, custom primary key name, and specify the table space used by the index
CREATE TABLE T_pk4
(Sno number (5) Constraint Pk_t_pk4_sno primary key using index tablespace example,
sname varchar2 (),
Idcard varchar2 (),
Createtime Date
);
5. Table-level definition primary key, custom primary key name, and specify table space used by index
CREATE TABLE T_PK5
(Sno number (5),
sname varchar2 (),
Idcard varchar2 (),
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_ ');

View PRIMARY KEY condition

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

View PRIMARY key Auto-create INDEX condition

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

Clean up the environment

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;

Add primary key to the command line, customize the primary key name

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);

The command line increments the primary key, customizes the primary key name, and specifies the automatically created index tablespace

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;

"ORACLE" Built-in constraints (11g)