-1

I keep getting this error in my psql database;

bikefacility=# ERROR:  syntax error at or near "c"
bikefacility-# LINE 1: c
bikefacility-#         ^
bikefacility-# bikefacility=# ALTER TABLE bicycle ADD CONSTRAINT fk_maintenance_contact_person FOREIGN KEY (maintenance_contact_person) REFERENCES maintenance(maintenance_contact_person);
ERROR:  syntax error at or near "ERROR"
LINE 1: ERROR:  syntax error at or near "c"
        ^
bikefacility=# ERROR:  column "maintenance_contact_person" referenced in foreign key constraint does not exist
bikefacility-# bikefacility=# ALTER TABLE bicycle ADD CONSTRAINT fk_rental_period FOREIGN KEY (rental_period) REFERENCES rental(rental_period);
ERROR:  syntax error at or near "ERROR"
LINE 1: ERROR:  column "maintenance_contact_person" referenced in fo...
        ^
bikefacility=# ERROR:  column "rental_period" referenced in foreign key constraint does not exist
bikefacility-# bikefacility=# ALTER TABLE bicycle ADD CONSTRAINT fk_terminal_id FOREIGN KEY (terminal_id) REFERENCES terminal(terminal_id);
ERROR:  syntax error at or near "ERROR"
LINE 1: ERROR:  column "rental_period" referenced in foreign key con...
        ^
bikefacility=# ERROR:  column "terminal_id" referenced in foreign key constraint does not exist

Here is the code I'm using

CREATE TABLE member (
  member_id INTEGER PRIMARY KEY,
  member_fname VARCHAR(15) NOT NULL,
  member_lname VARCHAR(15) NOT NULL,
  member_status VARCHAR(15) NOT NULL,
  member_address VARCHAR(10) NOT NULL,
  member_email VARCHAR(30) NOT NULL
);

CREATE TABLE bicycle (
  bicycle_id INTEGER PRIMARY KEY,
  bicycle_brand VARCHAR(25) NOT NULL,
  bicycle_model VARCHAR(25) NOT NULL,
  bicycle_colour VARCHAR(15) NOT NULL,
  bicycle_type VARCHAR(20) NOT NULL,
  bicycle_size VARCHAR(10) NOT NULL,
  bicycle_availability VARCHAR(20) NOT NULL

);

ALTER TABLE bicycle ADD CONSTRAINT fk_bicycle_pickup_date FOREIGN KEY (bicycle_pickup_date) REFERENCES rental(bicycle_pickup_date);
ALTER TABLE bicycle ADD CONSTRAINT fk_maintenance_contact_person FOREIGN KEY (maintenance_contact_person) REFERENCES maintenance(maintenance_contact_person);
ALTER TABLE bicycle ADD CONSTRAINT fk_terminal_id FOREIGN KEY (terminal_id) REFERENCES terminal(terminal_id);
ALTER TABLE bicycle ADD CONSTRAINT fk_rental_period FOREIGN KEY (rental_period) REFERENCES rental(rental_period);

CREATE TABLE company (
  company_id INTEGER PRIMARY KEY,
  company_name VARCHAR(20) NOT NULL,
  company_contact VARCHAR(50) NOT NULL,
  company_address VARCHAR(50) NOT NULL
);

CREATE TABLE sponsor (
  sponsor_id INTEGER PRIMARY KEY,
  sponsor_name VARCHAR(15) NOT NULL,
  sponsor_contact VARCHAR(30) NOT NULL,
  sponsor_period DATE NOT NULL,
  sponsor_address VARCHAR(50) NOT NULL,
  sponsor_fee DECIMAL (6, 2) NOT NULL
);

ALTER TABLE sponsor ADD CONSTRAINT fk_company_name FOREIGN KEY (company_name) REFERENCES company(company_name);
ALTER TABLE sponsor ADD CONSTRAINT fk_bicycle_availability FOREIGN KEY (bicycle_availability) REFERENCES bicycle(bicycle_availability);

CREATE TABLE terminal (
  terminal_id INTEGER PRIMARY KEY,
  terminal_address VARCHAR(50) NOT NULL,
  terminal_minstorage VARCHAR(50) NOT NULL,
  terminal_maxstorage VARCHAR(50) NOT NULL
);

ALTER TABLE terminal ADD CONSTRAINT fk2_maintenance_contact_person FOREIGN KEY (maintenance_contact_person) REFERENCES maintenance(maintenance_contact_person);
ALTER TABLE terminal ADD CONSTRAINT fk2_bicycle_availability FOREIGN KEY (bicycle_availability) REFERENCES bicycle(bicycle_availability);
ALTER TABLE terminal ADD CONSTRAINT fk_bicycle_id FOREIGN KEY (bicycle_id) REFERENCES bicycle(bicycle_id);

CREATE TABLE rental (
  rental_no INTEGER PRIMARY KEY,
  rental_period DATE NOT NULL,
  bicycle_pickup_date DATE NOT NULL
);

ALTER TABLE rental ADD CONSTRAINT fk2_bicycle_id FOREIGN KEY (bicycle_id) REFERENCES bicycle(bicycle_id);
ALTER TABLE rental ADD CONSTRAINT fk2_terminal_id FOREIGN KEY (terminal_id) REFERENCES terminal(terminal_id);


CREATE TABLE bill (
  bill_id INTEGER PRIMARY KEY,
  bill_date DATE NOT NULL,
  bill_total_amount DECIMAL(6, 2) NOT NULL,
  payment_no INTEGER NOT NULL,
  payment_date DATE NOT NULL,
  payment_method VARCHAR(15) NOT NULL
);

ALTER TABLE bill ADD CONSTRAINT fk3_bicycle_pickup_date FOREIGN KEY (bicycle_pickup_date) REFERENCES rental(bicycle_pickup_date);


CREATE TABLE maintenance (
  maintenance_id INTEGER PRIMARY KEY,
  maintenance_contact_person VARCHAR(15) NOT NULL,
  maintenance_phone_number INTEGER NOT NULL,
  maintenance_fee DECIMAL(6, 2) NOT NULL
);

ALTER TABLE maintenance ADD CONSTRAINT fk3_bicycle_id FOREIGN KEY (bicycle_id) REFERENCES bicycle(bicycle_id);
ALTER TABLE maintenance ADD CONSTRAINT  fk3_terminal_id FOREIGN KEY (terminal_id) REFERENCES terminal(terminal_id);



CREATE TABLE visitor (
  visitor_id INTEGER PRIMARY KEY,
  daypass_no INTEGER NOT NULL,
  daypass_date DATE NOT NULL,
  visitor_phone_number INTEGER NOT NULL,  
  visitor_fname VARCHAR(15) NOT NULL,
  visitor_email VARCHAR(40) NOT NULL,
  visitor_address VARCHAR(80) NOT NULL
);

ALTER TABLE visitor ADD CONSTRAINT fk4_bicycle_id FOREIGN KEY (bicycle_id) REFERENCES bicycle(bicycle_id);
ALTER TABLE visitor ADD CONSTRAINT fk_bill_id FOREIGN KEY (bill_id) REFERENCES bill(bill_id);

CREATE TABLE invoice (
  invoice_id INTEGER PRIMARY KEY
);

ALTER TABLE invoice ADD CONSTRAINT fk_customer_type FOREIGN KEY (customer_type) REFERENCES customer(customer_type);
ALTER TABLE invoice ADD CONSTRAINT fk_rental_no FOREIGN KEY (rental_no) REFERENCES rental(rental_no);
ALTER TABLE invoice ADD CONSTRAINT fk2_rental_period FOREIGN KEY (rental_period) REFERENCES rental(rental_period);
ALTER TABLE invoice ADD CONSTRAINT fk5_bicycle_id FOREIGN KEY (bicycle_id) REFERENCES bicycle(bicycle_id);
ALTER TABLE invoice ADD CONSTRAINT fk_bicycle_brand FOREIGN KEY (bicycle_brand) REFERENCES bicycle(bicycle_brand);
ALTER TABLE invoice ADD CONSTRAINT fk_bicycle_model FOREIGN KEY (bicycle_model) REFERENCES bicycle(bicycle_model);
ALTER TABLE invoice ADD CONSTRAINT fk_bicycle_size FOREIGN KEY (bicycle_size) REFERENCES bicycle(bicycle_size);
ALTER TABLE invoice ADD CONSTRAINT fk2_bill_id FOREIGN KEY (bill_id) REFERENCES bill(bill_id);
ALTER TABLE invoice ADD CONSTRAINT fk_bill_date FOREIGN KEY (bill_date) REFERENCES bill(bill_date);
ALTER TABLE invoice ADD CONSTRAINT fk_bill_total_amount FOREIGN KEY (bill_total_amount) REFERENCES bill(bill_total_amount);


CREATE TABLE customer (
customer_id INTEGER PRIMARY KEY
);
ALTER TABLE customer ADD CONSTRAINT fk_member_id FOREIGN KEY (member_id) REFERENCES member(member_id);
ALTER TABLE customer ADD CONSTRAINT fk_visitor_id FOREIGN KEY (visitor_id) REFERENCES visitor(visitor_id);
ALTER TABLE customer ADD CONSTRAINT fk_member_status FOREIGN KEY (member_status) REFERENCES member(member_status);
ALTER TABLE customer ADD CONSTRAINT fk3_bill_id FOREIGN KEY (bill_id) REFERENCES bill(bill_id);
ALTER TABLE customer ADD CONSTRAINT fk6_bicycle_id FOREIGN KEY (bicycle_id) REFERENCES bicycle(bicycle_id);

I have a feeling the error " foreign key does not exist " is going to show up a lot. Could someone help me out, please? What's wrong with my code? Thank you in advance!

  • May be you should look at the complete error messages. You are trying to reference non-existent tables in your constraints: rentals is created after you are trying to use it in a constraint. – mustaccio Jan 11 at 17:06
  • Also, please include your RDBMS version in the tags for the question - answers can depend on the version! p.s. welcome to the forum! – Vérace Jan 12 at 7:38
  • It looks like you're copying and pasting output from one psql session into another. – Colin 't Hart Jan 13 at 10:05
1

You have misunderstood the exact nature of FOREIGN KEYs.

The field specified as a FOREIGN KEY has to exist in the table you are altering!

I've put a correction of one issue into the fiddle here and pointed out some of your errors. I moved all of the table creation steps above the foreign key creation steps because that, in itself, can be a source of confusion (see the fiddle here).

--
-- First Error - first of your foreign keys defined
--
ALTER TABLE bicycle ADD CONSTRAINT fk_bicycle_pickup_date FOREIGN KEY (bicycle_pickup_date) REFERENCES rental(bicycle_pickup_date);

gives the error:

ERROR:  column "bicycle_pickup_date" referenced in foreign key constraint does not exist

It's telling you the problem. Here is your definition of the bicycle table:

CREATE TABLE bicycle (
  bicycle_id INTEGER PRIMARY KEY,
  bicycle_brand VARCHAR(25) NOT NULL,
  bicycle_model VARCHAR(25) NOT NULL,
  bicycle_colour VARCHAR(15) NOT NULL,
  bicycle_type VARCHAR(20) NOT NULL,
  bicycle_size VARCHAR(10) NOT NULL,
  bicycle_availability VARCHAR(20) NOT NULL
);

So, you can see that there no field called bicycle_pickup_date - the field is, in fact, where it should be, in the rental table.

To demonstrate how these FOREIGN KEYs should be defined I added a bicycle_id field to the maintenance table as follows (see the fiddle):

CREATE TABLE maintenance (
  maintenance_id INTEGER PRIMARY KEY,
  bicycle_id INTEGER NOT NULL,
  maintenance_contact_person VARCHAR(15) NOT NULL,
  maintenance_phone_number INTEGER NOT NULL,
  maintenance_fee DECIMAL(6, 2) NOT NULL
);

As an aside, it also seems like a good idea from a design point of view, since it would be beneficial to be able to check if any bikes are particularly troublesome/expensive to maintain!

So, now because the bicycle_id field exists in the maintenance table, we can define our FOREIGN KEY as follows:

--
-- First FOREIGN KEY defined without error
--
ALTER TABLE maintenance ADD CONSTRAINT maint_bike_fk FOREIGN KEY (bicycle_id)
  REFERENCES bicycle (bicycle_id);

Just as an aside, I prefer the following method of defining FOREIGN KEYs - keep as much of the DDL as possible within the table definition (again, see fiddle)!

I first drop the table:

DROP TABLE maintenance;

And then redefine it as follows:

CREATE TABLE maintenance (
  maintenance_id INTEGER PRIMARY KEY,
  bicycle_id INTEGER NOT NULL,
  maintenance_contact_person VARCHAR(15) NOT NULL,
  maintenance_phone_number INTEGER NOT NULL,
  maintenance_fee DECIMAL(6, 2) NOT NULL,
  CONSTRAINT maint_bike_fk FOREIGN KEY (bicycle_id)
    REFERENCES bicycle (bicycle_id)
);

I find the logic easier to follow this way. You should now be able to create appropriate keys and indexes referring to fields which actually exist - which is the only way you can do it! If you have any other issues with creating them, get back to us here.

Not the answer you're looking for? Browse other questions tagged or ask your own question.