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;

This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. See [ask] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. PS You have a syntax error. Read the grammar & manual. Show that constituent subexpressions are OK. — philipxy, Oct 10 '20 at 04:15
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code in table format. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Oct 10 '20 at 04:18

score 0 · Answer 1 · answered Sep 29 '20 at 15:19

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;

score 0 · Answer 2 · answered Sep 29 '20 at 15:29

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;

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

2 Answers2