What’s wrong with this SQL CREATE + INSERT batch?

What is wrong with this SQL statement?

Create table JaretsSchedule
(
    ScheduleID Int Primary Key NOT NULL,
    FieldID Int NOT NULL,
    HomeTeamID Int NOT NULL,
    AwayTeamID Int NOT NULL,
    GameTime DateTime NOT NULL
);

Insert into JaretsSchedule
Values 
(1, 1, 1, 2, '2012-10-02 12:00:00'),
(1, 1, 1, 3, '2012-10-17 12:00:00'),
(1, 1, 1, 4, '2012-09-23 12:00:00'),
(1, 2, 2, 1, '2012-09-12 12:00:00'),
(1, 3, 3, 1, '2012-08-19 12:00:00'),
(1, 4, 4, 1, '2012-07-25 12:00:00'),
(2, 2, 2, 1, '2012-09-15 12:00:00'),
(2, 2, 2, 3, '2012-09-06 12:00:00'),
(2, 2, 2, 4, '2012-08-28 12:00:00'),
(2, 1, 1, 2, '2012-10-02 12:00:00'),
(2, 3, 3, 2, '2012-08-11 12:00:00'),
(2, 4, 4, 2, '2012-07-17 12:00:00'),
(3, 3, 3, 1, '2012-08-19 12:00:00'),
(3, 3, 3, 2, '2012-08-11 12:00:00'),
(3, 3, 3, 4, '2012-08-03 12:00:00'),
(3, 1, 1, 3, '2012-10-17 12:00:00'),
(3, 2, 2, 3, '2012-10-17 12:00:00'),
(3, 4, 4, 3, '2012-07-09 12:00:00'),
(4, 4, 4, 1, '2012-07-25 12:00:00'),
(4, 4, 4, 2, '2012-07-17 12:00:00'),
(4, 4, 4, 3, '2012-07-09 12:00:00'),
(4, 1, 1, 4, '2012-09-23 12:00:00'),
(4, 2, 2, 4, '2012-08-28 12:00:00'),
(4, 3, 3, 4, '2012-08-03 12:00:00');

​x
 
Create table JaretsSchedule(    ScheduleID Int Primary Key NOT NULL,    FieldID Int NOT NULL,    HomeTeamID Int NOT NULL,    AwayTeamID Int NOT NULL,    GameTime DateTime NOT NULL);​Insert into JaretsScheduleValues (1, 1, 1, 2, '2012-10-02 12:00:00'),(1, 1, 1, 3, '2012-10-17 12:00:00'),(1, 1, 1, 4, '2012-09-23 12:00:00'),(1, 2, 2, 1, '2012-09-12 12:00:00'),(1, 3, 3, 1, '2012-08-19 12:00:00'),(1, 4, 4, 1, '2012-07-25 12:00:00'),(2, 2, 2, 1, '2012-09-15 12:00:00'),(2, 2, 2, 3, '2012-09-06 12:00:00'),(2, 2, 2, 4, '2012-08-28 12:00:00'),(2, 1, 1, 2, '2012-10-02 12:00:00'),(2, 3, 3, 2, '2012-08-11 12:00:00'),(2, 4, 4, 2, '2012-07-17 12:00:00'),(3, 3, 3, 1, '2012-08-19 12:00:00'),(3, 3, 3, 2, '2012-08-11 12:00:00'),(3, 3, 3, 4, '2012-08-03 12:00:00'),(3, 1, 1, 3, '2012-10-17 12:00:00'),(3, 2, 2, 3, '2012-10-17 12:00:00'),(3, 4, 4, 3, '2012-07-09 12:00:00'),(4, 4, 4, 1, '2012-07-25 12:00:00'),(4, 4, 4, 2, '2012-07-17 12:00:00'),(4, 4, 4, 3, '2012-07-09 12:00:00'),(4, 1, 1, 4, '2012-09-23 12:00:00'),(4, 2, 2, 4, '2012-08-28 12:00:00'),(4, 3, 3, 4, '2012-08-03 12:00:00');​

With the resulting error:

Msg 2627, Level 14, State 1, Line 8
Violation of PRIMARY KEY constraint ‘PK__JaretsSc__9C8A5B696CFE9A03’. Cannot insert duplicate key in object ‘dbo.JaretsSchedule’. The duplicate key value is (1).

Answer

The error message is literally telling you what the problem is. You can’t insert the same value more than once for the ScheduleID column, which you have defined as the primary key. Once workaround here would be to just make this column an identity/auto increment column. Then, don’t even include a value for it when inserting. Instead, let SQL Server handle it:

CREATE TABLE JaretsSchedule (
    ScheduleID INT NOT NULL IDENTITY PRIMARY KEY,
    FieldID INT NOT NULL,
    HomeTeamID INT NOT NULL,
    AwayTeamID INT NOT NULL,
    GameTime DateTime NOT NULL
);

INSERT INTO JaretsSchedule (FieldID, HomeTeamID, AwayTeamID, GameTime)
VALUES
    (1, 1, 2, '2012-10-02 12:00:00'),
    (1, 1, 3, '2012-10-17 12:00:00'),
    (1, 1, 4, '2012-09-23 12:00:00'),
    ...

 
CREATE TABLE JaretsSchedule (    ScheduleID INT NOT NULL IDENTITY PRIMARY KEY,    FieldID INT NOT NULL,    HomeTeamID INT NOT NULL,    AwayTeamID INT NOT NULL,    GameTime DateTime NOT NULL);​INSERT INTO JaretsSchedule (FieldID, HomeTeamID, AwayTeamID, GameTime)VALUES    (1, 1, 2, '2012-10-02 12:00:00'),    (1, 1, 3, '2012-10-17 12:00:00'),    (1, 1, 4, '2012-09-23 12:00:00'),    ...​

Note also that I explicitly list out the columns, in order, which are the target for the insert. This is also best practice, and avoids a situation later on wher it might not be clear to which columns the data in the VALUES clause corresponds.

Advertisement

Answer