BowlingLeagueStructure.SQL missing identity keywords for SQL Server
Closed this issue · 4 comments
Deleted user commented
Database: SQL Server 2017 - Express.
I needed to add "IDENTITY (1, 1)" to the ID columns for the Bowlers, Teams, Tournaments, and Tourney_Matches table definitions for the data query to insert without errors.
DJSteele commented
I'm curious to know what behaviour you saw when the IDENTITY (1,1) was not
included in the script.
The script has been successfully run by many users, since it was used not
just for this book, but for other books as well.
While it's true that you would need to include the IDENTITY (1,1) if you
wanted SQL Server to generate the IDs for you, the script to load the data
(in BowlingLeagueData.SQL) explicitly includes the ID fields, and provides
them with values.
For example, the script to generate the Tournaments table (contained in
BowlingLeagueStructure.SQL) is
CREATE TABLE Tournaments (
TourneyID int NOT NULL ,
TourneyDate date NULL ,
TourneyLocation varchar (50) NULL
)
GO
ALTER TABLE Tournaments ADD
CONSTRAINT Tournaments_PK PRIMARY KEY
(
TourneyID
)
GO
and part of the script to load the table (contained in
BowlingLeagueData.SQL) is
INSERT INTO Tournaments
(TourneyID, TourneyDate, TourneyLocation)
VALUES (1, '2015-09-04', 'Red Rooster Lanes');
INSERT INTO Tournaments
(TourneyID, TourneyDate, TourneyLocation)
VALUES (2, '2015-09-11', 'Thunderbird Lanes');
You can see the Primary Key for the table is declared to be TourneyID, and
each INSERT statement provides a unique value for TourneyID.
If you're experiencing something different than this, please provide us
with details.
Thanks.
Doug Steele
…On Sat, Mar 23, 2019 at 5:17 PM Zach Churchill ***@***.***> wrote:
Database: SQL Server 2017 - Express.
I needed to add "IDENTITY (1, 1)" to the ID columns for the Bowlers,
Teams, Tournaments, and Tourney_Matches table definitions for the data
query to insert without errors.
—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
<#21>, or mute the
thread
<https://github.com/notifications/unsubscribe-auth/ARep2gBtnG4z3hsEiTbcjl_CPm7Cu-MLks5vZpn0gaJpZM4cFJrd>
.
Deleted user commented
Ah, yeah I was not very clear with one point - it doesn't seem to stop SQL Server from running the insert queries, though it does show errors in the console (See attached picture). That is, I'm still able to query the data afterwards. I mostly just wanted to make you all (and other potential users) aware.
DJSteele commented
Very odd. I know all the files were tested by third parties, but I see what
you're describing!
In actual fact, since we did not use IDENTITY fields at all in that
database, all the SET IDENTITY_INSERT in the BowlingLeagueData.SQL are
incorrect, and should be commented out.
For instance, before the INSERT statements to add data to table
Tournaments, there is
SET IDENTITY_INSERT Tournaments ON
GO
And after the INSERT statements, there is
SET IDENTITY_INSERT Tournaments OFF
GO
Simply delete those lines, or change them to comments, and you should be
fine.
Thanks for bringing this to our attention.
Doug Steele
…On Sun, Mar 24, 2019 at 4:06 PM Zach Churchill ***@***.***> wrote:
Ah, yeah I was not very clear with one point - it doesn't seem to stop SQL
Server from running the insert queries, though it does show errors in the
console (See attached picture). That is, I'm still able to query the data
afterwards. I mostly just wanted to make you all (and other potential
users) aware.
[image: BowlingLeagueErrors]
<https://user-images.githubusercontent.com/32725550/54885075-ce799180-4e4e-11e9-8d8f-a788438fbc63.PNG>
—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
<#21 (comment)>,
or mute the thread
<https://github.com/notifications/unsubscribe-auth/ARep2sWAZ9dGRMpKBIYK3aWxERAKYIxBks5vZ9rVgaJpZM4cFJrd>
.
DJSteele commented
Fixed. Thanks for finding this.
John Viescas
On Mar 24, 2019, at 9:51 PM, Doug Steele <djsteele@gmail.com> wrote:
Very odd. I know all the files were tested by third parties, but I see what you're describing!
In actual fact, since we did not use IDENTITY fields at all in that database, all the SET IDENTITY_INSERT in the BowlingLeagueData.SQL are incorrect, and should be commented out.
For instance, before the INSERT statements to add data to table Tournaments, there is
SET IDENTITY_INSERT Tournaments ON
GO
And after the INSERT statements, there is
SET IDENTITY_INSERT Tournaments OFF
GO
Simply delete those lines, or change them to comments, and you should be fine.
Thanks for bringing this to our attention.
Doug Steele
On Sun, Mar 24, 2019 at 4:06 PM Zach Churchill <notifications@github.com <mailto:notifications@github.com>> wrote:
Ah, yeah I was not very clear with one point - it doesn't seem to stop SQL Server from running the insert queries, though it does show errors in the console (See attached picture). That is, I'm still able to query the data afterwards. I mostly just wanted to make you all (and other potential users) aware.
<https://user-images.githubusercontent.com/32725550/54885075-ce799180-4e4e-11e9-8d8f-a788438fbc63.PNG>
—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub <#21 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ARep2sWAZ9dGRMpKBIYK3aWxERAKYIxBks5vZ9rVgaJpZM4cFJrd>.