-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.

|improve this answer|||||

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