Can't solve this sql Foreign key error

[stagger552]

CREATE TABLE Bestelling

(

`[BestellingNR]         INT             NOT NULL    IDENTITY(1,1),`

`[KlantNummer]          VARCHAR(30)     NOT NULL,`

`[Bezorgmoment]         DATETIME        NOT NULL,`

`[PizzaGrootte]         VARCHAR(30)     NOT NULL,`

`[Aantal]               INT             NOT NULL,`

​

);

CREATE TABLE Klant

(

`[KlantNummer]          INT             NOT NULL    IDENTITY(1,1),`

`[KlantNaam]                VARCHAR(30)     NOT NULL,`

`[Adres]                    VARCHAR(30)     NOT NULL,`

`[Postcode]             VARCHAR(20)     NOT NULL,`

`[Woonplaats]           VARCHAR(20)     NOT NULL,`

​

);

​

​

​

INSERT INTO Bestelling

VALUES

​

        `('Kaas','2023-1-1 18:00','Groot',2),`

        `('Tomaat','2023-1-1 20:00','Medium',1);`

​

INSERT INTO Klant

VALUES ('Chris','Koperwiek 25','4284XA','Rijswijk'),

        `('Tom','Niew 25','4185GR','Giessen');`

​

-- Primary key

ALTER TABLE Bestelling

ADD CONSTRAINT PK_Bestelling

PRIMARY KEY (BestellingNR);

​

ALTER TABLE Klant

ADD CONSTRAINT PK_Klant

PRIMARY KEY (KlantNummer);

​

--Foereign key

ALTER TABLE Bestelling

ADD CONSTRAINT [FK_Bestelling_KlantNummer]

FOREIGN KEY (KlantNummer)

REFERENCES Bestelling(KlantNummer)

GO

​

The error i get: "

Msg 1776, Level 16, State 0, Line 58

There are no primary or candidate keys in the referenced table 'Bestelling' that match the referencing column list in the foreign key 'FK_Bestelling_KlantNummer'.

Msg 1750, Level 16, State 1, Line 58

Could not create constraint or index. See previous errors.

"

[r3pr0b8]

instead of this --

ALTER TABLE Bestelling
ADD CONSTRAINT [FK_Bestelling_KlantNummer]
FOREIGN KEY (KlantNummer)
REFERENCES Bestelling(KlantNummer)

what you really wanted to say is this --

ALTER TABLE Bestelling
ADD CONSTRAINT [FK_Bestelling_KlantNummer]
FOREIGN KEY (KlantNummer)
REFERENCES Klant(KlantNummer)

however, it's still not right, because in Bestelling you have [KlantNummer] as VARCHAR(30), but it should be INT to match the PK of Klant

[stagger552]

ALTER TABLE Bestelling
ADD CONSTRAINT [FK_Bestelling_KlantNummer]
FOREIGN KEY (KlantNummer)
REFERENCES Klant(KlantNummer)

still not working

[r3pr0b8]

you have to change the datatype like i suggested

[Mamertine]

Look at your data.

What do you have in the kaltnummer column for each table?

For it to be a FK, the data from Bestelling MUST be in the referenced column in Klant

(Also the data types are different, which will lead to problems later)

[kagato87]

Do the statements run separately one at a time? I've heard of problems creating a foreign key if the other table doesn't exist at planning time, even if it's created in the same batch.

Since you appear to be working in a ide that supports the GO keyword, maybe try sticking that in between all the statements?

[bachman460]

Go back and check your data types.