用整数数组设置外键 [英] Setting up foreign key with an array of integers

问题描述

我对使用SQL还是很陌生，但是我在Stack Overflow上遇到了有关使用标签的问题.

I'm pretty new to working with SQL but I've come across this question on Stack Overflow for working with tags.

建议的用于标记或标记的SQL数据库设计

这导致我在数据库中创建以下表:

This led me to create the following tables in my database:

文档

---------------------------------------------------------------
| Doc Id (PK)(int) | Doc Title (varchar) | Doc Link (varchar) |
---------------------------------------------------------------
| 1                | Printing            | http://example.com |
---------------------------------------------------------------
| 2                | Format              | http://example.com |
---------------------------------------------------------------

标签

--------------------------------------
| Tag Id (PK)(int) | Title (varchar) |
--------------------------------------
| 1                | print           |
--------------------------------------
| 2                | guide           |
--------------------------------------
| 3                | support         |
--------------------------------------

DocTag

---------------------------------
| DocId (int) | TagId (varchar) |
---------------------------------
| 1           | 1, 3            |
---------------------------------
| 2           | 2, 3            |
---------------------------------

但是，我无法在 DocTag 表和其他两个表之间创建外键，因为我需要在 DocTag ，因为一个文档可以包含多个标签.尝试创建主键时出现错误，我认为这是由于 TagId 中的varchar变量引起的.

However, I cannot create foreign keys between the DocTag table and the other two because I need to create an array of sorts in the TagId of DocTag because one doc can have many tags. I am getting an error when trying to create the primary key and I assume it's because of the varchar variable in TagId.

有什么建议可以克服这个问题?

What are some suggestions for overcoming this?

推荐答案

DocTag

---------------------------------
| DocId (int) | TagId (int)     |
---------------------------------
| 1           | 1               |
---------------------------------
| 1           | 3               |
---------------------------------
| 2           | 2               |
---------------------------------
| 2           | 3               | 
---------------------------------

外键必须与它们引用的列的数据类型匹配.

Foreign keys must match the data type of the column they reference.

每个外键引用必须是单个值.没有逗号分隔的列表.

Each foreign key reference must be a single value. No comma-separated lists.

即使您必须重复DocId，也可以将每个TagId值单独放在一行中.

Put each TagId value on a row of its own, even though you have to repeat the DocId.

请参见我的回答是:在数据库列中存储分隔列表真的那么糟糕吗?"

这篇关于用整数数组设置外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！