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

副标题#e#

主要介绍一下个人对主键(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,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;

显示:2016/10/28

4、截取时间到小时时,sql语句:

select trunc(sysdate,‘hh‘) from table1 where id=123;

显示:2016/10/28 15:00:00

5、截取时间到分钟时,sql语句:

select trunc(sysdate,‘mi‘) from table1 where id=123;

显示:2016/10/28 15:11:00

6、截取时间到秒暂时不知道怎么操作

#p#副标题#e##p#分页标题#e#

7、不可直接用trunc(sysdate,‘yyyy-mm-dd‘),会提示“精度说明符过多”

相关文章

发表回复

您的电子邮箱地址不会被公开。