unique column of type nvarchar
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.
Answer
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.)