SQL 表 - 半唯一行? [英] SQL table - semi-unique row?

问题描述

我有一个基本结构如下的 SQL 表:

I have an SQL table with basically the following structure:

PK (int, primary key), userID (int), data (varchar 64)

基本上，userID 定义的任何用户都可以存储任意数量的短字符串.但是，不允许用户存储两个相同的字符串(尽管用户 1 和用户 2 可以分别存储相同的字符串).如果可能的话，我希望在数据库级别实施此限制，因为恕我直言，结构约束应始终在表中，以及在从表中插入/读取数据的程序中.

Basically, any user as defined by userID is allowed to store any number of short strings. However, no user is allowed to store two identical strings (although user 1 and user 2 can both store the same string separately). I would, if at all possible, like to implement this restriction at the database level because IMHO structural constraints should always be in the tables, as well as in the programs inserting/reading data from the tables.

我唯一能想到的是添加第三列，在其中我在每次插入时连接用户 ID 和数据，并称 那个 列是唯一的，但这对我来说似乎太hacky"了.我愿意完全重组我的表，如果你们中的一个人有更好的方法来做到这一点，这将使我能够在字段上施加这种约束:)

The only thing I can think of is adding a third column in which I concatenate userID and data upon every insert, and call that column unique, but that seems too "hacky" to me. I am open to completely restructuring my tables if one of you guys has a better way of doing this that will allow me to put this constraint on the fields :)

谢谢！
马拉

推荐答案

听起来您需要对 userID 和 data 列进行复合唯一约束.

Sounds like you need a composite unique constraint on the userID and data columns.

像这样:

CONSTRAINT my_contraint_name UNIQUE ([userID], [data])

这篇关于SQL 表 - 半唯一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！