Trouble with Insert Statements

Ok, I need some help with some SQL I'm writing. My tables are compiling just fine in MS SQL Server Query Analyser, however I'm getting an error when I try to run an insert statement. Below is the table I've created, then the insert statement I'm trying to run.


create table customer
(customer_ID char(7) not null,
customer_name char(30) not null,
customer_street char(30) not null,
customer_town char(30) not null,
customer_county char(30) not null,
customer_postcode char(8) not null,
customer_telephone int not null,
customer_email char(30) not null,
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]')),
);


insert into customer values ('001-001','John Smith','4 Road Street','TownCity','TownCounty','TS10 2SW','01642 123456','John@smith.com');

Ok, change the Phone field to Char(12) NOT NULL


customer_telephone chat(12) not null,

If you drop the table and recreate it with this new code or if you do an ALTER TABLE command on the existing table, it should work.

Good luck

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);

One of the first rules of database (first normal form) is that you use numbers for adding, subtracting, multiplying and dividing. If you are not going to do math, then the field should not be a number. Telephone numbers, zip codes and the like are character fields. INT fields cannot contain a space - character fields can! The problem is an incorrect data type. Unless you are adding something to a phone number - ALWAYS make it a character field.
Also, just for kicks, never try and put formatting into the database. If the phone number is '01642 123456' then store it in a string as '01642123456'. You can format it when you look at it.