在sql server中,有没有一种方法可以确定两个表之间的关系表1有3个唯一键,表2有2个唯一键?

dly7yett  于 2021-08-13  发布在  Java
关注(0)|答案(3)|浏览(405)

我想创建两个表之间的关系,这是我的表结构
表1:

ID INT PrimaryKey AUTO_INCREMENT,
CountryCode INT UNIQUE,
Division VARCHAR(4) UNIQUE,
SubDivision VARCHAR(10) UNIQUE

表2:

ID INT PrimaryKey AUTO_INCREMENT,
CountryCode INT UNIQUE,
Division VARCHAR(4) UNIQUE,
ReportNo VARCHAR(10) UNIQUE

表1:

ID     |CountryCode       |Division      |SubDivision
-------+------------------+--------------+-----------
1      |IDN               |A             |A-1
2      |IDN               |B             |B-1
3      |IDN               |B             |B-2

表2

ID     |CountryCode       |Division      |ReportNo
-------+------------------+--------------+-----------
1      |IDN               |A             |Re001
2      |IDN               |A             |Re002
3      |IDN               |B             |Re003

我想在这两个表之间创建一个关系 table2 (CountryCode, Division)table1 (CountryCode, Division) .
因此,当我想在countrycode=idn和division=a的表1中删除时,当表2包含countrycode=idn和division=a时,sql将提示error。
我曾尝试在这两个表之间创建关系,但sql server总是抛出以下错误:
表“table1”中的列与现有主键或唯一约束不匹配
有谁能指导我如何在这两个表之间建立关系吗?
提前谢谢

tpxzln5u

tpxzln5u1#

你不能这样做。sql server要求外键的目标上有唯一约束(或主键约束),并且源表中有重复的约束。
要使其工作,您需要有一个单独的表来引用所有可能的内容 (CountryCode, Division) 组合。实际上,您的整个模式应该规范化为:

-- "top" table that stores the countries
create table countries (
    country_id int primary key
    name varchar(100)
);

-- the table that stores the divisions
create table divisions (
    division_id int primary key,
    country_id int references countries(country_id),
    name varchar(100)
);

-- the table that stores the subdivisions
-- this corresponds to "table1" in your question
create table subdivisions (
    subdivision_id int primary key,
    division_id int references divisions(division_id),
    name varchar(100)
);

-- the table that stores the reports
-- this corresponds to "table2" in your question
create table reports (
    report_id int primary key,
    division_id references divisions(division_id),
    name varchar(100)
);

您可以使用 identity 列(相当于sql server的mysql AUTO_INCREMENT ).
作为一个示例,下面是如何生成显示的细分的当前输出:

select 
    sd.id,
    c.name country,
    d.name division,
    sd.name subdivision
from subdivisions sd
inner join divisions d on d.division_id = sd.division_id
inner join countries c on c.country_id = d.country_id
hgncfbus

hgncfbus2#

正如gmb所回答的,你不能这样做,因为复制品。gmb的答案是解决你问题的最好方法。如果出于某种原因你不能听从他的建议,那么也许我的回答会有所帮助。
可以在countrycode、division和subdivision列上使用复合主键。然后将细分添加到表2中。然后在外键约束中引用此主键(请注意,我的示例故意抛出一个错误,以表明无法删除该值)

DROP TABLE IF EXISTS Table2;
DROP TABLE IF EXISTS Table1;
CREATE TABLE Table1
    (ID INT IDENTITY(1,1)
    , CountryCode CHAR(3) 
    , Division VARCHAR(4) 
    , SubDivision VARCHAR(10)
    , CONSTRAINT PK_Table1 PRIMARY KEY(CountryCode, Division, SubDivision)
    )
INSERT INTO Table1(CountryCode, Division, SubDivision)
VALUES    ('IDN', 'A', 'A-1')
        , ('IDN', 'B', 'B-1')
        , ('IDN', 'B', 'B-2');

CREATE TABLE Table2
    (ID INT IDENTITY(1,1) PRIMARY KEY 
    , CountryCode CHAR(3) 
    , Division VARCHAR(4) 
    , SubDivision VARCHAR(10)
    , ReportNo VARCHAR(10) 
    , CONSTRAINT FK_CountryDivision FOREIGN KEY(CountryCode, Division, SubDivision) REFERENCES Table1(CountryCode, Division, SubDivision)
    );
INSERT INTO Table2(CountryCode, Division, SubDivision, ReportNo)
VALUES    ('IDN', 'A', 'A-1', 'Re001')
        , ('IDN', 'B', 'B-1', 'Re002')
        , ('IDN', 'B', 'B-2', 'Re003');

DELETE FROM Table1
WHERE Division = 'A';

当然,这种变化可能会增加一整套新的问题,例如,当一份报告针对整个部门时,细分值应该是什么。
另外,我不得不对示例表进行一点修改,因为值不匹配,即将字符串值转换为int列。
sql小提琴

wb1gzix0

wb1gzix03#

谢谢你的回答。
但在我的设计中我不能这样做,因为我第一次就错了。
伟大的答案来自@10676716@gmb先生。

-- "top" table that stores the countries
create table countries (
    country_id int primary key
    name varchar(100)
);

-- the table that stores the divisions
create table divisions (
    division_id int primary key,
    country_id int references countries(country_id),
    name varchar(100)
);

-- the table that stores the subdivisions
-- this corresponds to "table1" in your question
create table subdivisions (
    subdivision_id int primary key,
    division_id int references divisions(division_id),
    name varchar(100)
);

-- the table that stores the reports
-- this corresponds to "table2" in your question
create table reports (
    report_id int primary key,
    division_id references divisions(division_id),
    name varchar(100)
);

再次感谢gmb先生。

相关问题