时间戳

首页 > 解决方案 > SQL中的多值表没有足够的列

sql - SQL中的多值表没有足够的列

问题描述

例如，当我有一个人可以说的语言的多值表时。比方说，有 5 列用于存储语言。

CREATE TABLE Languages  
(  
    PersonID int primary key,  //this is FOREIGN KEY to a Person, "REFERENCES Person(ID)" for example
    Lang1 varchar(30),  
    Lang2 varchar(30),  
    Lang3 varchar(30),  
    Lang4 varchar(30),  
    Lang5 varchar(30)  
)

如果有人会说超过 5 种语言，有什么解决方案？

标签： sqlsql-server

解决方案

您的设计已损坏，无法处理您描述的场景。您需要修复设计，这种情况的正常设计是：

CREATE TABLE Person
(
    PersonID int identity (1,1) primary key,
    Forename varchar(128),
    Surname varchar(128)
    -- etc etc
)

CREATE TABLE Language
(
    LanguageID int primary key,
    [Name] varchar(128)
    -- etc etc
)

CREATE TABLE PersonLanguage
(
    -- Optional to have a specific PK for a linking table - I prefer to
    PersonLanguageID int identity (1,1) primary key,
    PersonID int foreign key reference Person (PersonId),
    LanguageID int foreign key reference Language (LanguageId)
)

然后您可以添加任意数量的语言，例如

-- First insert will give ID 1
insert into Person (Forename, Surname)
values ('Bob', 'Test');

-- Insert will give IDs 1-7
insert into Language ([Name])
values ('English'), ('Spanish'), ('French'), ('German'), ('Dutch'), ('Portuguese'), ('Greek');

-- Add as many languages as you like
insert into PersonLanguage (PersonId, LanguageId)
values (1,1), (1,2), (1,3), (1,4), (1,5), (1,6), (1,7);

推荐阅读