SQL Server中的唯一行约束

I have the following table

我有下表

CREATE TABLE [dbo].[LogFiles_Warehouse](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [timestamp] [datetime] NOT NULL,
    [clientNr] [int] NOT NULL,
    [server] [nvarchar](150) COLLATE Latin1_General_CI_AS NOT NULL,
    [storeNr] [int] NOT NULL,
    [account] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
    [software] [nvarchar](300) COLLATE Latin1_General_CI_AS NOT NULL,
 CONSTRAINT [PK_Astoria_LogFiles_Warehouse] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

And want to avoid having duplicate rows in my table. I thought about creating a UNIQUE index on the complete table, but then SQL Manager Studio tells me that this is not possible because the key would be too large.

并希望避免在我的表中有重复的行。我考虑在整个表上创建一个UNIQUE索引,但SQL Manager Studio告诉我这是不可能的,因为密钥太大了。

Is there another way I could enforce unique rows over all columns, apart from indexes?

除了索引之外,还有另一种方法可以在所有列上强制执行唯一的行吗?

2 个解决方案

#1

Create a UNIQUE index on hashed values:

在散列值上创建UNIQUE索引:

CREATE TABLE [dbo].[LogFiles_Warehouse]
        (
        [id] [int] IDENTITY(1,1) NOT NULL,
        [timestamp] [datetime] NOT NULL,
        [clientNr] [int] NOT NULL,
        [server] [nvarchar](150) COLLATE Latin1_General_CI_AS NOT NULL,
        [storeNr] [int] NOT NULL,
        [account] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
        [software] [nvarchar](300) COLLATE Latin1_General_CI_AS NOT NULL,
        serverHash AS CAST(HASHBYTES('MD4', server) AS BINARY(16)),
        accountHash AS CAST(HASHBYTES('MD4', account) AS BINARY(16)),
        softwareHash AS CAST(HASHBYTES('MD4', software) AS BINARY(16))
        )

CREATE UNIQUE INDEX
        UX_LogFilesWarehouse_Server_Account_Software
ON      LogFiles_Warehouse (serverHash, accountHash, softwareHash)

#2

Use triggers + a smaller non unique index over the most distinguishing ields to helop aleviate the table s can problem.

使用触发器+一个较小的非唯一索引覆盖最明显的域,以帮助缓解表的问题。

This goes down a lot into a bad database design to start with. Fields like Software, Account do not belong into that table to start with (or if account, then not client nr). Your table is only so wisde because you arelady violate database design basics to start with.

这开始下降到很糟糕的数据库设计。软件,帐户等字段不属于该表的开头(或者如果是帐户,则不属于客户端nr)。你的桌子只是如此自然,因为你从一开始就违反了数据库设计的基础。

Also, to abvoid non unique fields, you have NT to have the Id field in the unique testing otherwise you ont ever have doubles to start with.

另外,为了避免使用非唯一字段,你必须在独特的测试中使用NT字段,否则你将有双倍的开头。

#1