not null-unique-primarykey-check
关于not_null
A column in a table can be specified not null. It’s not possible to insert a null in such a column. The default is null. So, in the following create table statement, a null can be inserted into the column named c.
可以去掉表中关于a 列的not null
Unique Key
The unique constraint doesn’t allow duplicate values in a column. If the unique constraint encompasses two or more columns, no two equal combinations are allowed.
However, if a column is not explicitely defined as not null, nulls can be inserted multiple times:
如果插入的值不唯一就会报错，
当然，你可以在一个表里创建多个唯一性约束
Primary Key
On a technical level, a primary key combines a unique and a not null constraint. Additionally, a table can have at most one primary key. After creating a primary key, it can be referenced by a foreign key.
下面介绍两种主键的创建方法：
Foreign Key
A foreign key constraint (also called referential integrity constraint) on a column ensures that the value in that column is found in the primary key of another table.
If a table has a foreign key that references a table, that referenced table can be dropped with a drop table .. cascade constraints.
It is not possible to establish a foreign key on a global temporary table. If tried, Oracle issues a ORA-14455: attempt to create referential integrity constraint on temporary table.
Check
A check constraint allows to state a minimum requirement for the value in a column. If more complicated requirements are desired, an insert trigger must be used.
The following table allows only numbers that are between 0 and 100 in the column a;
create table a_check_1 (
a number check (a between 0 and 100),
b number
);
Check constraints can be added after a table had been created:
alter table a_check_1
add constraint ch_b check (b > 50);
It is also possible to state a check constraint that check the value of more than one column. The following example makes sure that the value of begin_ is smaller than the value of end_.
create table a_check_2
begin_ number,
end_ number,
value_ number,
check (begin_ < end_)
);
1、创建表
CREATE TABLE students
(
student_id VARCHAR2(10) NOT NULL,
student_name VARCHAR2(30) NOT NULL,
college_major VARCHAR2(15) NOT NULL,
status VARCHAR2(20) NOT NULL,
state VARCHAR2(2),
license_no VARCHAR2(30)
);
2、创建主键
ALTER TABLE students
ADD CONSTRAINT pk_students
PRIMARY KEY (student_id) USING INDEX ;
3、创建Unique约束
ALTER TABLE students
ADD CONSTRAINT uk_students_license
UNIQUE (state, license_no) USING INDEX ;
4、创建Check约束
ALTER TABLE students ADD CONSTRAINT ck_students_st_lic
CHECK ((state IS NULL AND license_no IS NULL) OR
(state IS NOT NULL AND license_no is NOT NULL));
5、创建外键约束
ALTER TABLE students
ADD CONSTRAINT fk_students_state
FOREIGN KEY (state) REFERENCES state_lookup (state);
