locked
To build a foreign key relationship with a file table RRS feed

  • Question

  • Hello,

    I have got a requirement where i should create a foreign key relatioship from a normal table with a FileTable. I noticed that primary key of the FileTable is of HierarchyID data type. I created a column with this data type in my normal table and built a relationship, however I am not able to enter any value in normal table.

    So how can i reference FileTable from other Tbale, should we use other columns of FileTable say StreamID?

    Thanks!
    Shamsuddeen

    Tuesday, February 3, 2015 1:14 PM

Answers

  • Just take a look at the indices of your file table. SQL Server creates an unique index on stream_id.

    So, the answer is: Yes, use stream_id.

    Caveat: When your file is exposed by NTFS, touching a file will result in a a new stream_id.

    Tuesday, February 3, 2015 1:23 PM

All replies

  • Just take a look at the indices of your file table. SQL Server creates an unique index on stream_id.

    So, the answer is: Yes, use stream_id.

    Caveat: When your file is exposed by NTFS, touching a file will result in a a new stream_id.

    Tuesday, February 3, 2015 1:23 PM
  • Is the caveat correct ? Or maybe I don't understand it correctly ?

    I have just tried to change a file represented in a filetable with notepad.exe load from the fileshare and I find the stream_id unchanged in the Filetable row.  

    Friday, March 20, 2015 4:49 PM
  • Maybe "touching" was misleading. It depends on the tool you're using. E.g. using an Excel file while give you a different stream_id.
    Friday, March 20, 2015 5:08 PM
  • Yes you are right, I have just tested it, saving from Excel will change the stream_id. And Word does the same.

    So if you want to be able to modify files on the file system from standard programs - how do you references the row in the filetable with foreign key relationship ?

      
    Friday, March 20, 2015 7:40 PM
  • One solutions uses the filename from the file table or when to content matters, then using an additional hash.
    Saturday, March 21, 2015 1:22 PM