Menu
I'm creating a table with two columns that I want to auto-increment. One column is a primary key, so I'm using the IDENTITY keyword on it. The other column will be used to track the user-defined "sort order" of items in the table. Any time the user moves an item, its "sort order" will swap values with that of another element. However, when an item is inserted into the table, the inserted item should always be auto-assigned a sort-order value higher than any other value in the table. Here's a simplified version of the table creation script:
CREATE TABLE [AnswerRow] (
[AnswerRowId] [int] IDENTITY(1,1) NOT NULL,
[SortOrder] [int] NOT NULL,
[IsDeleted] [bit] NOT NULL CONSTRAINT [DF_AnswerRow_IsDeleted] DEFAULT 0,
CONSTRAINT [PK_AnswerRow] PRIMARY KEY CLUSTERED ([AnswerRowId] asc)
)
What's the best way to make the SortOrder column auto-increment the same way the AnswerRowId column will (but still be able to modify sort-order values afterward)?
638
Here's the SQL statement to add AUTO INCREMENT constraint to id column. ALTER TABLE sales MODIFY id INT NOT NULL AUTO_INCREMENT PRIMARY KEY; Next we will add a couple of rows in sales table. As you can see, the MySQL has automatically increased and populated id column with values 7 and 8.
So you can indeed have an AUTO_INCREMENT column in a table that is not the primary key. If that makes sense, is a different topic though. I should also mention that an AUTO_INCREMENT column should always be an integer type (technically a floating point type is also allowed) and that it should be UNSIGNED.
Here, to reset the Identity column in SQL Server you can use DBCC CHECKIDENT method. Syntax : DBCC CHECKIDENT ('table_name', RESEED, new_value); Note : If we reset the existing records in the table and insert new records, then it will show an error.
The IDENTITY keyword causes the columns to be automatically incremental, meaning that each successive insert into the table will automatically assign a value greater than any previous row of the table. These columns are often referred to as "autoincrement columns".
I'm not sure if this is what @Stephen Wrighton had in mind, but I think you could have an insert trigger make use of the IDENTITY value being generated for AnswerRowId:
CREATE TRIGGER [dbo].[AnswerRowInsertTrigger]
ON [dbo].[AnswerRow]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE a SET a.SortOrder = a.AnswerRowId
FROM AnswerRow a JOIN inserted i ON a.AnswerRowId = i.AnswerRowId
END
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
