SQL Identity Problem

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
LVL 3
jeshbrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jeshbrAuthor Commented:
This is just an example of what I would like to do.  
The 'Data' column means nothing.
0
jeshbrAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.