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;
添加唯一键后可以在唯一键的地方查看
如果添加重复的数据，会报错
案例7：NOT NULL
已经一直使用，不再赘述
案例8：使用断言
案例9：触发器