How do I declare a foreign key correctly?-CodePudding

SQL Error [42601]: ERROR: syntax error at or near "foreign"

Every instance where I put the foreign key throws an error

I tried creating a table with foreign keys and I did it exactly like on geeksforgeeks except for the names of the columns and tables

CREATE TABLE Estate
(
Main_Heating_System_ID int foreign key references Heating_System(HID), 
Address varchar(255) NOT null primary key,
EstateSize int NOT NULL,
);

CREATE TABLE Heating_System
(
HID int primary key, 
Address varchar(255) foreign key references Estate(Address),
kW int NOT NULL,
);

CREATE TABLE neighbouring
(
Estate_Address varchar(255) FOREIGN key references Estate(Address),
Neighbour_Address varchar(255) foreign key references Estate(Address),
);

CodePudding user response：

as @jarlh said, you don't use the keywords FOREIGN KEY to declare them on the same line as the column name and type during table creation as you are attempting (only if doing it via ADD CONSTRAINT). If you remove the two words FOREIGN KEY from each line, it should work

EDIT: looking at this again, I'm not sure about the design of your data model.

With these foreign keys, you can't insert into the Estate table unless the record you're inserting is referencing a HID from the Heating_System table. But if you try to insert into the Heating_System table first, you can't unless you're referencing an address which is already in the Estate table.

It looks to me like the address field on the Heating_System table might be unnecessary. If each Estate has one heating system, and the address of a heating system is just the address of its estate, you should remove the address field from the Heating_System table. You can still query the address a heating system is associated with (via its Estate) by doing e.g.

SELECT h."HID", 
       e."Address" 
  FROM Heating_System h
  JOIN Estate e ON h."HID" = e."Main_Heating_System_ID"

You could even create a view of this, but you should always avoid replicating information like that in your data model (the tables). I recommend doing a bit of learning about database normalization (1NF, 2NF, 3NF) to understand more

Also, it's conventional to avoid using upper-case characters in the names of tables and columns. Revising your names to reflect this might save you some headaches.

As another unrelated heads-up, you might want to consider using the type text instead of varchar(255) for your address fields, if there's any chance the address might be longer than 255 characters