时间戳

首页 > 解决方案 > 从其他表中的数据和用户定义的约束条件创建表

sql-server - 从其他表中的数据和用户定义的约束条件创建表

问题描述

以下两个表用于定义用户及其各自的角色：

CREATE TABLE users 
(
     id INTEGER NOT NULL PRIMARY KEY, 
     userName VARCHAR(50) NOT NULL
)

CREATE TABLE roles 
(
    id INTEGER NOT NULL PRIMARY KEY, 
    role VARCHAR(20) NOT NULL
)

users_roles 表应包含每个用户及其角色之间的映射。每个用户可以有多个角色，每个角色可以有多个用户。修改提供的 SQLite 创建表语句，以便：

  1. 只有 users 表中的用户才能存在于 users_roles 中。
  2. 只有角色表中的角色才能存在于 users_roles 中。

  3. 一个用户只能拥有一个特定角色一次。

    创建表 users_roles ( userId INTEGER, roleId INTEGER )

我努力了

CREATE TABLE users_roles 
(
    userId integer NOT NULL,
    roleId integer NOT NULL,
    PRIMARY KEY (userId, roleId),
    FOREIGN KEY (userId) REFERENCES users(id),
    FOREIGN KEY (roleId) REFERENCES roles(id)
)

它解决了许多这些问题，但有一个大问题。

如果角色表中有重复项，例如

id | role
---+-------
 1 | hr
 2 | hr
 3 | cse

在 users_roles 表中仍然有

users.id | roles.id
---------+----------
    1    |    1
    1    |    2

用户 id 1 与角色 id 1 和 2 匹配。但是 1 和 2 具有角色 hr，因此它违反了最后一个要点。

user_roles在创建表中我还需要做什么才能解决这个问题？

标签： sql-server

解决方案

通过添加约束来防止角色表上的重复：

ALTER TABLE [roles] 
   ADD CONSTRAINT [UC_roles_role] UNIQUE NONCLUSTERED ([role] ASC)
                   WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

推荐阅读