Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2008
    Location
    Midrand, South Africa
    Posts
    22

    Unanswered: Multicolumn constraint

    I am attempting to define a constraint on an existing table that already contains data by using the following:-
    Code:
    ALTER TABLE Comments 
    ADD CONSTRAINT UniqueValues 
    UNIQUE (EventID, MemberID);
    In other words I want the combination of EventID and MemberID to be unique or to say it another way within EventID of the same value there can only be one MemberID of that value.

    but when I run it I get
    Server: Msg 1505, Level 16, State 1, Line 1
    CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 4. Most significant primary key is '228'.
    Server: Msg 1750, Level 16, State 1, Line 1
    Could not create constraint. See previous errors.
    The statement has been terminated.

    I know that there are multiple occurrences of EventID.
    Please tell me what I am misunderstanding.

  2. #2
    Join Date
    Aug 2003
    Location
    West
    Posts
    101
    Then there is multiple occurrences of EventID+MemberID

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Let SQL Server tell you what the problem is, run:
    Code:
    SELECT Count(*), EventID, MemberID
       FROM Comments 
       GROUP BY EventID, MemberID
       HAVING 1 < Count(*)
       ORDER BY Count(*) DESC, EventID, MemberID
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Oct 2008
    Location
    Midrand, South Africa
    Posts
    22

    Multicolumn constraint

    Thank you I appreciate the help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •