none
Defining uniqueness of a record with combination of IDs RRS feed

  • Question

  • Hello,

    My question may be trivial but I just have a hard time abstracting table relationships :P

    I have 3 tables:

    [] -> primary key

    Datas: [ID], Data
    DataValues: DataID, ValueTypeID, Value
    ValueTypes: [ID], Name

    The first is the main table;
    The second is an value-attributing table and reason of this topic;
    The third is the value type definer;

    The problem is as follows:
    I want to be sure on this low-level model that ONE ValueType record only gets refferenced by ONE Data record.

    Example:
    Datas: ID -> 27, Data -> "Some Data"
    ValueTypes: ID -> 14, Name -> "Length"
    DataValues: DataID -> 27, ValueTypeID -> 14, Value -> 9

    I want not to be possible to add another record in DataValues with DataID = 27 AND ValueTypeID = 14
    It should allow, though, DataID = 27 AND ValueTypeID = 18 or DataID = 65 AND ValueTypeID = 14, for example.

    Thank you,

    Matheus Simon.

    Tuesday, September 28, 2010 2:54 PM

Answers

  • Create UNIQUE PK on Columns (DataID and ValuTypeID). 

    Alter Table DataValues
    ADD CONSTRAINT PK__DataValue__DataID_ValueTypeID PRIMARY KEY CLUSTERED (DataID, ValueTypeID)

    :)

    Cheers.

     

    • Marked as answer by Mattz Simon Tuesday, September 28, 2010 3:44 PM
    Tuesday, September 28, 2010 3:35 PM

All replies

  • Create UNIQUE PK on Columns (DataID and ValuTypeID). 

    Alter Table DataValues
    ADD CONSTRAINT PK__DataValue__DataID_ValueTypeID PRIMARY KEY CLUSTERED (DataID, ValueTypeID)

    :)

    Cheers.

     

    • Marked as answer by Mattz Simon Tuesday, September 28, 2010 3:44 PM
    Tuesday, September 28, 2010 3:35 PM
  • That worked out perfectly!

    Thanks Mohit!

    I feel kinda silly after such an easy solution..

    Tuesday, September 28, 2010 3:48 PM
  • Glad to help, sometimes just need second pair of eyes :).
    Tuesday, September 28, 2010 3:56 PM