Can a Unique Index be set on a secondary linked table field?

[Stryder_1]

Hi, For an example, using Sql Server - let's say I have three tables: Client: ID Name File: ID Name Bytes ClientID Data: ID DataField FileID Each record in Data can be linked back to each Client record through the File Record. I want the DataField value to be unique only per client - so two values of "42" in the DataField is ok as long as they link to different clients. I could put the ClientID in the Data table, but that would then mean I would need to be sure the ClientID in the related File record always matches. Can an index be set on the Data table using the DataField field and the Client ID field? If so, how would you do this? If not, is there an alternative? Thanks

[Lost User]

Stryder_1 wrote:

I want the DataField value to be unique only per client

How about this?

Client
ID
Name
DataField

File
ID
Name
Bytes
ClientID

That's what you asked literally, but not what you'd want;

Client
ID
Name

File
ID
Name
Bytes
DataField
ClientID

With a UNIQUE constraint on DataField and ClientId :)

I are Troll :suss:

modified on Monday, October 18, 2010 3:56 PM

[Lost User]

I think you can add two unique constaint in File..ClientID and Data..FileID to achieve your goal. By your data design, if you want the DataField value to be unique only per client, the infomation in [File] should also unique match one [Client] record. Client: ID Name File: ID Name Bytes ClientID <<< Data: ID DataField FileID <<<