unique column of type nvarchar

Question

I have a simple table:

File: path | deleted | categories | description

I want to use the table for a Linq to SQL entity model. On the model, that column path (which is string there, nvarchar in database) can be set as primary key, but this is not the case with the Visual Studio table designer.

A path is what makes one file unique, so I have to make sure there are no duplicate paths in the table. How to achieve that ? Thanks for the time.

Solution 1

You make a column unique with a UNIQUE constraint, a NOT NULL UNIQUE constraint, or PRIMARY KEY constraint in SQL. But every current dbms that I can think of off the top of my head has one or more restrictions on the length of a columnm that can be constrained that way.

That means you have a fairly show-stopping problem for the most general case. A UNC path on Windows, for example, can be about 32,767 characters long.

Linux systems vary widely. 1024 and 4096 seem common, based on quick Google research.

I think you're going to have to put a unique constraint on only a surrogate key. (You have no idea how much it hurts a database guy to say that.) The problem is that you can enforce uniqueness on the surrogate, but not on the thing it takes the place of. And the thing it takes the place of is the important part.

An ID number won't work in the general case; you could easily end up with {1, /etc/adjtime}, {2, /etc/adjtime}, {3, /etc/adjtime}. You need something that somehow ties the real data to the value of the surrogate key. Something like hashbytes() would "work" in T-SQL; linq has similar functions. (But you can have collisions, like you can with almost all hash functions.)

Solution 2

As Martin already explained, you can create a primary key as long as the value <= 450 characters (or 900 characters, if you are able to constrain the data such that Unicode characters are not allowed). This works fine:

CREATE TABLE dbo.sample1
(
  path NVARCHAR(450) NOT NULL PRIMARY KEY,
  deleted BIT NOT NULL DEFAULT 0
  -- ... other columns ...
);

CREATE TABLE dbo.sample2
(
  path VARCHAR(900) NOT NULL PRIMARY KEY,
  deleted BIT NOT NULL DEFAULT 0
  -- ... other columns ...
);

Whether some visual designer in some undeclared version of Visual Studio allows you to do that, I have no idea, but you don't have to create your tables using some visual designer, do you?

If the path is liable to exceed 900 bytes, you can't make that a key, sorry. You'll have to use an IDENTITY column, some other surrogate, or a hash of the path value to use in the key. Example of a hash that will support up to a path of 4000 characters - which doesn't satisfy all potential use cases, but hopefully you are not needing to exceed 4000:

CREATE TABLE dbo.sample3
(
  [path] NVARCHAR(4000) NOT NULL,
  pathhash AS CONVERT(VARBINARY(900), HASHBYTES('MD5', path)) 
    PERSISTED PRIMARY KEY
);

INSERT dbo.sample3([path])
  SELECT '\\some\share\x' + REPLICATE('x', 900) + '.gif'
  UNION ALL SELECT '\\some\share\x' + REPLICATE('x', 900) + '.jpg';

Try to run the insert again.

(And again, you can double the 4000 characters to 8000 characters if you can guarantee that no path will contain Unicode characters; in that case, you can use varchar(8000) instead of nvarchar(4000).)