Error with foreign key

1

Hi,
I create the database with:

CREATE DATABASE Visbyflyg;

CREATE TABLE Flygplan
(
Namn VarChar(255) NOT NULL,
Beteckning VarChar(255),
Platser Integer,
primary key (Namn)
);

CREATE TABLE Personal
(
Anstnummer VarChar(50) NOT NULL,
Fornamn VarChar(255),
Efternamn VarChar(255),
Flygtimmar Integer,
Befattning VarChar(30),
primary key (Anstnummer)
);



CREATE TABLE Flight
(
Flightnummer VarChar(50) NOT NULL,
Namn VarChar(255),
Resertill VarChar(255),
Reserfran VarChar(255),
Avgdatum Date NOT NULL,
Avgtid Time,
Ankdatum Date,
Anktid Time,
CONSTRAINT FlightPK
primary key (Flightnummer,Avgdatum),
CONSTRAINT FlightFlygplanFK
foreign key (Namn) references Flygplan(Namn)
ON DELETE SET NULL ON UPDATE CASCADE,
);


CREATE TABLE Biljetttyp
(
Biljettnr Integer NOT NULL IDENTITY,
Flightnummer VarChar(50),
Biljettyp VarChar(50),
Pris Integer,
primary key (Biljettnr)
);


CREATE TABLE Passagerare
(
Nr Integer NOT NULL IDENTITY, 
Fornamn VarChar(255),
Efternamn VarChar(255),
Typ VarChar(30),
Passning Bit,
Platsnr Integer,
Biljettyp VarChar(255),
Flightnummer VarChar(50),
Avgdatum Date
CONSTRAINT PassagerarePK
primary key (Nr),
CONSTRAINT PassagerareFlightFK
foreign key (Flightnummer,Avgdatum) references Flight(Flightnummer,Avgdatum)
ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Kompetens
(
Kompetensnr Integer NOT NULL IDENTITY,
Anstnummer VarChar(50),
Namn VarChar(255)
CONSTRAINT KompetensPK
primary key (Kompetensnr),
CONSTRAINT KompetensPersonalFK
foreign key (Anstnummer) references Personal(Anstnummer)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT KompetensFlygplanFK
foreign key (Namn) references Flygplan(Namn)
ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE Bemanning
(
Bemanningsnr Integer NOT NULL IDENTITY,
Flightnummer VarChar(50),
Avgdatum Date,
Position VarChar(50),
Anstnummer VarChar(50),
CONSTRAINT BemanningPK
primary key (Bemanningsnr),
CONSTRAINT BemanningFlightFK
foreign key (Flightnummer,Avgdatum) references Flight(Flightnummer,Avgdatum)
ON DELETE SET NULL ON UPDATE CASCADE
);

and insert values with:

INSERT INTO Flygplan (Namn,Beteckning,Platser) 
VALUES
('Zeus','Boeing747',250),
('Hera','AirbusA320',220),
('Poseidon','Boeing737',110),
('Ares','MD-81',141),
('Afrodite','Fokker100',250),
('Pallas-Athena','Avro RJ85',82),
('Hades','Avro RJ85',82);

INSERT INTO Personal (Anstnummer,Fornamn,Efternamn,Flygtimmar,Befattning)
VALUES
('N1','Neil','Nelson','2900','Pilot'),
('L1','Larry','Hegetay','3400','Pilot'),
('G1','Gork','Getaway','2670','Pilot'),
('A2','Amanda','Jonsson','3100','Pilot'),
('N2','Neil','Boston','2125','Pilot'),
('M1','Mary','Alstedt','1000','Pilot'),
('J2','Jouko','Uusitalo','1200','Pilot'),
('A4','Abraham','Omar','4000','Pilot'),
('V2','Vassil','Papadimitr','1200','Pilot'),
('U2','Ugi','Abbas','4000','Pilot'),
('M2','Michelle','Easter','300','Pilot'),
('M3','Mohammed','Ebb','400','Pilot'),
('J1','Jet','Rågen','740','Flygvärdinna'),
('B1','Bibbi','Nelson','80','Flygvärdinna'),
('V1','Vera','Hensel','390','Flygvärdinna'),
('C1','Charlie','Channing','495','Flygvärdinna'),
('Z1','Zebra','Wollter','100','Flygvärdinna'),
('A1','Agnes','Ungert','1200','Flygvärdinna'),
('T1','Tennessee','Magden','910','Flygvärdinna'),
('E1','Eija','Merinen','800','Flygvärdinna'),
('U1','Ullrika','Wales','200','Flygvärdinna'),
('A3','Alvar','Ihanen','800','Flygvärdinna'),
('L2','Lara','Esten','500','Flygvärdinna'),
('T2','Terese','Norman','600','Flygvärdinna'),
('T3','Torstein','Mattsen','400','Flygvärdinna'),
('V3','Vanja','Ivanova','1700','Flygvärdinna'),
('E2','Emil','Urban','1500','Flygvärdinna'),
('T4','Tova','Svensson','200','Flygvärdinna'),
('A5','Amanda','Bunkert','330','Flygvärdinna');

INSERT INTO Flight (Flightnummer,Namn,Resertill,Reserfran,Avgdatum,Avgtid,Ankdatum,Anktid)
VALUES
('LK202','Pallas-Athena','Umeå','Stockholm','2013-08-01','10:55:00','2013-08-01','11:55:00'),
('SK405','Afrodite','Helsingfors','Göteborg','2013-08-01','20:00:00','2013-08-01','22:05:00'),
('VR111','Zeus','Rio de Janeiro','Zurich','2013-08-01','17:40:00','2013-08-02','11:50:00'),
('LF550','Ares','Frankfurtam','Köpenhamn','2013-08-01','08:50:00','2013-08-01','09:55:00'),
('BA801','Poseidon','London','Paris','2013-08-01','18:15:00','2013-08-01','18:55:00'),
('AF330','Hera','London','Paris','2013-08-01','13:25:00','2013-08-01','14:10:00'),
('LF550','Zeus','Frankfurtam','Köpenhamn','2013-08-02','08:50:00', '2013-08-02','09:55:00');

INSERT INTO Bemanning (Flightnummer,Avgdatum,Position,Anstnummer)
VALUES
('LK202','2013-08-01','Fpilot','G1'),
('LK202','2013-08-01','Apilot','N2'),
('LK202','2013-08-01','Flv1','V1'),
('LK202','2013-08-01','Flv2','B1'),
('LF550','2013-08-01','Fpilot','L1'),
('LF550','2013-08-01','Apilot','A2'),
('LF550','2013-08-01','Flv1','Z1'),
('LF550','2013-08-01','Flv2','U1'),
('SK405','2013-08-01','Fpilot','M1'),
('SK405','2013-08-01','Apilot','J2'),
('SK405','2013-08-01','Flv1','E1'),
('SK405','2013-08-01','FLV2','A3'),
('VR111','2013-08-01','Fpilot','N1'),
('VR111','2013-08-01','Apilot','A4'),
('VR111','2013-08-01','Flv1','J1'),
('VR111','2013-08-01','Flv2','A1'),
('VR111','2013-08-01','Flv3','T1'),
('VR111','2013-08-01','Flv4','L2'),
('BA801','2013-08-01','Fpilot','V2'),
('BA801','2013-08-01','Apilot','U2'),
('BA801','2013-08-01','Flv1','J1'),
('BA801','2013-08-01','Flv2','A1'),
('BA801','2013-08-01','Flv3','T3'),
('AF330','2013-08-01','Fpilot','M2'),
('AF330','2013-08-01','Apilot','M3'),
('AF330','2013-08-01','Flv1','V3'),
('AF330','2013-08-01','Flv2','E2'),
('LF550','2013-08-02','Fpilot','G1'),
('LF550','2013-08-02','Apilot','A4'),
('LF550','2013-08-02','Flv1','T1'),
('LF550','2013-08-02','Flv2','J1');


INSERT INTO Biljetttyp (Flightnummer,Biljettyp,Pris)
VALUES
('LK202','Återbetalningsbar',1000),
('LK202','Ombokningsbar',500),
('LK202','Röd',400),
('SK405','Återbetalningsbar',1800),
('SK405','Ombokningsbar',900),
('SK405','Röd',750),
('VR111','Återbetalningsbar',4800),
('VR111','Ombokningsbar',2400),
('VR111','Röd',2000),
('LF550','Återbetalningsbar',2200),
('LF550','Ombokningsbar',1100),
('LF550','Röd',800),
('BA801','Återbetalningsbar',1900),
('BA801','Ombokningsbar',950),
('BA801','Röd',700),
('AF330','Återbetalningsbar',1600),
('AF330','Ombokningsbar',800),
('AF330','Röd',600);

INSERT INTO Passagerare (Fornamn,Efternamn,Typ,Passning,Platsnr,Biljettyp,Flightnummer,Avgdatum)
VALUES
('Marty','Feldman','Man',0,NULL,'Återbetalningsbar','LF550','2013-08-01'),
('Jens','Christiansen','Man',1,NULL,'Ombokningsbar','LF550','2013-08-01'),
('Martii','Turunen','Barn',0,NULL,'Ombokningsbar','SK405','2013-08-01'),
('Heino','Turunen','Man',0,NULL,'Ombokningsbar','SK405','2013-08-01'),
('Regine','FairChild','Kvinna',1,NULL,'Återbetalningsbar','VR111','2013-08-01'),
('Tina','Gottlieb','Barn',1,NULL,'Ombokningsbar','BA801','2013-08-01'),
('Måns','Uman','Man',0,NULL,'Återbetalningsbar','LK202','2013-08-01'),
('Collette','Ronnan','Kvinna',0,NULL,'Återbetalningsbar','AF330','2013-08-01'),
('Mary','Ottosson','Kvinna',0,NULL,'Röd','LK202','2013-08-01'),
('Panu','Onninen','Barn',1,NULL,'Återbetalningsbar','SK405','2013-08-01'),
('Gary','Eastman','Man',0,NULL,'Återbetalningsbar','LF550','2013-08-01'),
('Lorry','Arguent','Kvinna',1,NULL,'Röd','','2013-08-01'),
('Jesper','Tuborg','Man',1,NULL,'Ombokningsbar','BA801','2013-08-01'),
('Toyota','Corolla','Kvinna',0,NULL,'Återbetalningsbar','LF550','2013-08-02');


INSERT INTO Kompetens (Anstnummer,Namn)
VALUES
('N1','Boeing747'),
('N1','Avro RJ85'),
('L1','Boeing737'),
('L1','Airbus A320'),
('L1','MD-81'),
('G1','Boeing747'),
('G1','MD-81'),
('G1','Avro RJ85'),
('A2','Boeing737'),
('A2','Airbus A320'),
('A2','MD-81'),
('N2','Airbus A320'),
('N2','Boeing 747'),
('N2','Avro RJ85'),
('M1','Fokker 100'),
('J2','Fokker 100'),
('A4','Boeing 747'),
('V2','Boeing 737'),
('V2','Boeing 747'),
('U2','Boeing 737'),
('U2','Airbus A320'),
('M2','Airbus A320'),
('M3','Airbus A320');

But get the errors

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "KompetensFlygplanFK". The conflict occurred in database "master", table "dbo.Flygplan", column 'Namn'.
The statement has been terminated.

and

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "PassagerareFlightFK". The conflict occurred in database "master", table "dbo.Flight".
The statement has been terminated.

Can I get rid of the errors without dropping my foreign keys?

2

That is the reason we have PK and FK relationship,to maintain referential integrity.
Unless you have the record in the parent table you cannot have it in the child table.

1 Like
3

Along with ahmeds08 comment, if I am reading the error correctly you may have inadvertently created your tables and objects in the master database. Your creation script creates a database but does not seem to have any USE statement to then have the tables created in the new database.

1 Like
4

But I have the same columns the parent table and the parent table is filled with records first so as I see it I have no records in the child tables FK that is not in the parent tables PK, right? So what can I change, not to get the error?

5

Hi, thanks for your reply. What do you mean?

The tables are created correctly but it is when I try to fill the last two tables with values that I get the error.

6

It means,as per the error message it shows that the tables are created in the master database.User objects should always be created in the user databases.

1 Like
7

Ok, putting aside that your code put the tables in the master database the reason you are getting the foreign key conflicts as follows:

The conflict with the flight table is because you have a missing value in the "Lorry" row for the Passagerare insert statement.

The conflict with the flygplan table is because you are using the values from the Beteckning column and not the namn column in your kompetens insert statement.

I hope this helps.

1 Like
8

Yes, that helped a lot. Thanks!

9

Ah, ok. Thanks.