Cfrgtkky

I'm having trouble understanding how to setup my foreign key reference. I'm trying to make a reference between tables Lägenheter and Arbetslista so that one row in Lägenheter belong to many rows in Arbetslista.

I have created tables likes this:

CREATE TABLE Deltagare (

Deltagarnr int PRIMARY KEY,

Namn varchar(30),

Typ varchar(30)

);



CREATE TABLE Uppgifter (

Uppgift varchar(30) PRIMARY KEY,

Typ varchar(30),

Pris money

);



CREATE TABLE Arbetslista (

Deltagarnr int not null,

Datum date not null,

Uppgift varchar(30) not null, 

Lägenhetsnr int not null,



PRIMARY KEY (Deltagarnr, Datum, Uppgift),

FOREIGN KEY (Deltagarnr) REFERENCES Deltagare(Deltagarnr),

FOREIGN KEY (Uppgift) REFERENCES Uppgifter(Uppgift)

);

Then I import some data from a .mdb file to all three tables.
After that I add another table:

CREATE TABLE Lägenheter(

lägenhetsnummer int PRIMARY KEY,

gatuadress varchar(30),

kvadrater int

);

It all works fine until this last step:

ALTER TABLE Arbetslista

ADD FOREIGN KEY (Lägenhetsnr) REFERENCES Lägenheter(lägenhetsnummer)

;

Here I get this error message:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__Arbetslis__Lägen__403A8C7D". The conflict occurred in database "Hushåll", table "dbo.Lägenheter", column 'lägenhetsnummer'.

After that I have also tried to delete all data from Arbetslista and then execute the mentioned ALTER TABLE again. It works, but then I get an error trying to import the data again:

Hresult: 0x80004005 Description: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Arbetslis__Lägen__412EB0B6". The conflict occurred in database "Hushåll", table "dbo.Lägenheter", column 'lägenhetsnummer'.".
(SQL Server Import and Export Wizard)

Why is this not possible when the other foreign keys seems to work fine?

There are rows in Arbetslista table, for which the value of Lägenhetsnr doesn't exists in field lägenhetsnummer of table Lägenheter. You must clean the existing data to be able to create the foreign key like that.

Check your data by executing this script:

select * from Arbetslista a where not exists (select * from Lägenheter l where l.lägenhetsnummer = a.Lägenhetsnr)

After importing the data into Arbetslista, the column Lägenhetsnr was initialize to some value ( say XYZ).
later, when trying to add the FOREIGN KEY to Arbetslista, SQL try to meet this, however it cannot find row with primary key lägenhetsnummer = XYZ and the statement would be terminated.
I think the best is to create the 4 table first then clean the data and try to import it.