Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2010
    Posts
    1

    Unanswered: IDENTITY Crisis: Converting an identity column to non-integer datatype

    Hi all:
    I'm new to SQL and need to know if the following is possible:
    I have a table with the following fields:

    Item - varchar(20)
    IDNo - int, IDENTITY (1,1)

    I need a third column to do the following:
    1) Serve as a Primary Key for the table
    2) Be a computed column of the form Item+IDNo
    3) Be able to establish relationships with Foreign Keys

    For example: Item = X, IDNo = 1, KeyColumn = X1

    Any ideas?

  2. #2
    Join Date
    Sep 2010
    Posts
    153

    Smile try this, it will work.

    [B]create table item_details (IDNo int identity(1, 1), Item varchar(20), item_id as convert(nvarchar, Item) + convert(nvarchar, IDNo) persisted primary key );




    tablename = item_details.

    we are converting datatype of IDNo into nvarchar and we need to declare auto computed field as persisted then we can create a table.

    if you insert value in item field, automaticallt it will generate an autoincrement Slno and convert ito nvarchar and combines with Item.



    Thanks.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Bleuchild View Post
    Hi all:
    I'm new to SQL and need to know if the following is possible:
    I have a table with the following fields:

    Item - varchar(20)
    IDNo - int, IDENTITY (1,1)

    I need a third column to do the following:
    1) Serve as a Primary Key for the table
    2) Be a computed column of the form Item+IDNo
    3) Be able to establish relationships with Foreign Keys

    For example: Item = X, IDNo = 1, KeyColumn = X1

    Any ideas?
    Yes, stop what you are doing and Make Iyem, IDNo a Composite PK

    Why? I have no idea
    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.

Tags for this Thread

Posting Permissions

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