--约束:主键、唯一、非空、默认、检查、外键
--数据的完整性:实体完整性、域完整性、自定义完整性、引用完整性
(1)第一种:写在每个字段后面
2 (
3 sno int primary key,
4 sname varchar2(20) not null unique,
5 sex varchar2(2) check(sex in ('男','女')),
6 age int check(age between 10 and 50),
7 address varchar2(20) default '湖南长沙'
8 )
9
10 select * from student; --查询表
11 drop table student; --删除表
(2)第二种:写在所有字段后面
2 (
3 sno int,
4 sname varchar2(20),
5 sex varchar2(2),
6 age int,
7 address varchar2(20),
8 constraint PK_STUDENT_SNO primary key(sno),
9 constraint CK_STUDENT_SEX check(sex in ('男','女')),
10 constraint CK_STUDENT_AGE check(age between 10 and 50)
11 )
12
13 --添加字段
14 alter table student
15 add address1 varchar2(30)
16
17 --删除字段(必须加上column关键字)
18 alter table student
19 drop column address1
20
21 --修改数据类型(注意这里要用modify)
22 alter table student
2 (
3 sno int,
4 sname varchar2(20),
5 sex varchar2(2),
6 age int,
7 address varchar2(20)
8 )
9
10 --default(默认约束) , unique(唯一约束) , not null(非空约束)
11 alter table student
12 modify address varchar2(30) default '湖南长沙'
13
14 alter table student
15 modify sname unique
16
17 alter table student
18 modify sname not null
19
20
21 --对于primary key,check,foreign key
22 alter table student
23 add constraint PK_STUDENT_SNO primary key(sno);
24
25 alter table student
26 add constraint CK_STUDENT_AGE check(sex in ('男','女'));
27
28 --删除约束
29 alter table student
30 drop constraint DF_STUDENT_ADDRESS
31
32 --外键(注意:如果是直接书写在字段后面的话,不需要显示的写上foreign key)
33 create table sc
34 (
35 sno int, --references student(sno), --在MYSQL同样不需要写foreign key
36 cno int,
37 scgrade float
38 )
39
40 alter table sc
41 drop constraint SYS_C005542;
42
43 alter table sc
44 add constraint FK_SC_STUDENT_SNO foreign key(sno) references student(sno);
45
46 select * from user_constraints; --查看该用户空间下的所有约束
47
48 --查看哪个表的哪个字段上面存在哪些约束
49 select * from user_cons_columns where table_name='STUDENT';
(3)第三种写法:创建表以后再添加约束
create table student
(
sno int,
sname varchar2(20),
sex varchar2(2),
age int,
address varchar2(20)
)
--default(默认约束) , unique(唯一约束) , not null(非空约束)
alter table student
modify address varchar2(30) default '湖南长沙'
alter table student
modify sname unique
alter table student
modify sname not null
--对于primary key,check,foreign key
alter table student
add constraint PK_STUDENT_SNO primary key(sno);
alter table student
add constraint CK_STUDENT_AGE check(sex in ('男','女'));
--删除约束
alter table student
drop constraint DF_STUDENT_ADDRESS
--外键(注意:如果是直接书写在字段后面的话,不需要显示的写上foreign key)
create table sc
(
sno int, --references student(sno), --在MYSQL同样不需要写foreign key
cno int,
scgrade float
)
alter table sc
drop constraint SYS_C005542;
alter table sc
add constraint FK_SC_STUDENT_SNO foreign key(sno) references student(sno);
select * from user_constraints; --查看该用户空间下的所有约束
--查看哪个表的哪个字段上面存在哪些约束
select * from user_cons_columns where table_name='STUDENT';