SQL 约束在两列中具有一个唯一值

发布于 2024-11-10 16:41:46 字数 339 浏览 4 评论 0原文

我需要确保一个值在两列中是唯一的（这不是“两列”索引问题）。

Table A
Column A1       Column A2

Memphis         New York     -> ok
San Francisco   Miami        -> ok
Washington      Chicago      -> ok
Miami           Las Vegas    -> Forbidden ! Miami already exists

是否可以？

我的例子是城市，但不要集中于此。我真正需要的是生成的十六进制 ID。

原文

I need to assure that a value is unique in two columns (this is not a "two columns" index issue).

Table A
Column A1       Column A2

Memphis         New York     -> ok
San Francisco   Miami        -> ok
Washington      Chicago      -> ok
Miami           Las Vegas    -> Forbidden ! Miami already exists

Is it possible ?

My example is with cities but don't focalize on that. My real need is about generated hexadecimal ids.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

千纸鹤 2024-11-17 16:41:46

在 SQL Server 中，可以借助索引视图来强制执行唯一性。您还需要一个与表 A 位于同一数据库中的数字表（如果您还没有）。

这是我的测试脚本，其中包含一些评论：

CREATE TABLE MyNumbersTable (Value int);
-- You need at least 2 rows, by the number of columns
-- you are going to implement uniqueness on
INSERT INTO MyNumbersTable
SELECT 1 UNION ALL
SELECT 2;
GO
CREATE TABLE MyUniqueCities (  -- the main table
  ID int IDENTITY,
  City1 varchar(50) NOT NULL,
  City2 varchar(50) NOT NULL
);
GO
CREATE VIEW MyIndexedView
WITH SCHEMABINDING  -- this is required for creating an indexed view
AS
SELECT
  City = CASE t.Value    -- after supplying the numbers table
    WHEN 1 THEN u.City1  -- with the necessary number of rows
    WHEN 2 THEN u.City2  -- you can extend this CASE expression
  END                    -- to add more columns to the constraint
FROM dbo.MyUniqueCities u
  INNER JOIN dbo.MyNumbersTable t
    ON t.Value BETWEEN 1 AND 2  -- change here too for more columns
GO
-- the first index on an indexed view *must* be unique,
-- which suits us perfectly
CREATE UNIQUE CLUSTERED INDEX UIX_MyIndexedView ON MyIndexedView (City)
GO
-- the first two rows insert fine
INSERT INTO MyUniqueCities VALUES ('London', 'New York');
INSERT INTO MyUniqueCities VALUES ('Amsterdam', 'Prague');
-- the following insert produces an error, because of 'London'
INSERT INTO MyUniqueCities VALUES ('Melbourne', 'London');
GO
DROP VIEW MyIndexedView
DROP TABLE MyUniqueCities
DROP TABLE MyNumbersTable
GO

有用的阅读：

In SQL Server it is possible to enforce the uniqueness with the help of an indexed view. You will also need a numbers table (if you haven't already got one) in the same database as your Table A.

Here's my testing script with some comments:

CREATE TABLE MyNumbersTable (Value int);
-- You need at least 2 rows, by the number of columns
-- you are going to implement uniqueness on
INSERT INTO MyNumbersTable
SELECT 1 UNION ALL
SELECT 2;
GO
CREATE TABLE MyUniqueCities (  -- the main table
  ID int IDENTITY,
  City1 varchar(50) NOT NULL,
  City2 varchar(50) NOT NULL
);
GO
CREATE VIEW MyIndexedView
WITH SCHEMABINDING  -- this is required for creating an indexed view
AS
SELECT
  City = CASE t.Value    -- after supplying the numbers table
    WHEN 1 THEN u.City1  -- with the necessary number of rows
    WHEN 2 THEN u.City2  -- you can extend this CASE expression
  END                    -- to add more columns to the constraint
FROM dbo.MyUniqueCities u
  INNER JOIN dbo.MyNumbersTable t
    ON t.Value BETWEEN 1 AND 2  -- change here too for more columns
GO
-- the first index on an indexed view *must* be unique,
-- which suits us perfectly
CREATE UNIQUE CLUSTERED INDEX UIX_MyIndexedView ON MyIndexedView (City)
GO
-- the first two rows insert fine
INSERT INTO MyUniqueCities VALUES ('London', 'New York');
INSERT INTO MyUniqueCities VALUES ('Amsterdam', 'Prague');
-- the following insert produces an error, because of 'London'
INSERT INTO MyUniqueCities VALUES ('Melbourne', 'London');
GO
DROP VIEW MyIndexedView
DROP TABLE MyUniqueCities
DROP TABLE MyNumbersTable
GO

Useful reading:

回复收藏 0 原文