Auto Generate Unique ID separate from Primary Key

Question

I have a Relational database (that I'm new to creating) that has many tables relating to baseball statistical tracking and analysis. The table I'm working on currently is the tbl_PitchLog table, that tracks pitches in an at-bat.

What I'd like to do is eliminate the need for an at-bat table and just use At-Bat Unique ID for a group of pitches. I have a Pitch_ID field, but I'd like SS to generate a new AtBat_ID that I can then reference to get all the pitches of a particular at-bat.

For Example

Pitch_ID | Pitch_Number | Result
1            1            Foul
2            2            Foul
3            3            Strike
4            1            Ball
5            2            Flyout
6            1            Groundout

To be:

Pitch_ID | AtBat_ID | Pitch_Number | Result
1           1            1            Foul
2           1            2            Foul
3           1            3            Strike
4           2            1            Ball
5           2            2            Flyout
6           3            1            Groundout

score 2 · Accepted Answer · edited Nov 26 '17 at 08:39

2

You don't specify what version of SS you're using, but SQL Server 2012 introduced sequences; you can create an at bat sequence, get the next value when the at bat changes, and use that value for your inserts.

CREATE SEQUENCE at_bat_seq
 AS INTEGER
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE <what you want the max to be>
 NO CYCLE; 

DECLARE @at_bat int;
SET @at_bat = NEXT VALUE FOR at_bat_seq;

Most of the qualifiers are self-explanatory; the [NO] CYCLE specifies whether the value will start over at the min when it hits the max. As defined above, you'll get an error when you get to the max. If you just want it to start over when it gets to the max, then specify CYCLE instead of NO CYCLE.

edited Nov 26 '17 at 08:39

marc_s

732,580
175
1,330
1,459

answered Nov 25 '17 at 23:19

vr8ce

476
2
13

Genius! I'm using VS Pro 2015 so I'm pretty sure it's got SS 2012 – jDave1984 Nov 26 '17 at 00:21
If I don't want it to cycle, do I need to provide a Max Value – jDave1984 Nov 26 '17 at 00:29

score 0 · Answer 2 · answered Nov 25 '17 at 23:02

0

Create the tbl_PitchLog table with a Pitch_ID as its primary key, while it's at the same time a foreign key taken from the main table.

What you're looking for is a one to one relationship.

answered Nov 25 '17 at 23:02

Shimmy Weitzhandler

101,809
122
424
632

`Pitch_ID` is already my primary key, but I'm looking for a way to generate the `AtBat_ID` without having to create a redundant `tbl_AtBat` table. All the data for each at-bat would already be stored in this table. Although it would be rolling up into Game and Season tables, as well – jDave1984 Nov 25 '17 at 23:10
Although this would actually be a one-to-many relationship. I have a Game_ID and Season_ID in table as well ;) – jDave1984 Nov 25 '17 at 23:11

Auto Generate Unique ID separate from Primary Key

2 Answers2