SQL Foreign Keys

Ok, now I can't get my Foreign keys to work >_< I've tried compiling my code and I get an error message saying "Foreign key 'received_fkey' references invalid column 'product_order_ID' in referencing table 'customer'". Below is my code for creating my tables. Could I maybe have them in the wrong order or something?


create table customer
(customer_ID varchar(7),
customer_name varchar(30),
customer_street varchar(30),
customer_town varchar(30),
customer_county varchar(30),
customer_postcode varchar(8),
customer_telephone varchar(12),
customer_email varchar(30),
constraint customerkey primary key (customer_ID),
constraint cust_ID check ((customer_ID like '[0-9][0-9][0-9][-][0-9][0-9][0-9]')),
constraint cust_postcode check ((customer_postcode like '[A-Z][A-Z][0-9][0-9][ ][0-9][A-Z][A-Z]') or (customer_postcode like '[A-Z][A-Z][0-9][ ][ ][0-9][A-Z][A-Z]')),
constraint cust_phone check ((customer_telephone like '[0-9][0-9][0-9][0-9][0-9][ ][0-9][0-9][0-9][0-9][0-9][0-9]')),
constraint received_fkey foreign key (product_order_ID) references product_order
on delete cascade
on update cascade);

create table product_order
(product_order_ID char(7) not null,
constraint product_orderkey primary key (product_order_ID),
constraint prodorder_ID check ((product_order_ID like '[0-9][0-9][0-9][-][0-9][0-9][0-9]')),
constraint sentby_fkey foreign key (warehouse_ID) references warehouse
on update cascade
on delete cascade);

create table customer_order
(customer_order_ID char(7) not null,
order_date char(10) not null,
delivery_date char(10) not null,
constraint customer_orderkey primary key (customer_order_ID),
constraint custorder_ID check ((customer_order_ID like '[0-9][0-9][0-9][-][0-9][0-9][0-9]')),
constraint orddate check ((order_date like '[1-9[0-9][/][0-1][1-9][/][1-9][0-9][0-9][0-9]') or (order_date like '[1-9][/][0-1][1-9][/][1-9][0-9][0-9][0-9]')),
constraint delivdate check ((delivery_date like '[1-9[0-9][/][0-1][1-9][/][1-9][0-9][0-9][0-9]') or (delivery_date like '[1-9][/][0-1][1-9][/][1-9][0-9][0-9][0-9]')),
constraint makes_fkey foreign key (customer_ID) references customer
on update cascade
on delete cascade);

create table product
(product_ID char(7) not null,
product_description char not null,
constraint productkey primary key (product_ID),
constraint prod_ID check ((product_ID like '[0-9][0-9][0-9][-][0-9][0-9][0-9]')),
constraint orders_fkey foreign key (customer_order_ID) references customer_order
on update cascade
on delete cascade);

create table supplier_order
(supplier_order_ID char(7) not null,
constraint supplier_orderkey primary key (supplier_order_ID),
constraint supporder_ID check ((supplier_order_ID like '[0-9][0-9][0-9][-][0-9][0-9][0-9]')),
constraint creates_fkey foreign key (product_ID) references product
on update cascade
on delete cascade);

create table supplier
(supplier_ID char(7) not null,
supplier_name char(30) not null,
supplier_street char(30) not null,
supplier_town char(30) not null,
supplier_county char(30) not null,
supplier_postcode char(8) not null,
supplier_telephone int not null,
supplier_email char(30) not null,
VAT_code int not null,
supplier_contact char(30) not null,
constraint supplierkey primary key (supplier_ID),
constraint supp_ID check ((supplier_ID like '[0-9][0-9][0-9][-][0-9][0-9][0-9]')),
constraint supp_postcode check ((supplier_postcode like '[A-Z][A-Z][0-9][0-9][ ][0-9][A-Z][A-Z]') or (supplier_postcode like '[A-Z][A-Z][0-9][ ][ ][0-9][A-Z][A-Z]')),
constraint supp_phone check ((supplier_telephone like '[0-9][0-9][0-9][0-9][0-9][ ][0-9][0-9][0-9][0-9][0-9][0-9]')),
constraint sentto_fkey foreign key (supplier_order_ID) references supplier_order
on update cascade
on delete cascade);

create table warehouse
(warehouse_ID char(7) not null,
product_stock int not null,
warehouse_name char(30) not null,
warehouse_street char(30) not null,
warehouse_town char(30) not null,
warehouse_county char(30) not null,
warehouse_postcode char(8) not null,
warehouse_telephone int not null,
warehouse_email char(30) not null,
constraint warhousekey primary key (warehouse_ID),
constraint ware_ID check ((warehouse_ID like '[0-9][0-9][0-9][-][0-9][0-9][0-9]')),
constraint ware_postcode check ((warehouse_postcode like '[A-Z][A-Z][0-9][0-9][ ][0-9][A-Z][A-Z]') or (warehouse_postcode like '[A-Z][A-Z][0-9][ ][ ][0-9][A-Z][A-Z]')),
constraint ware_phone check ((warehouse_telephone like '[0-9][0-9][0-9][0-9][0-9][ ][0-9][0-9][0-9][0-9][0-9][0-9]')),
constraint ware_fkey foreign key (supplier_ID) references supplier
on update cascade
on delete cascade);

create table supplier_service
(supplier_service_ID char(7) not null,
constraint supplier_servicekey primary key (supplier_service_ID),
constraint suppservice_ID check ((supplier_service_ID like '[0-9][0-9][0-9][-][0-9][0-9][0-9]')),
constraint offers_fkey foreign key (supplier_ID) references supplier
on update cascade
on delete cascade);

create table web_service
(web_service_ID char(7) not null,
web_service_desription char not null,
web_service_URL char not null,
constraint web_servicekey primary key (web_service_ID),
constraint webserv_ID check ((web_service_ID like '[0-9][0-9][0-9][-][0-9][0-9][0-9]')),
constraint webserv_fkey foreign key (supplier_service_ID) references supplier_service
on update cascade
on delete cascade);

When the script runs, it tries to create a constraint on a table that doesn't exist yet.
Create the tables first, then create the constraints.

So remove the Foreign keys, run the code, then put them back in and run the foreign key constraints on there own? I didn't think you could run statements individually like that, just the entire create statement at once

You can do it like this, after the tables have been created.

the customer table must have a column named product_order_ID, in order to make it a foreign key relationship.

Ah, thanks. Using the Alter command might get me an extra mark or two in the projcet since it's not something we were taught in class, lol. As long as I can manage 40% that's a pass ^_^

Thanks pal, you may just have helped me pass, lol

If I had known this was for your homework.
I wouldn't have helped you.
If you can't do it yourself, then you don't deserve to pass the class.

The only reason I'm doing it is because it's a required class, I didn't pick it :P

Ah, tell that during your first job interview

lol, at interviews my motto is "Tell them what they want to hear, not what you think"