Solved

SQL Identity Problem

Posted on 2007-03-19
3
241 Views
Last Modified: 2008-03-06
Is there a way to setup a SQL database to automatically reset an identity column based on the primary key?

Example:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IdentityTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IdentityTest]
GO

CREATE TABLE [dbo].[IdentityTest] (
      [RecordID] [int] NOT NULL ,
      [IdentityID] [int] IDENTITY (1, 1) NOT NULL ,
      [Data] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[IdentityTest] WITH NOCHECK ADD
      CONSTRAINT [PK_IdentityTest] PRIMARY KEY  CLUSTERED
      (
            [RecordID],
            [IdentityID]
      )  ON [PRIMARY]
GO


INSERT INTO IdentityTest (RecordID, Data) VALUES (1, 'DATA 1-1')
GO
INSERT INTO IdentityTest (RecordID, Data) VALUES (1, 'DATA 1-2')
GO
INSERT INTO IdentityTest (RecordID, Data) VALUES (2, 'DATA 2-1')
GO
INSERT INTO IdentityTest (RecordID, Data) VALUES (2, 'DATA 2-2')
GO
INSERT INTO IdentityTest (RecordID, Data) VALUES (2, 'DATA 2-3')
GO
INSERT INTO IdentityTest (RecordID, Data) VALUES (3, 'DATA 3-1')
GO
INSERT INTO IdentityTest (RecordID, Data) VALUES (4, 'DATA 4-1')
GO
INSERT INTO IdentityTest (RecordID, Data) VALUES (4, 'DATA 4-2')
GO
INSERT INTO IdentityTest (RecordID, Data) VALUES (4, 'DATA 4-3')
GO
INSERT INTO IdentityTest (RecordID, Data) VALUES (4, 'DATA 4-4')
GO
INSERT INTO IdentityTest (RecordID, Data) VALUES (5, 'DATA 5-1')
GO
SELECT * FROM IdentityTest
GO

Actual Results:

RecordID    IdentityID  Data
----------- ----------- -------------------------
1              1              DATA 1-1
1              2              DATA 1-2
2              3              DATA 2-1
2              4              DATA 2-2
2              5              DATA 2-3
3              6              DATA 3-1
4              7              DATA 4-1
4              8              DATA 4-2
4              9              DATA 4-3
4              10            DATA 4-4
5              11            DATA 5-1



Desired Results:

RecordID    IdentityID  Data
----------- ----------- -------------------------
1              1              DATA 1-1
1              2              DATA 1-2
2              1              DATA 2-1
2              2              DATA 2-2
2              3              DATA 2-3
3              1              DATA 3-1
4              1              DATA 4-1
4              2              DATA 4-2
4              3              DATA 4-3
4              4              DATA 4-4
5              1              DATA 5-1
0
Comment
Question by:jeshbr
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 18751139
no  the identity feature doesn't do that ...

you probably  want to implement the sequence numbering via triggers...

but how do you determine order?
by sorting the data column?

 
0
 
LVL 3

Author Comment

by:jeshbr
ID: 18751161
This is just an example of what I would like to do.  
The 'Data' column means nothing.
0
 
LVL 3

Author Comment

by:jeshbr
ID: 18751670
I was able to achive my desired result using a trigger.

Code:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_InsertTrigger_IdentityTest]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[_InsertTrigger_IdentityTest]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IdentityTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IdentityTest]
GO

CREATE TABLE [dbo].[IdentityTest] (
      [RecordID] [int] NOT NULL ,
      [IdentityID] [int] NOT NULL ,
      [Data] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[IdentityTest] WITH NOCHECK ADD
      CONSTRAINT [PK_IdentityTest] PRIMARY KEY  CLUSTERED
      (
            [RecordID],
            [IdentityID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[IdentityTest] ADD
      CONSTRAINT [DF_IdentityTest_IdentityID] DEFAULT ((0)) FOR [IdentityID]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE TRIGGER _InsertTrigger_IdentityTest ON IdentityTest FOR INSERT AS
BEGIN
IF  ( SELECT [IdentityID] FROM INSERTED) = 0
    BEGIN
        UPDATE IT SET IdentityID = (LastIdentityID.IdentityID + 1)
        FROM IdentityTest IT
            INNER JOIN INSERTED I
                  ON (I.RecordID = IT.RecordID)
                  AND (I.IdentityID = IT.IdentityID)
            INNER JOIN (
                        SELECT ITTemp.RecordID AS RecordID,
                              MAX(ITTemp.IdentityID) AS IdentityID
                        FROM IdentityTest ITTemp
                        GROUP BY ITTemp.RecordID
                  ) LastIdentityID ON (LastIdentityID.RecordID = IT.RecordID)
    END
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

INSERT INTO IdentityTest (RecordID, Data) VALUES (1, 'DATA 1-1')
GO
INSERT INTO IdentityTest (RecordID, Data) VALUES (1, 'DATA 1-2')
GO
INSERT INTO IdentityTest (RecordID, Data) VALUES (2, 'DATA 2-1')
GO
INSERT INTO IdentityTest (RecordID, Data) VALUES (2, 'DATA 2-2')
GO
INSERT INTO IdentityTest (RecordID, Data) VALUES (2, 'DATA 2-3')
GO
INSERT INTO IdentityTest (RecordID, Data) VALUES (3, 'DATA 3-1')
GO
INSERT INTO IdentityTest (RecordID, Data) VALUES (4, 'DATA 4-1')
GO
INSERT INTO IdentityTest (RecordID, Data) VALUES (4, 'DATA 4-2')
GO
INSERT INTO IdentityTest (RecordID, Data) VALUES (4, 'DATA 4-3')
GO
INSERT INTO IdentityTest (RecordID, Data) VALUES (4, 'DATA 4-4')
GO
INSERT INTO IdentityTest (RecordID, Data) VALUES (5, 'DATA 5-1')
GO
SELECT * FROM IdentityTest
GO
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question