Unique row constraint in SQL Server

Question

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.

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

Surely having an identity means there will be no unique rows, are you talking about unique across the rest of the columns — benni_mac_b
– benni_mac_b, Commented Feb 8, 2012 at 8:17
Could you tell us what is the interest of such a constraint? With fields such as timestamp and clientNr, I doubt that you can even get duplicate rows. — Philippe Grondier
– Philippe Grondier, Commented Feb 8, 2012 at 8:25
The data are logfiles that users can import. But when two users import the same logfiles, I want to avoid that the data is inserted twice. — F.P
– F.P, Commented Feb 8, 2012 at 8:37

Quassnoi · Accepted Answer · 2012-02-08 08:35:36Z

8

Create a UNIQUE index on hashed values:

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)

answered Feb 8, 2012 at 8:35

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

F.P Over a year ago

Would'nt it be easier to hash the complete row and create the index over that hash?

Quassnoi Over a year ago

@Florian: easier for who and how do you "hash the complete row"? An index on 3 16-byte fields is not a problem for the server. And if you are going to concatenate all values then it's a possible source for confusion between, say, server=MSSQLServer,account=20081 and server=MSSQLServer2008,account=1.

F.P Over a year ago

Okay, that makes sense. Can I add the other rows to the index, too? (i.e. timestamp, clientnr and storenr) So the index will effectively be over all columns in the end? Without the IDENTITY of course.

Quassnoi Over a year ago

@Florian: of course. If they're not varchars (like INT or DATE) you can just add them directly rather than hashed.

TomTom · Accepted Answer · 2012-02-08 08:20:15Z

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.

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.

answered Feb 8, 2012 at 8:20

TomTom

62.4k11 gold badges95 silver badges152 bronze badges

1 Comment

F.P Over a year ago

This is just a table to hold values from a logfile. None of the fields have functional, transitive, multivalued or any other dependencies to each other. Of course, that would mean that there can never be two identical rows - but, as the logfiles are imported, a user could import the same logfile twice and therefore create two identical rows.

Collectives™ on Stack Overflow

Unique row constraint in SQL Server

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related