ORACLE的5种约束

最新推荐文章于 2023-05-11 12:55:26 发布

最新推荐文章于 2023-05-11 12:55:26 发布 · 239 阅读

文章标签：

本文详细介绍了SQL中各种约束的使用方法，包括notnull、unique、check、primarykey和foreignkey等约束，并提供了创建表及添加约束的具体语法示例。

[size=large][color=darkblue]not null约束
1：
create table student(
sid int(3),
name varchar(10)not null
);
2:
create table student1(
sid int(3),
name varchar(10)
);
--nysql:alter table student1 modify name varchar(10) not null;
--oracle:alter table student1 modify name not null;
unique 约束：
1:
create table student3(
sid int(3),
name varchar(10) unique
);
2:
create table student4(
sid int(3),
name varchar(10),
constraint unique_student4_sid unique (sid)
);
3:
create table student5(
sid int(3),
name varchar(10)
);
alter table student5 add constraint unique_student5_sid unique(sid);
check约束
1:
create table student6(
sid int(3),
name varchar(10),
age int(3) check (age between 0 and 120)
);
2:
create table student7(
sid int(3),
name varchar(10)not null,
age int(3),
constraint check_studnet7_age check(age between 0 and 120)
);
3:
create table student8(
sid int(3),
name varchar(10)not null
);
alter table student8 add age int(3) check(age between 1 and 120)
4:
create table student9(
sid int(3),
name varchar(10)not null,
age int(3)
);
alter table student9 add constraint check (age between 1 and 120)
primary key 约束：
1:
create table student10(
sid int(3) primary key,
name varchar(10)not null
);
2:
create table student11(
sid int(3),
name varchar(10)not null,
constraint pk_student10_name primary key (sid)
);
3:
create table student12(
sid int(3),
name varchar(10)not null
);
alter table student12 add constraint pk_student12_sid primary key(sid)
foreign key外键
1:
create table student13(
sid int(3),
name varchar(10)not null,
classid int(3) references class(id)
);
2:
create table class(
id int(3) primary key,
name varchar(10));
create table student14(
sid int(3),
name varchar(10)not null,
classid int(3),
constraint fk_student14)_sid foreign key (classid)
references class(id);
3:
create table student15(
sid int(3),
name varchar(10)not null,
classid int(3));
alter table student15 constraint fk_student15_classid foreign key (calssid) references class(id)[/color][/size]