表主键自动设置为 not null 必须

本文介绍: 主键非空必选项

SQL> create table abc as select *From dba_objects;

Table created

SQL> alter table ABC add constraint asdf primary key (OWNER, OBJECT_NAME,OBJECT_ID);
ORA-01449: column contains NULL values; cannot alter to NOT NULL

SQL> alter table ABC add constraint asdf unique key (OWNER, OBJECT_NAME,OBJECT_ID);
ORA-00906: missing left parenthesis

SQL> alter table ABC add constraint asdf unique (OWNER, OBJECT_NAME,OBJECT_ID);

Table altered

SQL> delete from abc where object_id is null;

2 rows deleted

SQL> alter table ABC add constraint asdf primary key (OWNER, OBJECT_NAME,OBJECT_ID);
ORA-02261: such unique or primary key already exists in the table

SQL> alter table ABC drop constraint asdf;

Table altered

SQL> alter table ABC add constraint asdf primary key (OWNER, OBJECT_NAME,OBJECT_ID);

Table altered

SQL>

自动变成非空！

SQL> alter table ABC modify OBJECT_ID null;
ORA-01451: column to be modified to NULL cannot be modified to NULL

SQL> alter table ABC disable constraint asdf ;

Table altered

SQL> alter table ABC modify OBJECT_ID null;
ORA-01451: column to be modified to NULL cannot be modified to NULL

隐藏了非空！但是也不能变成NULL

SQL> alter table ABC modify OBJECT_ID null;——-隐试非空
ORA-01451: column to be modified to NULL cannot be modified to NULL

SQL> alter table ABC modify OBJECT_ID not null;

Table altered

SQL> alter table ABC modify OBJECT_ID null;

Table altered

SQL>

PK对应的index建不建立都可以，名字可以同也可以不同，根据字段的判定的。如果是PK带出来的index，drop后就会连index一起drop ，否则index不会drop

语法：
CREATE TABLE schema_name.table_name (
column_1 data_type column_constraint,
column_2 data_type column_constraint,
…
table_constraint
);

案例1：直接创建表，不设置约束

CREATE TABLE tbl_students (
   stu_num CHAR(10) NOT NULL,
   stu_name VARCHAR2(10) NOT NULL,
   stu_sex CHAR(4) NOT NULL,
   stu_age NUMBER(3),
   stu_tel NUMBER(11)
)

// 给学生表新增一列
ALTER TABLE tbl_students ADD stu_email VARCHAR2(20);

// 新增
ALTER TABLE tbl_students ADD stu_email VARCHAR2(20);

// 修改列，仅支持修改类型和约束
ALTER TABLE tbl_students MODIFY stu_email VARCHAR2(50)

// 删除列
ALTER TABLE tbl_students DROP COLUMN stu_email

// 删除表
DROP TABLE PERSONS;

注：alter不能和本身冲突，比如性别保存男，3个字符，再修改会2个字符就会报错

> ORA-01441: cannot decrease column length because some value is too big

案例2：主键

主键数据表中的一个或多个字段，用于唯一表示数据表中的一条数据
主键所在字段唯一且不为空，分为单列主键和多列主键

单列主键：

创建成功后，查看设计表时，可以看到键

// 在创建表时创建主键
CREATE TABLE tbl_students (
   stu_num CHAR(10) primary key,
   stu_name VARCHAR2(10) NOT NULL,
   stu_sex CHAR(2) NOT NULL,
   stu_age NUMBER(3),
   stu_tel NUMBER(11)
)

CREATE TABLE tbl_students (
   stu_num CHAR(10),
   stu_name VARCHAR2(10) NOT NULL,
   stu_sex CHAR(2) NOT NULL,
   stu_age NUMBER(3),
   stu_tel NUMBER(11)，
   primary key(stu_num)
)

// 为列添加约束
ALTER TABLE tbl_students ADD CONSTRAINTS pk_student primary key(stu_num)

注：添加主键的修改不能和本身冲突，当表stu_num未标注为not null时，如果数据没有null，也是可以添加约束的，但是如果数据有null或者重复，则会报错，造成添加失败，强制not null

> ORA-01449: column contains NULL values; cannot alter to NOT NULL

ORA-02437: cannot validate (WATERBOSSFACTORY.PK_STUDENT) – primary key violated（用重复的值）

案例3：联合主键

使用两个或两个以上的字段作为主键

// 创建联合主键
CREATE TABLE tbl_grads(
   course_id char(3),
   course_name VARCHAR2(50) ,
   stu_num CHAR(10),
   stu_name VARCHAR2(10),
   score NUMBER(3),
   primary key (course_id,stu_num)
)

// 下面的会报错， ORA-02260: table can have only one primary key
CREATE TABLE tbl_grads(
   course_id char(3) primary key,
   course_name VARCHAR2(50) ,
   stu_num CHAR(10),
   stu_name VARCHAR2(10),
   score NUMBER(3) primary key
)

ALTER TABLE tbl_grads ADD CONSTRAINTS pk_grads primary key(course_id,stu_num)
联合主键仅

注：

（1）只要有一个为空或者联合主键所在的字段都重复，则插入数据或者alter添加主键就会失败

ORA-01449: column contains NULL values; cannot alter to NOT NULL

> ORA-02437: cannot validate (WATERBOSSFACTORY.PK_GRADS) – primary key violated 两字段都重复

案例4：外键约束

主外键关联，即限定外键字段的值必须来自与其它数据表中的关联字段，一般是主键

// 创建表时指定外键如果外键关联的表不存在，则会报错 ORA-00942: table or view does not exist
CREATE TABLE tbl_students(
   stu_num CHAR(10) primary key,
   stu_name VARCHAR2(10) NOT NULL,
   stu_sex CHAR(4) NOT NULL,
   stu_age NUMBER(3),
   stu_tel NUMBER(11),
   stu_cid CHAR(3) not NULL,
   CONSTRAINT fk_student_classes foreign key (stu_cid) references tbl_classes(class_id) on DELETE cascade
)

// on delete cascade代表删除班级表的主键时，学生表的相关数据也会删除

// 添加外键约束
ALTER TABLE tbl_students ADD CONSTRAINTS fk_student_classes foreign key (stu_cid) references tbl_classes(class_id) on DELETE cascade;

// 删除外键约束
ALTER TABLE TBL_STUDENTS DROP CONstraints fk_student_classes;

注：

（1）两个表的删除逻辑是必须先删除班级表，然后再删除学生表，因为学生表的外键指向了班级表的主键

（2）设置了on delete cascade后，在删除班级表中的数据时，会同时删除掉班级的学生

案例5：CHECK约束

是检查约束，用于限定每一列能够输入的值，以保证数据的正确性

// 添加check约束
CREATE TABLE tbl_students(
   stu_num CHAR(10) primary key,
   stu_name VARCHAR2(10) NOT NULL,
   stu_sex CHAR(4) NOT NULL,
   stu_age NUMBER(3),
   stu_tel NUMBER(11),
   stu_cid CHAR(3) not NULL,
   CONSTRAINT fk_student_classes foreign key (stu_cid) references tbl_classes(class_id) on DELETE cascade,
   CONSTRAINT ck_student_sex CHECK(stu_sex=’男’ or stu_sex=’女’)，
   CONSTRAINT ck_student_age CHECK(stu_age BETWEEN 6 AND 30)
)

ALTER TABLE TBL_STUDENTS DROP constraints ck_student_age;

ALTER TABLE tbl_students add constraints ck_student_age CHECK(stu_age BETWEEN 6 AND 30);

添加好check约束后如下：

此时添加数据不满足约束时，会报错

案例6：UNIQUE约束

用于限定字段的唯一性，唯一键添加方式有三种，一种是在字段直接添加unique，一种是使用CONSTRANT指定，这两种的区别是直接添加unique时唯一键的名称是随机的，CONSTRANT是指定的。

// 电话是唯一的，添加唯一约束
CREATE TABLE tbl_students(
   stu_num CHAR(10) primary key,
   stu_name VARCHAR2(10) NOT NULL,
   stu_sex CHAR(4) NOT NULL,
   stu_age NUMBER(3),
   stu_tel NUMBER(11) UNIQUE,
   stu_cid CHAR(3) not NULL ,
   CONSTRAINT fk_student_classes foreign key (stu_cid) references tbl_classes(class_id) on DELETE cascade,
   CONSTRAINT ck_student_sex CHECK(stu_sex=’男’ or stu_sex=’女’)，
   CONSTRAINT ck_student_age CHECK(stu_age BETWEEN 6 AND 30)
)

CREATE TABLE tbl_students(
   stu_num CHAR(10) primary key,
   stu_name VARCHAR2(10) NOT NULL,
   stu_sex CHAR(4) NOT NULL,
   stu_age NUMBER(3),
   stu_tel NUMBER(11),
   stu_cid CHAR(3) not NULL ,
   CONSTRAINT fk_student_classes foreign key (stu_cid) references tbl_classes(class_id) on DELETE cascade,
   CONSTRAINT uq_student_tel UNIQUE(stu_tel),
   CONSTRAINT ck_student_sex CHECK(stu_sex=’男’ or stu_sex=’女’)，
   CONSTRAINT ck_student_age CHECK(stu_age BETWEEN 6 AND 30)
)

ALTER TABLE TBL_STUDENTS DROP constraints uq_student_tel;