Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    8

    Unanswered: Unable to create unique constraint on a NULL column

    Hi all,

    I am trying to add a unique index/constraint on a column that allows NULL values. The column does have NULL values and when I try to create a unique constraint, I get the following error.

    CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 9. Most significant primary key is '<NULL>'.

    Are'nt you allowed to create a UNIQUE constraint on a NULL column? Books Online says that you are allowed to create a unique constraint on NULL columns, then why am I getting this error.

    Any help would be appreciated.
    Thanks,
    Amir

  2. #2
    Join Date
    Oct 2003
    Posts
    8

    Multiple rows

    One more thing, there are multiple rows in the table with NULL in the column. If there is only one row, there is no problem. ButI want to have a Unique constraint even when there are multiple NULL rows and from what I understand, that should be allowed.

    Thanks in advance,
    Amir

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Column has to have no more than one value NULL.

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Multiple rows

    Originally posted by ac_786
    One more thing, there are multiple rows in the table with NULL in the column. If there is only one row, there is no problem. ButI want to have a Unique constraint even when there are multiple NULL rows and from what I understand, that should be allowed.

    Thanks in advance,
    Amir
    Use trigger as constraint...

  5. #5
    Join Date
    Oct 2003
    Posts
    8
    My problem is that, this column needs to serve in a foreign key constraint (as the master column). If I am unable to enforce a unqiue key constraint on the column, then I will be unable to have a foreign key constraint pointing to this column.

    Do you think that the column needs to have only ONE NULL value? That would seem absurd.

    Any more ideas, please?

    Thanks,
    Amir

  6. #6
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by ac_786
    My problem is that, this column needs to serve in a foreign key constraint (as the master column). If I am unable to enforce a unqiue key constraint on the column, then I will be unable to have a foreign key constraint pointing to this column.

    Do you think that the column needs to have only ONE NULL value? That would seem absurd.

    Any more ideas, please?

    Thanks,
    Amir
    Yep, only ONE NULL value. It is an uniqueness. What is the problem with your table about nulls? Update it or remove extra rows for while.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You're trying to violate the basics of any RDBMS. PK should not allow nulls, needless to say have multiple rows with nulls. Maybe if you think about it a little longer you may come up with an alternative design?

  8. #8
    Join Date
    Oct 2003
    Posts
    8
    Ok. Why don't you tell me a better design?

    I have a table called TblDeal that is being created to consolidate Debt and Equity positions. That TblDeal has a column called DebtID and EquityId which basically come from the TblDebt and the TblEquity table.

    Each row in the TblDeal, can either be a Debt or an Equity and depending upon which one it is, either the DebtID or the EquityId will be populated. Also the TblDeal table has lots of fields that are common between Debt and Equity.

    Is there a better way to design this Tbldeal table and have the DebtID and the EquityId populated.?

    Thanks,
    Amir

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    OK, in short, the table that this tblDeal relates to through these 2 fields, should have an indicator that would say whether it's a Dept or Equity. And that's how tblDeal should be structured as well. This way you don't have 2 fields, but just one.

Posting Permissions

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