主键约束primary key 具有唯一性和非空性和被引用性
非空 not null
唯一unique
=====================================
主键约束使用方式
create table emp
(
empo int primary key
)
或者
create table emp
(
empo int ,
primary key(empo)
)
或者在创建之后
alter table emp add primary key(empo)
删除主键
alter table emp drop primary key
create table emp
(
sid int primary key auto_increment;//自增长
)
==================================================
外键约束
create table dept
(
deptno int primary key auto_increment,
dname varchar(50);
)
create table emp
{
empo int primary key,
ename varchar(50),
dno int,
constraint fk_emp_dept foreign key(dno) references dept(deptno)//为dno创建外键约束,机必须在表dept存在的数据,dno才能引用
}
列如:
insert into dept values(10,'财力部');
insert into dept values(20,'人力部');
insert into emp values(1,''张三');// 外键可以为空
insert into emp values(1,''张三',10);
insert into emp values(1,''张三',30);//此时会报错,因为外键中没有30这个数字
============================================
一对一关系:从表的主建及是外键
=========================================
多对多关系:
create table student
(sid int primary key,
sname varchar(40);
create table teacher
(tid int primary key,
tname varchar(40));
create table stu_tea
-> (sid int,tid int,
-> constraint fk_tea foreign key(tid) references teacher(tid),
-> constraint fk_stu foreign key(sid) references student(sid));
====================================================================