Good morning all,
At the moment I have this DDL:
/*==============================================================*/
/* Database name: Zvcvolkel */
/* DBMS name: Microsoft SQL Server 2000 */
/*==============================================================*/
use master
go
drop database Zvcvolkel
go
/*==============================================================*/
/* Database: Zvcvolkel */
/*==============================================================*/
create database Zvcvolkel
go
use Zvcvolkel
go
CREATE TABLE Rol(
Rol_ID int IDENTITY(1,1),
R_Naam varchar(max),
CONSTRAINT PK_ROL PRIMARY KEY (Rol_ID)
)
go
CREATE TABLE Persoon(
Persoon_ID int IDENTITY(1,1),
Rol_ID int,
L_Voornaam varchar(max),
L_Achternaam varchar(max),
L_Geboortedat date,
L_Woonplaats varchar(max),
L_Geslacht char(1),
L_Rekeningnr varchar(max),
CONSTRAINT PK_Persoon PRIMARY KEY (Persoon_ID),
CONSTRAINT FK_Persoon_Ref_Rol FOREIGN KEY (Rol_ID) REFERENCES Rol (Rol_ID) ON UPDATE CASCADE,
CONSTRAINT CHK_L_geslacht CHECK (L_geslacht = 'M' OR L_geslacht = 'V')
)
go
CREATE TABLE Medewerker(
Medewerker_ID int IDENTITY(1,1),
Persoon_ID int,
M_Salaris decimal,
CONSTRAINT PK_Medewerker PRIMARY KEY (Medewerker_ID),
CONSTRAINT FK_Medewerker_Ref_Persoon FOREIGN KEY (Persoon_ID) REFERENCES Persoon (Persoon_ID) ON UPDATE CASCADE,
CONSTRAINT CHK_M_Salaris CHECK (M_Salaris >= 0)
)
go
CREATE TABLE Lid(
Lid_ID int IDENTITY(1,1),
Persoon_ID int,
L_GebNaam varchar(max),
L_Wachtwoord varchar(max),
CONSTRAINT PK_Lid PRIMARY KEY (Lid_ID),
CONSTRAINT FK_Lid_Ref_Persoon FOREIGN KEY (Persoon_ID) REFERENCES Persoon (Persoon_ID) ON UPDATE CASCADE,
)
go
CREATE TABLE Stat(
Status_ID int IDENTITY(1,1),
S_Naam varchar(max),
CONSTRAINT PK_Stat PRIMARY KEY (Status_ID)
)
go
CREATE TABLE Hangaar(
Hangaar_ID int IDENTITY(1,1),
H_Naam varchar(max),
H_Locatie varchar(max),
CONSTRAINT PK_Hangaar PRIMARY KEY (Hangaar_ID)
)
go
CREATE TABLE Vliegtuig(
Vliegtuig_ID int IDENTITY(1,1),
Hangaar_ID int,
Status_ID int,
V_Naam varchar(max),
V_Type varchar(max),
CONSTRAINT PK_Vliegtuig PRIMARY KEY (Vliegtuig_ID),
CONSTRAINT FK_Vliegtuig_Ref_Hangaar FOREIGN KEY (Hangaar_ID) REFERENCES Hangaar (Hangaar_ID) ON UPDATE CASCADE,
CONSTRAINT FK_Vliegtuig_Ref_Status FOREIGN KEY (Status_ID) REFERENCES Stat (Status_ID) ON UPDATE CASCADE,
)
go
CREATE TABLE Vlucht(
Vlucht_ID int IDENTITY(1,1),
Lid_ID int,
Vliegtuig_ID int,
Vl_Vertrektijd date,
VL_Eindtijd date,
VL_Type char(1),
Vl_Notitie varchar(max),
CONSTRAINT PK_Vlucht PRIMARY KEY (Vlucht_ID),
CONSTRAINT FK_Vlucht_Ref_Lid FOREIGN KEY (Lid_ID) REFERENCES Lid (Lid_ID) ON UPDATE CASCADE,
CONSTRAINT FK_Vlucht_Ref_Vliegtuig FOREIGN KEY (Vliegtuig_ID) REFERENCES Vliegtuig (Vliegtuig_ID) ON UPDATE CASCADE,
CONSTRAINT CHK_VL_type CHECK (VL_type = 'R' OR VL_type = 'L')
)
go
Next up is the creation of a DML file which is reusable:
use Zvcvolkel
go
DELETE FROM Vlucht
DELETE FROM Medewerker
DELETE FROM Lid
DELETE FROM Persoon
DELETE FROM Rol
DELETE FROM Vliegtuig
DELETE FROM Stat
DELETE FROM Hangaar
go
INSERT INTO Rol
VALUES ('Management'),
('Instructeur'),
('Technicus'),
('Lid'),
('Bartender')
INSERT INTO Stat
VALUES ('Beschikbaar'),
('Niet beschikbaar'),
('Onderhoud')
INSERT INTO Hangaar
VALUES ('Hangaar 1', 'Volkel'),
('Hangaar 2', 'Volkel')
INSERT INTO Vliegtuig
VALUES (1, 1, 'PH-143', 'ASK-21'),
(1, 3, 'PH-1643', 'LS-4'),
(2, 2, 'PH-831', 'Ka-8')
INSERT INTO Persoon
VALUES (1, 'Chef', 'de Directeur', '01-01-1986', 'Eindhoven', 'M', 'IBAN7277'),
(2, 'Piet', 'de Instructeur', '02-02-1965', 'Geldrop', 'M', 'IBAN5656'),
(3, 'Monica', 'de Technicus', '03-03-1958', 'Helmond', 'V', 'IBAN8579'),
(4, 'Linda', 'het Lid', '04-04-1976', 'Deurne', 'V', 'IBAN4665'),
(5, 'Jan', 'de Bartender', '05-05-1966', 'Venray', 'M', 'IBAN5765')
INSERT INTO Medewerker
VALUES (5, 1250.00)
INSERT INTO Lid
VALUES (1, 'Chefgeb', 'Chefww'),
(2, 'Pietgeb', 'Pietww'),
(3, 'Monicageb', 'Monicaww'),
(4, 'Lindageb', 'Lindaww')
INSERT INTO Vlucht
VALUES (2, 1, '10-04-2018 10:34:00', '10-04-2018 17:34:00', 'R', 'Geen notitie')
It works perfectly on the first run, but when I run it again it adds the rows again and it screws over the ID's because of auto increment. When I run the query again the Rol_ID become 6 - 7 - 8 - 9 - 10 instead of 1 - 2 - 3 - 4 - 5! Everytime I run the ID's increase, where it should start at 1 again because of the Delete statement on the first lines.
Does anyone know what I am missing here?
I think you can use
DBCC CHECKIDENT ('TableName', RESEED, 0)
for all tables which use IDENTITY column.