MySQL | 复合主键、外键创建

惜木兮

于 2021-05-21 15:45:06 发布

阅读量2.5k

点赞数 2

分类专栏： MySQL SQL

本文链接：https://blog.csdn.net/weixin_46023346/article/details/117124322

版权

MySQL 复合主键外键约束数据库设计表关联

关键词由CSDN通过智能技术生成

SQL 同时被 2 个专栏收录

39 篇文章 2 订阅

订阅专栏

MySQL

12 篇文章 2 订阅

订阅专栏

复合主键为一个表中多个主键来确定数据的唯一性。

CREATE TABLE IF NOT EXISTS active_user(
logindate DATE,
uid VARCHAR(225),
new_user INT,
PRIMARY KEY(logindate,uid)
);
## 外键分开写会报错
mysql> CREATE TABLE IF NOT EXISTS  receive_red_packets(
    -> receive_date DATE,
    -> receive_datetime DATETIME,
    -> uid VARCHAR(225),
    -> money FLOAT,
    -> FOREIGN KEY(receive_date) REFERENCES active_user(logindate),
    -> FOREIGN KEY(uid) REFERENCES active_user(uid)
    -> );
ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'receive_red_packets_ibfk_2' in the referenced table 'active_user'

mysql> CREATE TABLE IF NOT EXISTS  receive_red_packets(
    -> receive_date DATE,
    -> receive_datetime DATETIME,
    -> uid VARCHAR(225),
    -> money FLOAT,
    -> FOREIGN KEY(receive_date,uid) REFERENCES active_user(logindate,uid)
    -> );
Query OK, 0 rows affected (0.05 sec)