创建表:
create table student(
id number,
name varchar2(10),
phone number,
class varchar2(10)
);
六大约束
表级约束和行级约束:
表级约束:
create table student(
id number,
constraint pk_id primary key(id)
);行级约束:
create table student(
id number primary key(id)
);
1.主键约束(Primay Key Coustraint) 唯一性,非空性
表级别设置主键:
建表时定义:
create table student(
id number,
constraint pk_id primary key(id)
);外部定义:alter table student add constraint pk_id primary key(id);
2.唯一约束 (Unique Counstraint)唯一性,可以空,但只能有一个
建表后创建唯一约束:alter table student add constraint uk_phone unique(phone);
3.检查约束 (Check Counstraint) 对该列数据的范围、格式的限制(如:年龄、性别等)
建表后创建检查约束:alter table student add constraint ck_class check(class>=1);
4.默认约束 (Default Counstraint) 该数据的默认值
建表后创建默认约束:alter table student modify (name varchar2(10) default 'stu1');
5.外键约束 (Foreign Key Counstraint) 需要建立两表间的关系并引用主表的列
create table school(
sname varchar2(30),
sphone number,
smoney number(20),
swhere varchar2(30)
snumber number
);create table student(
id number,
name varchar2(10),
phone number,
class varchar2(10),
sname varchar2(30)
);
为school表中sname设置主键:
alter table school add constraint school_sname_pk primary key(sname);
为student表中sname设置外键:
alter table student add constraint student_sname_fk foreign key(sname) references school(sname);
6.非空约束(NOT NULL)
alter table student modify class not null;