I am currently working on my SQL Server project. I came across this error, and don't know how to fix it. I checked other stack posts, and it didn't work for me. I also tried YouTube. I saw a video and tried SET IDENTITY_INSERT (Table name) ON& SET IDENTITY_INSERT (Table name) OFF before and after my insert statement. Is there something wrong with my code that is not allowing me to insert data into my project?
CREATE TABLE Vet
(
[VET_NUM] INT IDENTITY(1,1) CONSTRAINT Pk_Vet_VetNum PRIMARY KEY,
[LAST_NAME] CHAR(20),
[FIRST_NAME] CHAR(20),
[STREET] CHAR(30),
[CITY] CHAR(20),
[STATE] CHAR(20),
[POSTAL_CODE] CHAR(20),
[SALARY] DECIMAL(8,2),
[DEGREE] CHAR(20),
[POSITION] CHAR(20)
);
CREATE TABLE Owner
(
[OWNER_NUM] INT IDENTITY(1,1) CONSTRAINT Pk_Owner_OwnerNum PRIMARY KEY,
[OWNER_NAME] CHAR(30) NOT NULL,
[STREET] CHAR(30),
[CITY] CHAR(20),
[STATE] CHAR(20),
[POSTAL_CODE] CHAR(20)
--[PET_NUM] CHAR(5) -- Removed as duplicate (Owner Num already exists in Pet)
);
CREATE TABLE Pet
(
[PET_NUM] INT IDENTITY(1,1) CONSTRAINT Pk_Pet_PetNum PRIMARY KEY,
[PET_NAME] CHAR(35) NOT NULL,
[STREET] CHAR(30),
[CITY] CHAR(15),
[STATE] CHAR(2),
[POSTAL_CODE] CHAR(5),
[BREED] CHAR(20),
[OWNER_NUM] INT CONSTRAINT Fk_Pet_OwnerNum FOREIGN KEY REFERENCES Owner(OWNER_NUM),-- Added Foreign key reference
[APPOINTMENT_NUM] CHAR(5)
)
CREATE TABLE Appointment
(
[APPOINTMENT_NUM] INT IDENTITY(1,1) CONSTRAINT Pk_Appointment_AppointmentNum PRIMARY KEY,
[APPOINTMENT_DATE] DATE,
[VET_NUM] INT UNIQUE NOT NULL CONSTRAINT Fk_Appointment_VetNum FOREIGN KEY REFERENCES VET(VET_NUM),-- Added Foreign key reference );
[PET_NUM] INT UNIQUE NOT NULL CONSTRAINT Fk_Appointment_PetNum FOREIGN KEY REFERENCES Pet(PET_NUM)-- Added Foreign key reference );
)
INSERT INTO vet ([vet_num],[last_name],[first_name],[street],[city],[state], [postal_code], [salary],[degree],[position])
VALUES (1, 'Skechley', 'Cristine', '24340 7th Plaza', 'Pittsburgh', 'PA', '15274', 88053.30, 'Masters', 'Vet'),
(2, 'Fishpoole', 'Sig', '06784 Anthes Point', 'Philadelphia', 'PA', '19184', 45525.56, 'Associates', 'Receptionist'),
(3, 'Stother', 'Rycca', '04304 Superior Hill', 'Allentown', 'PA', '18105', 90553.37, 'Masters', 'Vet'),
(4, 'Scandrite', 'Kerrill', '30320 Express Crossing', 'Harrisburg', 'PA', '17126', 102553.59, 'Doctorates', 'Vet'),
(5, 'Glassman', 'Rhett', '33418 Tomscot Trail', 'Mc Keesport', 'PA', '15134', 56052.24, 'Bachelor', 'Management'),
(6, 'Gioan', 'Rab', '1 Pennsylvania Street', 'Hatfield', 'PA', '19440', 35880.76, 'Student', 'Janitor'),
(7, 'Patel', 'Dhruv', '24411 Jean Drive', 'Hatfield', 'PA', '19440', 42790.88, 'Student', 'Receptionist'),
(8, 'Smith', 'Giana', '24 Malple Street', 'Lansdale', 'PA', '19446', 35880.22, 'Student', 'Janitor'),
(9, 'Lopez', 'Briana', 'Orvilla', 'Allentown', 'PA', '18105', 74880.47, 'Masters', 'Vet'),
(10, 'Sam', 'Hector', 'Orvilla', 'Allentown', 'PA', '18105', 74880.47, 'Masters', 'Vet');
INSERT INTO owner ([owner_num],[owner_name],[street],[city],[state],[postal_code])
VALUES (11, 'Sammantha Rodgers','96372 Dexter Terrace','Erie', 'PA','16510'),
(12,'Jenkins Tim','486 Marcy Avenue','Philadelphia','PA','19184'),
(13,'Smith Bobby','03781 Meadow Ridge','Erie','PA','16510'),
(14,'Parker Aaron','80 Marcy Place','Harrisburg','PA','17126'),
(15,'Gil Malcomn','239 Tony Point','Harrisburg','PA','17110'),
(16,'Ramsey Torrey','0778 Columbus Park','Philadelphia','PA','19178'),
(17,'Novak Cole','63519 Warbler Way','Pittsburgh','PA','15261'),
(18,'Cunningham Dylan','0728 Esch Terrace','Pittsburgh ','PA','15274'),
(19,'Barclay Liam','36 John Wall Parkway','Pittsburgh ','PA','15210'),
(20,'Perez Joeseph','Hatfield Village','Hatfield','PA','19440');
INSERT INTO appointment ([APPOINTMENT_NUM],[appointment_date],[VET_NUM],[PET_NUM])
VALUES (21,'12/12/2017',1,25),
(30,'12/12/2017',2,26),
(23,'12/12/2017',1,27),
(29,'12/13/2017',4,23),
(25,'12/14/2017',5,22);
INSERT INTO Pet ([PET_NUM], [PET_NAME], [STREET], [CITY], [STATE],[POSTAL_CODE], [BREED], [OWNER_NUM], [APPOINTMENT_NUM])
VALUES (21,'Jimmy','96372 Dexter Terrace','Erie', 'PA','16510','Affenpinscher',11,21),
(22,'Lily','486 Marcy Avenue','Philadelphia','PA','19184','American Bulldog',12,22),
(23,'Sally','03781 Meadow Ridge','Erie','PA','16510','American Eskimo Dog',13,23),
(24,'Joey','80 Marcy Place','Harrisburg','PA','17126','Barbet',14,24),
(25,'Rocky','239 Tony Point','Harrisburg','PA','17110','Papillon',15,25),
(26,'Sam','0778 Columbus Park','Philadelphia','PA','19178','McNab',16,26),
(27,'Chloe','63519 Warbler Way','Pittsburgh','PA','15261','Mountain Cur',17,27),
(28,'Mike','0728 Esch Terrace','Pittsburgh ','PA','15274','Pug',18,28),
(29,'Bruno','36 John Wall Parkway','Pittsburgh ','PA','15210','Pomeranian',19,9),
(30,'Daisy','Hatfield Village','Hatfield','PA','19440','Rat Terrier',20,30),
(31,'Tim','Hatfield Village','Hatfield','PA','19440','German Shepherd',20,30);
The error messages I am getting:
Msg 544, Level 16, State 1, Line 53
Cannot insert explicit value for identity column in table 'Vet' when IDENTITY_INSERT is set to OFF.Msg 544, Level 16, State 1, Line 68
Cannot insert explicit value for identity column in table 'Owner' when IDENTITY_INSERT is set to OFF.Msg 544, Level 16, State 1, Line 81
Cannot insert explicit value for identity column in table 'Appointment' when IDENTITY_INSERT is set to OFF.Msg 544, Level 16, State 1, Line 89
Cannot insert explicit value for identity column in table 'Pet' when IDENTITY_INSERT is set to OFF.
