Foreign key 'Bestellung' references invalid column 'Bestellung' in referencing table 'Kunde'

Question

My problem arises when I want to link two existing tables.

I am very new to the field and have only been dealing with databases for a few days.

It would be great if someone could help me and tell me what I did wrong.

I use "microsoft / mssql-server-linux: latest" in a Docker and "Azure Data Studio" as editor.

CREATE TABLE Bestellung
(
BestlNr INTEGER NOT NULL,
Datum INTEGER,
Gesamtpreis INTEGER,
PRIMARY KEY (BestlNr)
) ;


CREATE TABLE Kunde
(
KdNr INTEGER NOT NULL,
Vorname VARCHAR,
Nachnahme VARCHAR,
Geburtsdatum INTEGER,
Strasse VARCHAR,
Hausnummer VARCHAR,
Ort VARCHAR,
PLZ INTEGER,
Passwort VARCHAR(50),
PRIMARY KEY (KdNr),
) ;


ALTER TABLE Bestellung
   ADD CONSTRAINT Bestellung, FOREIGN KEY (KdNr),
   REFERENCES Kunde (KdNr),
      ON DELETE CASCADE
      ON UPDATE CASCADE;

Answer 1

first of all you need to add a column for your Foreign key in Bestellung table

so:

CREATE TABLE Bestellung
(
BestlNr INTEGER NOT NULL,
Datum INTEGER,
Gesamtpreis INTEGER,
FK_KdNr INTEGER,
PRIMARY KEY (BestlNr)
) ;

then make the relationship

ALTER TABLE Bestellung
   ADD CONSTRAINT Bestellung FOREIGN KEY (FK_KdNr)
   REFERENCES Kunde (KdNr)
      ON DELETE CASCADE
      ON UPDATE CASCADE;

Answer 2

Firstly, declaring your columns as a varchar(1) is somewhat pointless, if you only need one character, then use a char(1).

Next, your ALTER TABLE statement has a bunch of commas it should not, and one of the CREATE statement has a single extra one, so we'll remove those.

Next, to create a FOREIGN KEY you need the column to exist in both tables; Bestellung doesn't have a column KdNr and hence why it's failing.

Finally, you try to give your CONSTRAINT the same name as your table; you can't do that. Object names, within a database, need to be unique.

After changing these bits, that ends up with the following:

CREATE TABLE dbo.Bestellung (BestlNr int NOT NULL,
                             Datum int,
                             Gesamtpreis int,
                             KdNr int
                                 PRIMARY KEY (BestlNr));


CREATE TABLE dbo.Kunde (KdNr int NOT NULL,
                        Vorname char(1),
                        Nachnahme char(1),
                        Geburtsdatum int,
                        Strasse char(1),
                        Hausnummer char(1),
                        Ort char(1),
                        PLZ int,
                        Passwort varchar(50),
                            PRIMARY KEY (KdNr));
GO

ALTER TABLE Bestellung
ADD CONSTRAINT Kunde_KdNr_FK
    FOREIGN KEY (KdNr)
    REFERENCES Kunde (KdNr) ON DELETE CASCADE ON UPDATE CASCADE;