Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662

    Unanswered: PK on computed column

    Maybe I am missing something very obvious, but I couldn't do it:

    begin tran
    go
    create table foo (
    f1 int not null,
    f2 int not null,
    f3 as (f1 + f2) not null primary key clustered)
    go
    rollback tran
    go

    This returns:
    Server: Msg 8183, Level 16, State 1, Line 8
    Only UNIQUE or PRIMARY KEY constraints are allowed on computed columns.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Never mind, this worked:

    begin tran
    go
    create table foo (
    f1 int not null,
    f2 int not null,
    f3 as isnull((f1 + f2), 0) primary key clustered)
    go
    rollback tran
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My machines don't care for that either, although logically they shouldn't object.

    -PatP

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK...for the life of me.....why?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Actually it sarted with RogerWilco's post (http://www.dbforums.com/t1006214.html), where I was trying to demonstrate that while having 4 fields instead of 1 does not mean that you will have to perform every join on all 4 fields. And sure enough, you also can make the computed column a PK, and have a UNIQUE constraint defined on it, needless to say create indexes. Of course, when we start talking about doing those things, Brett's belief that "one should leave all connection settings to default" would have to be shattered, because this is exactly the situation where not only you have to change them, but also understand the implications of changing each and one of them...But that may be easily transfered to a dedicated thread...I already see its name...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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