SQL Server 2008 - 如何添加约束以防止相同的值出现在 A 列和 B 列中？

发布于 2024-11-07 03:05:44 字数 725 浏览 0 评论 0原文

我在一个表 (Table1) 中有两个 varchar 列 (ColA, ColB)

有没有办法制作一个约束来防止ColA 和 ColB 中出现的值相同吗？

示例

原始表：

-----------------------
| Col A    |   Col B  |
-----------------------
| Alice    |   Bob    |
-----------------------

插入我希望失败的命令

Insert into Table1 values ('Bob', 'Jill')

不需要的结果

-----------------------
| ColA     |   ColB   |
-----------------------
| Alice    |   Bob    |   <-- Bob In Col B
-----------------------
| Bob      |   Jill   |   <-- Bob in Col A
-----------------------

原文

I have two varchar columns (ColA, ColB) in a single table (Table1)

Is there a way to craft a constraint that would prevent the same value from appearing in both ColA and ColB?

Example

Original Table:

-----------------------
| Col A    |   Col B  |
-----------------------
| Alice    |   Bob    |
-----------------------

Insert Command that I would like to fail

Insert into Table1 values ('Bob', 'Jill')

Undesired Result

-----------------------
| ColA     |   ColB   |
-----------------------
| Alice    |   Bob    |   <-- Bob In Col B
-----------------------
| Bob      |   Jill   |   <-- Bob in Col A
-----------------------

分享到QQ

分享到微博

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

发布评论

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

挽清梦 2024-11-14 03:05:44

您需要将此约束逻辑实现为 触发器< /a>.

回复收藏 0 原文

稀香 2024-11-14 03:05:44

@米奇小麦是正确的。

这是一些工作代码：

ALTER TRIGGER TABLE1_Check_Two_Col_Uniqueness ON TABLE1
INSTEAD OF INSERT
AS

BEGIN
    SET NOCOUNT ON

    IF  EXISTS      (   SELECT 1 FROM Table1 as T1 inner join inserted as I on I.ColA = T1.ColB )
        OR EXISTS   (   SELECT 1 FROM Table1 as T1 inner join inserted as I on I.ColB = T1.ColA )
        BEGIN
            RAISERROR('Error Inserting Table1_Record, it would cause a duplicate ColA and ColB', 16, 1)
            RETURN
        END
    ELSE
        BEGIN
            INSERT INTO Table1
            SELECT
                ColA, 
                ColB
            FROM
                inserted
        END
END

@Mitch Wheat is correct.

Here's some working code:

ALTER TRIGGER TABLE1_Check_Two_Col_Uniqueness ON TABLE1
INSTEAD OF INSERT
AS

BEGIN
    SET NOCOUNT ON

    IF  EXISTS      (   SELECT 1 FROM Table1 as T1 inner join inserted as I on I.ColA = T1.ColB )
        OR EXISTS   (   SELECT 1 FROM Table1 as T1 inner join inserted as I on I.ColB = T1.ColA )
        BEGIN
            RAISERROR('Error Inserting Table1_Record, it would cause a duplicate ColA and ColB', 16, 1)
            RETURN
        END
    ELSE
        BEGIN
            INSERT INTO Table1
            SELECT
                ColA, 
                ColB
            FROM
                inserted
        END
END

回复收藏 0 原文

~没有更多了~