SQL Table - What should be primary key?

[Arg81]

OK, another daft question (one of those early morning ones I think )


I have a "documentation" table. All this does is store the document path as a link which can be displayed in a grid, and double clicked to be opened (all this is done using OpenFileDialog)

Now, the only things that get recorded, are;

Parent Table ID
Document Path

I thought I may get away with no primary key, I can insert new rows fines, and update existing ones (i.e. if someone changes file path), however I can't delete a row and realise this is cause VS doesn't like the table not having a primary key.

I've kinda been stuck in this scenario before. I'm not sure whats best to set as a key.
The relationship is a one-to-many : One Parent row can have multiple document links attached.

Do I;

(a) Create a DocumentRowID field in the Documentation table and set this as primary key & identity? Personally I see this as a "useless, non-needed" field, only used as an identity...(no other RowID from the parent will link back to a document path from another RowID..)

(b) Set both rowID and DocumentPath as a joint primary key? However a primary key can only be at max 900 bytes and my documentpath is set to this (stupid user's with very long folder listings on our shared network drive!) - I could probably drop to 890....(its varchar) as the RowID is an int @ 4 :- At least this way the "silly" users can't add the same document path more than once to a parent row...

(c) Neither of the above, quit what I'm doing as it's all wrong, go out and research into setting up SQL tables properly then come back realising I was a big noob


Ta!

 

[cjard]

A primary key is a field, or group of fields that can be used to uniquely identify a row in a result set

I would take this opportunity to make a better file recording system:


path, name, version, size, created by user, created on machine, result



path, name and version are PK
in windows a path can be no longer than 240 characters and a file name can be no longer than 255. the total of path+filename must be less than 255 characters. Files created without heed to these elimits become inaccessible.

We have problems with users creating long paths:

x:\company\procedures and manuals\human resources procedures and manuals\new hr procedures manual\old hr procedures manual backup\hr procedures for adding, updating, and removing users from the system 20070601 135412.doc

We just tell the users not to be so dumb, and shorten their path within 1 week or the documents will be filed in the bin.

What makes it worse is that x: is mapped to a unc path that is already about 60 characters, so the full path of the file is totally inaccessible

By adding more auditing, you can see who created a file (and hence who to bollock), what machine (and hence what old version of word causing incompatibilities) etc.. da da


Anyways.. yesh. In your example, because no two documents can have the same path, the path would be the ID and it would be a varchar 255, and any user that has a problem with that can somehow find a path shorter than 255 - after all, the maximum length of a text message on a cellphone is 160 characters and people obsess about fitting in the evening's plans and time schedules into that 160 to avoid paying the extra 10p the provider will hit them with for going a few characters over.. im sure they will show a similar dedication here

 

[Arg81]

TBH, I completely forgot about the windows file path length.

I just did a few tests and some file paths+name are fast approaching 240 characters....

Bloody users....