Oracle数据库-primary key/foreign key和references关系

转载

mob604756fa4732 2019-06-24 23:48:00

文章标签 外键主键字段 sql语句字段名 文章分类 代码人生

主要介绍一下个人对主键（primary key）、外键（foreign key）、候选键（Candidate key）、超键（super key）、references的总结

概念：

主键：用户选择元组标识的一个候选键，主键不允许为空

外键：来描述两个表的关系，外键可为空

超键：能唯一的标识元组的属性集

候选键：不含有多余属性的超键

实例：

假如有以下学生和教师两个表：

Student（student_no,student_name,student_age,student_sex,student_credit,teacher_no）

Teacher（teacher_no,teacher_name,teacher_salary）

超键：Student表中可根据学生编号（student_no），或身份证号（student_credit），或（学生编号，姓名）（student_no,student_name），或（学生编号，身份证号）（student_no,student_credit）等来唯一确定是哪一个学生，因此这些组合都可以作为此表的超键

候选键：候选键属于超键，且是最小的超键，即如果去掉超键组合中任意一个属性就不再是超键了。Student表中候选键为学生编号（student_no），身份证号（student_credit）

主键：主键是候选键中的一个，可人为决定，通常会选择编号来作为表的主键。现分别选取student_no，teacher_no作为Student表，Teacher表的主键

外键：teacher_no为两个表的公共关键字，且是Teacher表的主键，因此teacher_no是Student表的外键，用来描述Student表和Teacher表的关系

--References用法

创建一张Student表：

Create table Student(

student_no number（10） not null,

student_name varchar2(10) not null,

student_age number(4) not null,

student_sex varchar2(4) not null,

student_credit varchar2(18) not null,

teacher_no number(10) not null,

constraint PK_Student primary key(student_no) --设置主键

);

创建一张Teacher表：

Create table Teacher(

teacher_no number(10) not null,

teacher_name varchar2(10) not null,

teacher_salary number(10) not null,

constraint PK_Teacher primary key(teacher_no) --设置主键

);

--创建外键约束

alter table Student add constraint FK_Student_References_Teacher (teacher_no) references Teacher(teacher_no);

1.primary key

☆如果一个table有primary key，那么这个primary key 的value就不能为null，而且每条record就不能重复（完全相同），否则会发生如下错误

A.当primary key置为null时：ERROR 1048 (23000): Column 'id' cannot be null

B.当primary key 重复时：ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

例子：create table t2 ( id int(4) not null primary key, --auto_increment, name char(20) not null, sex int(4) not null default '0', degree double(16,2));

A.的sql语句：insert t2 values(1,'www',1,99.8);当执行两遍时报错

B.的sql语句：insert t2 values(null,'www',1,99.8);

结果：select * from t2;

+----+--------+------+----------+
| id | name | sex | degree |
+----+--------+------+----------+
| 1 | www | 1 | 99.80 |
+----+--------+------+----------+

否则，当table无primary key时

语句：create table t1 ( id int(4), name char(20));

C.insert t1 values (1,'www');可以执行n遍

D.insert t1 values (null,'www');也可以执行n遍

结果：select * from t1;

+--------+---------+
| id | name |
+--------+---------+
| 1 | www |
| 1 | www |
| NULL | www |
+--------+--------+

2.foreign key

外键的使用条件有三个

① 两个表必须是InnoDB表，MyISAM表暂时不支持外键
② 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；
③ 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

我们建立一个table：create table t1 ( id int(4), name char(20))type=innodb;并且建立索引create index t1_index on t1(name);

然后再建一个table：create table t3( id int(4),name char(20),foreign key(name) references t1(name)type=innodb );

那么 insert t3 values(1,'aaa');就会报错：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`anwei`.`t3`, CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`name`) REFERENCES `t1` (`name`))

但是在完成insert t1 values(1,'aaa');后就可以了，也就是values(1,'aaa');就不报错了，但是其他那么的值就又会报错的了。

说明：name是容许null值的，所以null值不受限制。
---------------------

创建一个字段，首先是字段名，然后是字段数据类型，后面的就是对此字段的约束，一般有not null ，unsigned，auto_increment，default 等等。

然后挨个解释题目中的五个概念

unique key：在一张表中可以标识一个列或多个列，因此它既可以看做列级约束、也可以看做表级约束。用来限制一个列中所有元素都不重复，列元素可以为null。
primary key：在一张表中只能有一个，来约束该列中所有元素不重复，且不能为null。
以上两者联系与区别：primary = unique + not null 是一个可取的说法；primary是一个索引，当系统要查询表中某条记录时，可以通过primary key标记的列，快速查询到所需的记录，所以这里primary修饰的列一般都是事先规定好，就是用来进行索引的列，比如：id int unsigned not null auto_increment; 这种列。而unique key 只是用来限制列元素不重复，并不一定用来快速检索，比如：我们想保证一个表中“身份证”字段的每一条记录不重复 or “电话号”字段每一条记录不重复，就可以用unique进行约束，而此时这样的列跟索引查找并没有关系。
foreign key：一个表中的 foreign key 指向另一个表中的 unique key （当然也可以是primary key，而且一般就应该是primary key吧，毕竟primary key没null的值）。就是一张表中，foreign标识的列，其中每一个元素，都能对应到另一张表中unique标识的列元素。
index / key ：两者一般可以划等号，同等看待。其功能：如果要像primary key标识的字段一样，能够利用索引快速的查询得到结果怎么办？primary key一张表只能设置一个，此时就可以用index / key 来给一个字段列加索引，从而大大加快该列中元素的查找速度。
---------------------

trunc 函数可用于截取日期时间

用法：trunc（字段名，精度）

具体实例：

在表table1中，有一个字段名为sysdate，该行id=123，日期显示：2016/10/28 15:11:58

1、截取时间到年时，sql语句如下：

select trunc(sysdate,'yyyy') from table1 where id=123; --yyyy也可用year替换

显示：2016/1/1

2、截取时间到月时，sql语句：

select trunc(sysdate,'mm') from table1 where id=123;

显示：2016/10/1

3、截取时间到日时，sql语句：

select trunc(sysdate,'dd') from table1 where id=123;