?
Solved

Visual Studio 2008, VB, SQL: How to create 'identity increment' for clustered key field? Syntax?

Posted on 2008-11-12
3
Medium Priority
?
561 Views
Last Modified: 2013-11-27
I have following stored procedure to create several database tables (see attached code).

In line 103 (and also for other fields that are part of clustered keys) I want the identifying field value to increment by 1 however not further so for al records in the database (as by IDENTITY (100000,1)' but per [CompetitionId], [MatchId], [PlayerId], [ScoreItemId] value combination (in this example).

How is that properly done? I thought of creating a function (stored procedure?) that can be used in the DEFAULT attribute like DEFAULT (newid()), but than different. Is that a good approach? What should the code look like (syntax)?

I work with Visual Web Developer 2008, VB.
1    ALTER PROCEDURE dbo.ScoreCompleteAdd
2    	/*
3    	*/
4    AS
5    	SET NOCOUNT OFF;
6    
7    	IF OBJECT_ID ('dbo.Competition', 'U') IS NULL
8    	BEGIN
9    	CREATE TABLE [dbo].[Competition]
10   	(
11   	[CompetitionId] [int] NOT NULL IDENTITY (1,1),
12   	[Description] [nvarchar] NULL,
13   	[StartDateTime] [datetime] NULL DEFAULT (getdate()),
14   	[EndDateTime] [datetime] NULL,
15   	[ScoreCurrent] [int] NULL,
16   	[ScoreIntermediate] [int] NULL,
17   	[ScoreEnd] [int] NULL
18       PRIMARY KEY ([CompetitionId])
19       WITH (IGNORE_DUP_KEY = OFF)
20   	) 
21   	ON [PRIMARY]
22   	END;
23   
24   	IF OBJECT_ID ('dbo.Match','U') IS NULL
25   	BEGIN
26   	CREATE TABLE [dbo].[Match]
27   	(
28   	[CompetitionId] [int] NOT NULL REFERENCES Competition(CompetitionId),
29   	[MatchId] [int] NOT NULL IDENTITY (10,1),
30   	[Description] [nvarchar] NULL,
31   	[StartDateTime] [datetime] NULL,
32   	[EndDateTime] [datetime] NULL,
33   	[ScoreCurrent] [int] NULL,
34   	[ScoreIntermediate] [int] NULL,
35   	[ScoreEnd] [int] NULL
36       PRIMARY KEY CLUSTERED ([CompetitionId], [MatchId])
37       WITH (IGNORE_DUP_KEY = OFF)
38   	) 
39   	ON [PRIMARY]
40   	END;
41   
42   	IF OBJECT_ID ('dbo.PlayingClub','U') IS NULL
43   	BEGIN
44   	CREATE TABLE [dbo].[PlayingClub]
45   	(
46   	[CompetitionId] [int] NOT NULL REFERENCES Competition(CompetitionId),
47   	[MatchId] [int] NOT NULL FOREIGN KEY (CompetitionId, MatchId) REFERENCES Match,
48   	[PlayingClubId] [int] NOT NULL IDENTITY (100,1),
49   	[Description] [nvarchar] NULL,
50   	[ScoreCurrent] [int] NULL,
51   	[ScoreIntermediate] [int] NULL,
52   	[ScoreEnd] [int] NULL
53       PRIMARY KEY CLUSTERED ([CompetitionId], [MatchId], [PlayingClubId])
54       WITH (IGNORE_DUP_KEY = OFF)
55   	) 
56   	ON [PRIMARY]
57   	END;
58   
59   
60   	IF OBJECT_ID ('dbo.Player','U') IS NULL
61   	BEGIN
62   	CREATE TABLE [dbo].[Player]
63   	(
64   	[CompetitionId] [int] NOT NULL REFERENCES Competition(CompetitionId),
65   	[MatchId] [int] NOT NULL FOREIGN KEY (CompetitionId, MatchId) REFERENCES Match,
66   	[PersonId] [int] NOT NULL REFERENCES Person(PersonId),
67   	[PlayerId] [int] NOT NULL IDENTITY (1000,1),
68   	[ScoreCurrent] [int] NULL,
69   	[ScoreIntermediate] [int] NULL,
70   	[ScoreEnd] [int] NULL
71       PRIMARY KEY CLUSTERED ([CompetitionId], [MatchId], [PlayerId])
72       WITH (IGNORE_DUP_KEY = OFF)
73   	) 
74   	ON [PRIMARY]
75   	END;
76   
77   
78   	IF OBJECT_ID ('dbo.ScoreItem','U') IS NULL
79   	BEGIN
80   	CREATE TABLE [dbo].[ScoreItem]
81   	(
82   	[CompetitionId] [int] NOT NULL REFERENCES Competition(CompetitionId),
83   	[MatchId] [int] NOT NULL FOREIGN KEY (CompetitionId, MatchId) REFERENCES Match,
84   	[PlayerId] [int] NOT NULL FOREIGN KEY (CompetitionId, MatchId, PlayerId) REFERENCES Player,
85   	[ScoreItemId] [int] NOT NULL IDENTITY (10000,1),
86   	[ScoreCurrent] [int] NULL,
87   	[ScoreIntermediate] [int] NULL,
88   	[ScoreEnd] [int] NULL
89       PRIMARY KEY CLUSTERED ([CompetitionId], [MatchId], [PlayerId], [ScoreItemId])
90       WITH (IGNORE_DUP_KEY = OFF)
91   	) 
92   	ON [PRIMARY]
93   	END;
94   	
95   	IF OBJECT_ID ('dbo.ScoreDetail','U') IS NULL
96   	BEGIN
97   	CREATE TABLE [dbo].[ScoreDetail]
98   	(
99   	[CompetitionId] [int] NOT NULL REFERENCES Competition(CompetitionId),
100  	[MatchId] [int] NOT NULL FOREIGN KEY (CompetitionId, MatchId) REFERENCES Match,
101  	[PlayerId] [int] NOT NULL FOREIGN KEY (CompetitionId, MatchId, PlayerId) REFERENCES Player,
102  	[ScoreItemId] [int] NOT NULL FOREIGN KEY (CompetitionId, MatchId, PlayerId, ScoreItemId) REFERENCES ScoreItem,
103  	[ScoreDetailId] [int] NOT NULL IDENTITY (100000,1),
104  	[Score] [int] NULL
105      PRIMARY KEY CLUSTERED ([CompetitionId], [MatchId], [PlayerId], [ScoreItemId], [ScoreDetailId])
106      WITH (IGNORE_DUP_KEY = OFF)
107  	) 
108  	ON [PRIMARY]
109  	END;
110  	
111  	/* SET NOCOUNT ON */
112  	RETURN

Open in new window

0
Comment
Question by:ruud00000
2 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 1500 total points
ID: 22953591
Which version of SQL Server are you using?
0
 

Author Closing Comment

by:ruud00000
ID: 31516149
Sorry, i didn't continue with this. Assigned points anyway.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

862 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