Mysql workbench 数据库设计

  • PK:primary key 主键
  • NN:not null 非空
  • UQ:unique 唯一索引
  • BIN:binary 二进制数据(比text更大)
  • UN:unsigned 无符号(非负数)
  • ZF:zero fill 填充0 例如字段内容是1 int(4), 则内容显示为0001
  • AI:auto increment 自增

外键约束

MySQL中定义外键的表,双方必须都是Innodb

子表中外键字段和其对应父表中的字段必须都设为索引主键自动为索引

E-R图中几种关系

  • Identifying Relationship
    需要提供外键给另外一张表作联合主键
  • Non-identifying Mandatory Relationship
    强制外键
  • Non-identifying Optional Relationship
    可选外键
  • One-to-one Relationship Non-specific
    无特殊关系

判别一个关系是Non-Identifying还是Identifying只要区分子实体的主键,看是否需要父实体的外键共同作为主键(类似联合主键?),需要则为Identifying,如果子实体自己的主键就可唯一标识则它为Non-Identifying

  • 虚线1:1关联是添加外键的
  • 实线1:1是添加外键为主键的
-- 多个外键 个人理解 要把该字段设置为索引,再foreign key
CREATE TABLE product (
    category INT NOT NULL,
    id INT NOT NULL,
    price DECIMAL,
    PRIMARY KEY (category , id)
);
 
CREATE TABLE customer (
    id INT NOT NULL,
    PRIMARY KEY (id)
);
 
CREATE TABLE product_order (
    no INT NOT NULL AUTO_INCREMENT,
    product_category INT NOT NULL,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,
    PRIMARY KEY (no),
    INDEX (product_category , product_id),
    FOREIGN KEY (product_category , product_id)
        REFERENCES product (category , id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    INDEX (customer_id),
    FOREIGN KEY (customer_id)
        REFERENCES customer (id)
);
-- 先给父表,子表中涉及到外键的字段设置索引
ALTER TABLE parts ADD INDEX idx_model (model);
ALTER TABLE pc ADD INDEX idx_cpumodel (cpumodel);
 
-- 在给pc表设置外键
ALTER TABLE pc ADD CONSTRAINT fk_cpu_model FOREIGN KEY (cpumodel) REFERENCES parts(model) ON UPDATE CASCADE;
 
-- 删除外键
alter table child drop foreign key 外键名

 

There are no comment yet.

HTML tag cannot be used in this comment.