精华内容
下载资源
问答
  • NULL 博文链接:https://yanzhenwei.iteye.com/blog/850724
  • (还未加外键约束) 1. mysql> show columns from message;//信息表 +-----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------...
  • MySQL数据库——外键约束

    千次阅读 2019-05-06 16:47:23
    文章目录方案一:单张表方案二:两张表方案三:两张表并添加外键约束 把用户信息及其收货地址保存在数据中 方案一:单张表 创建表 create table user_info( id char(36) primary key, user_name varchar(30) not ...


    把用户信息及其收货地址保存在数据中

    方案一:单张表

    创建表

    create table user_info(
      id char(36) primary key,
      user_name varchar(30) not null,
      password varchar(30) not null,
      real_name varchar(8),
      mobile char(11),
      address varchar(150)
    );
    

    表中的id使用由java工具类生成的UUID保证id的唯一性
    插入数据

    insert into user_info (id,user_name,password,real_name,mobile,address) 
    values ('1e8e8d94-61b3-40b3-8e80-4890119d7c74','appache','123456','Lucy','18920120206','USA');
    
    insert into user_info (id,user_name,password,real_name,mobile,address) 
    values ('e6952417-96f9-4f61-8f8e-a67ff6b4bb69','appache','123456','Tom','18617297545','UE');
    
    insert into user_info (id,user_name,password,real_name,mobile,address)
    values ('e4d4aaf2-6412-41ad-9157-acec294f0b36','appache','123456','Tim','17694976949','Japan');
    

    插入数据后的user_infor表
    由于每个用户可以有多个收货地址,可以看出这种方法创建的表存在较为严重的字段冗余(user_name和password列),而随着该表中的个人信息字段越来越多,则这一问题表现的就越严重,所以不推荐使用。为了解决字段冗余的问题我们可以创建两张表,一张用来保存个人信息,另外一张用来保存收货地址。

    方案二:两张表

    创建表user_info

    create table user_info(
      id char(36) primary key,
      user_name varchar(30) unique not null,
      password varchar(30) not null
    )
    

    插入数据

    insert into user_info (id,user_name,password) values ('1e8e8d94-61b3-40b3-8e80-4890119d7c74','appache','123456');
    

    第一张表插入数据
    创建表address

    create table address(
      id char(36) primary key,
      user_info_id char(36),
      real_name varchar(8) not null,
      mobile char(11) not null,
      address varchar(150) not null
    )
    

    user_info_id对应 user_info表中的id字段
    插入数据

    insert into address (id,user_info_id,real_name,mobile,address) 
    values ('bfb9472a-7911-4e6f-a479-3b719454ebab','1e8e8d94-61b3-40b3-8e80-4890119d7c74','Lucy','18920120206','USA');
    insert into address (id,user_info_id,real_name,mobile,address) 
    values ('5227c6b9-45a2-44aa-8ac0-1f63a38d3b65','1e8e8d94-61b3-40b3-8e80-4890119d7c74','Tom','18617297545','UE');
    insert into address (id,user_info_id,real_name,mobile,address) 
    values ('30b8584b-aa6a-4516-a623-03f487058586','1e8e8d94-61b3-40b3-8e80-4890119d7c74','Tim','17694976949','Japan');
    

    向第二张表中插入数据
    这种方法虽然解决了字段的冗余问题,但由于只是逻辑上的“外键”关系,在插入数据的时候需要靠数据库操作者的自觉性,所以依然无法保证数据完整性,有可能会出现垃圾信息(没有用的信息)存在
    如果这里在向aadress表中添加一条数据并且该数据没有与之对应的用户信息则这里添加的数据就是没有用的信息

    insert into address (id,user_info_id,real_name,mobile,address) values ('7da42cc6-36a6-4ad5-9998-71dbc30c8e17','ddc376dd-f8b3-42a6-b42a-db22abed1941','xiaowang','18338970095','China');
    

    向address表中插入数据
    如果这里如果把user_info表中的数据删除但此时地址表中数据将不再完整,这些地址没有与之对应的用户。此时addres表中与之前用户对应的收货地址成为了垃圾信息。
    把user_info中的数据删除
    address表中的数据依然存在
    这里只是逻辑上的“外键”关系,所以还需要手动删除address表中对应的数据。

    显然逻辑上的"外键"关系需要依靠操作者的自觉性,依然无法保证数据的完整性。

    方案三:两张表并添加外键约束

    创建表user_info

    create table user_info(
      id char(36) primary key,
      user_name varchar(30) not null,
      password varchar(30) not null
    )
    

    插入一条数据

    insert into user_info (id,user_name,password) values ('1e8e8d94-61b3-40b3-8e80-4890119d7c74','appache','123456');
    

    创建表address的同时添加外键约束

    create table address(
      id char(36) primary key,
      user_info_id char(36),
      real_name varchar(8) not null,
      mobile char(11) not null,
      address varchar(150) not null,
      #下面这条语句就是在user_info_id添加了外键,指向user_info表的主键
      foreign key(user_info_id) references user_info(id)
    )
    

    插入数据

    insert into address (id,user_info_id,real_name,mobile,address) 
    values ('bfb9472a-7911-4e6f-a479-3b719454ebab','1e8e8d94-61b3-40b3-8e80-4890119d7c74','Lucy','18920120206','USA');
    insert into address (id,user_info_id,real_name,mobile,address) 
    values ('5227c6b9-45a2-44aa-8ac0-1f63a38d3b65','1e8e8d94-61b3-40b3-8e80-4890119d7c74','Tom','18617297545','UE');
    insert into address (id,user_info_id,real_name,mobile,address) 
    values ('30b8584b-aa6a-4516-a623-03f487058586','1e8e8d94-61b3-40b3-8e80-4890119d7c74','Tim','17694976949','Japan');
    

    这种方案为user_info_id添加了外键,指向user_info表的主键,该约束起到了保护数据完整性的作用:如果删除的用户信息id已经在address表中使用,则该条数据无法删除;无法向address表中添加用户id不存在的地址信息。
    如果向address表中插入无关的信息

    insert into address (id,user_info_id,real_name,mobile,address) values ('7da42cc6-36a6-4ad5-9998-71dbc30c8e17','ddc376dd-f8b3-42a6-b42a-db22abed1941','xiaowang','18338970095','China');
    

    这里会报错

    insert into address (id,user_info_id,real_name,mobile,address) values ('7da42cc6-36a6-4ad5-9998-71dbc30c8e17','ddc376dd-f8b3-42a6-b42a-db22abed1941','xiaowang','18338970095','China')
    > 1452 - Cannot add or update a child row: a foreign key constraint fails (`user_info`.`address`, CONSTRAINT `address_ibfk_1` FOREIGN KEY (`user_info_id`) REFERENCES `user_info` (`id`))
    > 时间: 0.009s
    

    如果这里删除表user_info,也会报错

    #以下两条语句中的任意一条即可
    delete from user_info
    delete from user_info where id = '1e8e8d94-61b3-40b3-8e80-4890119d7c74';
    

    这里也会报错

    delete from user_info
    > 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`user_info`.`address`, CONSTRAINT `address_ibfk_1` FOREIGN KEY (`user_info_id`) REFERENCES `user_info` (`id`))
    > 时间: 0.019s
    

    如果想要向address表中插入数据必须要和user_info_id指向的user_info表中的id相对应,否则无法插入
    如果想要删除user_info表中的数据,必须把指向他的主键对应的数据先全部删除,否则无法删除

    所以这种方案起到了保护数据完整性的作用,推荐使用该方案。


    删除user_info表中的id=1e8e8d94-61b3-40b3-8e80-4890119d7c74数据

    1. 先把address表中user_info_id指向该id的数据删除
    delete from address where user_info_id = '1e8e8d94-61b3-40b3-8e80-4890119d7c74';
    

    结果如下
    删除后的address表
    2. 这时就可以删除user_info表中的数据

    delete from user_infor where id = '1e8e8d94-61b3-40b3-8e80-4890119d7c74';
    

    user_infor表中的数据删除成功

    展开全文
  • SET NULL:父表delete、update的时候,子表会将关联记录的外键字段所在列设为null,所以注意在设计子表时外键不能设为not null;RESTRICT:如果想要删除父表的记录时,而在子表中有关联该父表的记录,则不允许删除父...

     

     

    CASCADE:父表delete、update的时候,子表会delete、update掉关联记录;
    SET NULL:父表delete、update的时候,子表会将关联记录的外键字段所在列设为null,所以注意在设计子表时外键不能设为not null;
    RESTRICT:如果想要删除父表的记录时,而在子表中有关联该父表的记录,则不允许删除父表中的记录;
    NO ACTION:同 RESTRICT,也是首先先检查外键;

     

    来源:https://www.cnblogs.com/lwyang/p/9794085.html

    转载于:https://www.cnblogs.com/CaptainFM/p/10944507.html

    展开全文
  • MySql数据库主键外键与数据库设计

    千次阅读 2019-05-20 01:38:57
    MySql数据库主键外键与数据库设计

    MySql数据库主键外键与数据库设计

    首先要指出的:
    列、字段、属性是一个概念
    行、记录、元组是一个概念

    MySQL数据库CONSTRAINT约束:非空约束,唯一约束,主键约束,外键约束
    show create table table_name\G;可以查看已建表的相关信息,包括主外键,外键名,存储引擎,编码方式等等

    主键约束

    主键:表中经常有一个列或者多列的组合,其值能唯一的标识表中的每一行,这样的一列或多列称为表的主键,通过它可以强制表的实体完整性,当创建或更改标识可以通过定义primary key约束来创建主键,一个表只能有一个primary key约束,而且primary key约束的列不能接受空值,由于primary key约束确保该列数据的唯一性,所以经常用来定义标识列。主键自带索引,并且一个表只能有一个主键约束
        实体完整性这项规则要求每个数据表都必须有主键,而作为主键的所有字段,其属性必须是独一及非空值。

    主键的作用
    用作标识列,区分其他字段
    强制表的实体完整性
    用于其他表的外键关联
    用于记录的修改与删除
    加速查询

    • 创建主键约束
    1. 建表时创建主键
      create table Student(id int primary key,name varchar(32) default null)
    2. 建表后添加主键
      create table Student(id int,name varchar(32));
      alter table Student add primary key(id);
    3. 建表后新加字段并设为主键
      create table Mother(id int,name varchar(32));
      alter tabler Mother add age int primary key;

    删除主键约束
    alter table Student drop primary key;

    联合主键:用两个或两个以上的字段组成的主键,用这个主键包含的字段作为主键约束,这个组合在数据表中是唯一,且加了主键索引。

    创建表时创建联合主键
    create table STUDENT(id int auto_increment,courser int,name varchar(10),primary key(id,courser));

    已有的表添加联合主键
    create table Person(id int,province varchar(8),city varchar(8),country varchar(8));
    alter table Person add constraint main_key primary key(id,province);
    Person是表名,main_key 是联合主键名。在建好的表中我们添加了如下数据:
    在这里插入图片描述

    由于id、province是联合主键,所以我们想要创立
    在这里插入图片描述
    时,会出现如下报错
    在这里插入图片描述
    但是我们可以让一个联合主键的一个字段名一样,另一个字段名不一样来创建,即联合主键的字段名不完全一致:
    在这里插入图片描述

    删除联合主键的主键约束,由于主键约束在表单中的唯一性,我们可以直接删除:
    alter table Person drop primary key;

    外键约束

    为了满足关系型数据库的第三范式,降低数据冗余,在表与表之间需要满足响应的关系,可以为表和表之间设置外键。
    外键约束:用于两个表的数据的数据连接,一个表可以有多个外键,MySQL中的innodb存储引擎支持外键;
    外键对应的是参照完整性,一个表的外键可以是空值,若不为空值,则每一个外键的值必须等于另一个表中的主键的某个值。定义一个外键后,不允许删除另一个表中具有关联关系的行;外键的主要作用是保持数据的一致性、完整性。一个主键表某一字段的和跟他有关联的外键表的某一字段有直接的关联。
    对于有外键关联的两个表来说:
    主表(父表):主键所在的表
    从表(字表):外键所在的表

    mysql设置外键约束字段选取
    父表必须是已经存在或者正在创建的表
    父表主键不能包含空值,但外键可以有
    外键列的数目必须和父表的主键中列的数目相同
    从表外键列和主表主键列对应的列的数据类型相同

    创建表时设置外键约束
    create table friend(id int ,name varchar(20),foreign key(id) references child(id));
    父表child,子表friend

    在已有的表中设置外键约束
    alter table subject add foreign key(id) references child(id);
    注意:子表的外键必须关联父表的主键,且关联字段的数据类型必须匹配,如果类型不一样,则创建子表时会出现错误。

    删除外键约束
    有外键约束的列必须先删除外键,然后才能删除该列:
    alter table subject drop foreign key subject_ibfk_1;
    subject_ibfk_1是外键的名字,我们可以自己设,也可以系统生成,这样的话我们要查看建表信息才能知道外键名字是什么。

    查看表中的外键
    show create table table_name
    select * from information_schema.key_column_usage;

    数据库设计

    数据库范式与设计:一对一,一对多,多对多
    关系型数据库的特点:基本组成但对为二维表,各二维表之间存在一定的关系,数据库范式级别越高,数据冗余越低
    第一范式:字段不可再分,只要是个二维表都符合第一范式
    第二范式:每个表都有一个能区分每条记录的主键(非空 唯一 索引)
    第三范式:表中所用数据元素不但能惟一的被主关键字所标识,而且它们之间还必须相互独立,不存在其他的函数关系,只有通过外键来互相联系

    表与表之间的三种关系:
    一对一:如学生分数表与学生信息表,两表有一样的主键ID
    一对多:如教学楼和教室,班级表和学生表的班级ID,多个从表通过外键连接主表主键完成一对多的关系
    多对多:如学生表和课程表,演员和电影;以演员和电影为例,分别建立演员表和课程表,演员表电影表不重复,然后建立一个关系表,将两个表连接起来,变成连个一对多的关系:
    在这里插入图片描述

    建立演员表
    create table actors(id int auto_increment primary key,name varchar(10));
    建立电影表
    create table films(id int auto_increment primary key,name varchar(20));
    建立中间表,并通过外键连接其他相关表
    create table link(id int auto_increment primary key,id_a int,id_f int);
    alter table link add foreign key(id_a) references films(id);
    alter table link add foreign key(id_f) references actors(id);

    这里我们想查询成龙演过的电影:
    select name,f_name from(select * from actors inner join(select id_a,id_f,name as f_name from link inner join films on films.id=link.id_f) as a on a.id_a=id having id=1) as b;

    展开全文
  • mysql数据库创建外键

    千次阅读 2019-01-09 17:02:04
    创建外键注意的地方 必须是innodb引擎 注意外键字段类型和另一张表主键字段类型一致否则报“cannot add foreign key constraint“ 创建 create table class(cid int not null auto_increment, caption ...

    创建外键注意的地方

    1. 必须是innodb引擎

    2. 注意外键字段类型和另一张表主键字段类型一致否则报“cannot add foreign key constraint“

    创建

    create table class(cid int not null auto_increment, caption char(50) not null, primary key(cid)) engine=innodb charset=utf8;

    create table student(sid int not null auto_increment, sname char(20) not null, class_id int not null, 
        primary key(sid), foreign key(class_id) references class(cid) )engine=innodb charset=utf8;

     

    注:references class 是另一张表,(cid)是class表的主键

    若表已创建好,则通过如下规则:

    alter table 要添加外键的表 add foreign key(要添加外键的表将要被设为外键的字段) references 与外键相关的表 (与外键相关的表的主键)

    alter table score add foreign key (corse_id) references course (cid);

     

     

     

    展开全文
  • mysql数据库表格添加外键

    千次阅读 2019-05-22 12:58:17
    将表格studentInfo中的rid字段设置外键,与表格room关联(room中的rid为主键) ALTER TABLE studentInfo ADD CONSTRAINT FOREIGN KEY(rid) REFERENCES room(rid) 另:补充一个更改表名的代码操作 ALTER TABLE ...
  • 本文介绍了在MySQL数据库中定义外键的方法。
  • 还有十五分钟图书馆关闭,赶紧来把今天的关于MySQL外键修改的内容记录一下。 背景: 在写作业的时候必要的建立了两个数据表。 users表和records表修改前如下所示: 可以看出开始的情况是: records的user_id是外键...
  • #求最大年龄 mysql> SELECT MAX(`age`) FROM `student_details`;   #求最小年龄:   #其他一些操作:                                       ...
  • Mysql数据库如何删除某一个数据库的所有外键并不删除相应的索引,一个sql脚本解决,无需知道外键的名称。
  • 省表两个属性 id和名称 市与县都是三个属性 外键已建立好 非常全
  • 如何查看MySQL数据库外键关系

    千次阅读 2019-08-23 16:45:51
    查看所有的主外键关系,例如有一张表为:role,输入以下命令 SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME='role'
  • mysql数据库中主外键设置语句

    千次阅读 2019-07-04 10:09:52
    create table tableName( k1 int auto_increment, k2 varchar(50) not null, k3… primary key(k1), foreign key(kn) reference otherTable(ok) ...//此表中外键kn参照主键表otherTable的主键ok ...
  • 本文介绍在在MySQL数据库中定义数据表、设置索引、定义外键、级联等操作。
  • sql 1452 Cannot add or update a child row:a foreign key constraint fails 今天对已存在的两个表中的一个表添加另一个表的主键为外键,遇到以下错误: sql 1452 Cannot add or ...设置外键和对应的另一个表的...
  • 将表中已有字段设置 外键 似乎不能设置为主键即使定义时没有定义主键也会报错 如 Multiple primary key defined 1。添加新字段 alter table 表名 add 字段名 字段描述; alter table student add phone varchar(20);...
  • ** MySQL数据库中删除外键 ** 首先查看数据库以及选中数据库的一个文件 在文件中创建一个数据表命名为orders 表格删除外键 查看orders_customers表结构
  • mysql删除数据库所有外键

    千次阅读 2018-10-22 17:06:52
    删除所有表的外键  查询schema中所有外键名称然后拼接生成删除语句,再执行。 SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME,' ;')  FROM information_schema...
  • MySQL中如何设置外键

    万次阅读 多人点赞 2019-07-02 22:53:13
    Windows操作系统,MySQL 5.7 二、使用工具 Navicat 三、前提知识 1.什么是主键 Primary key,唯一标识一个实体,取值非空唯一。比如,一个人的身份证号。 2.什么是外键 Foreign key, 本关系表中的属性的属性值需要...
  • 数据库SQL外键设置

    2021-01-31 18:03:40
    创建约束 alter table user_relation add CONSTRAINT `fk_deleteUser` FOREIGN KEY (`account`) REFERENCES `sys_user` (`... 表名 外键名 表字段 外键关联表 外键关联字段 删除时 级联模式 更新时 不做任何操作
  • 内包含外键约束模式,数据库的视图基本操作
  • mysql数据库创建外键老是失败? 可能原因: 1、这是建外键的列与要关联的列类型不匹配造成的; 2、一个表用powerdesigner导入的整型长度为11,另一个直接在mysql中建立的表默认整型长度是10; 3、外键...
  • mysql数据类型 1.1数值型:int tinyint微整型 smallint 小整型 1.2小数类型:decimal(总位数,小数位) 例:价格 decimal(5,2) 100.00-999.99 日期和时间类型 datetime 年月日时分秒 字符串类型 char(字符串的长度)...
  • 查询数据库外键和外键关联的表名
  • MySQL数据库中添加外键的两种方式

    千次阅读 2019-04-11 17:02:42
    关于MySQL数据表中添加外键的两种方式: 第一种方式          Alter table 表名 add [constraint 外键名字] foreign key [外键字段] references 父表(主键字段);...
  • [导读 ] 使用 MySQL 开发过数据库驱动的小型 web 应用程序的人都知道对关系数据库 的表进行创建 检索更新和删除等操作都是些比较简单的过程 理论上 只要掌握了最常 见的 SQL 语句的用法并熟悉您选择使用的服务器端...
  • MySQL数据库的主键和外键详解

    千次阅读 2020-03-20 20:15:42
    MySQL数据库的主键和外键详解 主键 主键的定义 主键:表中经常有一个列或多列的组合,其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键,通过它可强制表的实体完整性。当创建或更改表时可通过定义 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 102,397
精华内容 40,958
关键字:

mysql数据库设置外键

mysql 订阅