外键的基本概念

外键的基本概念

• 外键（FOREIGN KEY）：外键是一个或多个列，其值必须在另一个表中对应列的值中存在。外键用于维护两个表之间的关系，并确保引用的完整性。

外键在示例表sys_user_role结构中的应用

假设外键约束如下：

FOREIGN KEY (user_id) REFERENCES sys_user(id),
FOREIGN KEY (role_id) REFERENCES sys_role(id)

    这意味着 sys_user_role 表中的 user_id 列必须引用 sys_user 表中的 id 列，而 role_id 列必须引用 sys_role 表中的 id 列。

示例表结构的详细解释

用户表（sys_user）

CREATE TABLE sys_user (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20) UNIQUE,
    dept_id BIGINT,
    is_admin BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (dept_id) REFERENCES sys_dept(id)
);

• id：用户表的主键，唯一标识每个用户。
• dept_id：外键，引用 sys_dept 表的 id 列，表示用户所属的部门。

部门表（sys_dept）

CREATE TABLE sys_dept (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

• id：部门表的主键，唯一标识每个部门。
• name：部门名称。

角色表（sys_role）

CREATE TABLE sys_role (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

• id：角色表的主键，唯一标识每个角色。
• name：角色名称。

用户角色关联表（sys_user_role）

CREATE TABLE sys_user_role (
    user_id BIGINT,
    role_id BIGINT,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES sys_user(id),
    FOREIGN KEY (role_id) REFERENCES sys_role(id)
);

• user_id：外键，引用 sys_user 表的 id 列。
• role_id：外键，引用 sys_role 表的 id 列。
• PRIMARY KEY (user_id, role_id)：联合主键，确保每个用户和角色的组合在表中是唯一的。

关系的具体含义

1. sys_user 表和 sys_dept 表之间的关系：

   • 每个用户可以属于一个部门。
   • dept_id 列作为外键，引用 sys_dept 表的 id 列，确保每个用户记录中的 dept_id 都能在 sys_dept 表中找到对应的部门。

2. sys_user_role 表和 sys_user 表、sys_role 表之间的关系：

   • user_id 列作为外键，引用 sys_user 表的 id 列，确保每个 sys_user_role 记录中的 user_id 都能在 sys_user 表中找到对应的用户。
   • role_id 列作为外键，引用 sys_role 表的 id 列，确保每个 sys_user_role 记录中的 role_id 都能在 sys_role 表中找到对应的角色。

进一步示例

假设我们有以下用户、角色和部门：

• 用户：Alice, Bob
• 角色：Admin, User
• 部门：HR, IT

相关的表数据可能如下：

sys_user 表

id	username	password	email	phone	dept_id	is_admin
1	Alice	*****	alice@example.com	123456789	1	true
2	Bob	*****	bob@example.com	987654321	2	false

sys_dept 表

id	name
1	HR
2	IT

sys_role 表

id	name
1	Admin
2	User

sys_user_role 表

user_id	role_id
1	1
2	2

总结

     外键约束在数据库设计中起到了关键作用，通过定义外键，可以确保数据的完整性和一致性。外键在应用程序中帮助维护表与表之间的关系，例如用户与角色、用户与部门等。在设计数据库时，理解和正确使用外键至关重要，可以显著提高数据库的健壮性和数据的可靠性。