IDENTITY beroende på annat värde

2 svar · 451 visningar · startad av Dino 26 feb. 2007

DinoMedlem sedan sep. 20011 914 inlägg

Frågan26 feb. 2007 10:52#1

SqlServer 2005

Vet inte hur jag skall gå tillväga. Vid en INSERT så vill jag att CustomId skall öka med ett, som en IDENTITY, beroende på AppId. CustomId måste vara unik per AppId. INSERTen nedan innehåller bara test data.

CREATE TABLE [Invoice] (
	[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
	[AppId] [int] NOT NULL ,
	[CustomId] [int] NOT NULL ,
	CONSTRAINT [PK_Snippets] PRIMARY KEY  CLUSTERED 
	(
		[Id]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

INSERT INTO [dbo].[Invoice]([AppId], [CustomId]) VALUES(1,1)
INSERT INTO [dbo].[Invoice]([AppId], [CustomId]) VALUES(1,2)
INSERT INTO [dbo].[Invoice]([AppId], [CustomId]) VALUES(2,1)
INSERT INTO [dbo].[Invoice]([AppId], [CustomId]) VALUES(1,3)
INSERT INTO [dbo].[Invoice]([AppId], [CustomId]) VALUES(1,4)
INSERT INTO [dbo].[Invoice]([AppId], [CustomId]) VALUES(1,5)
INSERT INTO [dbo].[Invoice]([AppId], [CustomId]) VALUES(2,2)
INSERT INTO [dbo].[Invoice]([AppId], [CustomId]) VALUES(1,6)

SELECT * FROM dbo.Invoice

emissionMedlem sedan dec. 19996 721 inlägg

26 feb. 2007 12:04#2

Skapa en ny tabell, med kolumner för AppId och sekvensvärde. När du INSERT:ar hämtar du först ett nytt värde från tabellen, och uppdaterar den. Använder du en DBMS med stöd för triggers kan hela manövern göras i en dylik.

DinoMedlem sedan sep. 20011 914 inlägg

26 feb. 2007 12:51#3

Ok, förstår hur du menar.

Hur skulle den här varianten fungera? Skulle man kunna vara säker på att CustomId blir unik?

CREATE TABLE [Invoice] (
	[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
	[AppId] [int] NOT NULL ,
	[CustomId] [int] NOT NULL ,
	CONSTRAINT [PK_Invoice] PRIMARY KEY  CLUSTERED 
	(
		[Id]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

--Fill with data
INSERT INTO [dbo].[Invoice]([AppId], [CustomId]) VALUES(1,1)
INSERT INTO [dbo].[Invoice]([AppId], [CustomId]) VALUES(1,2)
INSERT INTO [dbo].[Invoice]([AppId], [CustomId]) VALUES(2,1)
INSERT INTO [dbo].[Invoice]([AppId], [CustomId]) VALUES(1,3)
INSERT INTO [dbo].[Invoice]([AppId], [CustomId]) VALUES(1,4)
INSERT INTO [dbo].[Invoice]([AppId], [CustomId]) VALUES(1,5)
INSERT INTO [dbo].[Invoice]([AppId], [CustomId]) VALUES(2,2)
INSERT INTO [dbo].[Invoice]([AppId], [CustomId]) VALUES(1,6)

--The INSERT
DECLARE @oldid int;
SET @oldid = (SELECT TOP 1 i.CustomId FROM dbo.Invoice i WHERE AppId = 1 ORDER BY i.[Id] DESC);
SET @oldid = @oldid + 1;

INSERT INTO [dbo].[Invoice]([AppId], [CustomId]) VALUES(1, @oldid);

--View data
SELECT * FROM dbo.Invoice