locked
Identity Column with Null value RRS feed

  • Question

  • User2048898515 posted

    Hi Team,

    I will be having a table with 3 columns:(for simplicity purpose)

    1)ID- It is a GUID

    2)Description- Varchar

    3)tempId- integer

    tempId column will incremented by 1 for each row.

    We have one business logic, for that we have to empty the tempId for the security purposes.

    But if we keep the tempId as identity column , i found that we cant update the field to null.

    Can you suggest what are all the different ways to handle it?

    one of my colleague told me to use the SP to get the max of tempId and increment by 1 and use that in C# to insert it.

    Monday, June 25, 2018 6:01 AM

All replies

  • User745891735 posted

    Hello Nambir,

    remove identity from "tempId" column.

    Declare @NextTempId int

    Select @NextTempId = max(isnull(tempId,0)) + 1 from table.

    use @NextTempId to insert in table.

    Monday, June 25, 2018 6:19 AM
  • User-330142929 posted

     Hi Nambir, 

    As far as I know, as the identity column of the database, we cannot set Allow Nulls= true. because it is the identification column, used to identify the only record.

    For security purposes, Guid could be used as an Identity column. I suggest that you export this data migration to another table, using the following SQL statement.

    Please ensure that the column data types are the same.

    ALTER TABLE table1 ADD [column1] INT,[column2] nvarchar(50)
    go
    INSERT INTO table1 ([column1],[column2])
    SELECT [column1],[column2] FROM table2

    I have made a demo, wish it is useful to you.

    Products Table Design.

    CREATE TABLE [dbo].[Products] (
        [Id]    INT           IDENTITY (1, 1) NOT NULL,
        [Name]  NVARCHAR (50) NOT NULL,
        [Price] INT           NOT NULL,
        PRIMARY KEY CLUSTERED ([Id] ASC)
    );

    Table1 Table Design.

    CREATE TABLE [dbo].[Table1]
    (
           [Guid] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT newid()
    )

    Migration Code.

    ALTER TABLE Table1 ADD [ProductName] nvarchar(50),[ProductPrice] int
    go
    INSERT INTO Table1 ([ProductName],[ProductPrice])
    SELECT Name,Price FROM Products

    How it works.

    Feel free to let me know if you have any question.

    Best Regards,

    Abraham

     

     

    Tuesday, June 26, 2018 9:07 AM