Programming Forums - MySql Error

Programming Forums (http://www.programmingforums.org/forumindex.php)

- Other Web Development Languages (http://www.programmingforums.org/forum40.html)

- - MySql Error (http://www.programmingforums.org/showthread.php?t=12669)

Hello All,

I cant seem to find any problems with this code, but MySQl throws a fit when I run it (lol)

--Invoice Database 



drop table description;

drop table invoice;

drop table website;

drop table customer;





CREATE TABLE Customer

(customerID   char(3)  Primary Key,

firstName     char(30),

lastName      char(30), 

company       char(50),

streetNumber  char(10),

streetName    char(30),

city          char(20),

prov          char(20),

postal        char(7),      

phone         char(14),      

phone2        char(14),     

cell          char(14),      

home          char(14),      

fax           char(14),      

customerDate  date     NOT NULL,

domain        char(40) REFERENCES website (domain));





CREATE TABLE website

(domain         char(40) PRIMARY KEY,

customerID      char(3)  REFERENCES Customer (customerID),

dateRegistered  date     NOT NULL,

dateExpires     date     NOT NULL,

amountPaid      number(6,2));





CREATE TABLE invoice

(invoiceNumber char(4)     PRIMARY KEY,

customerID     char(3)     REFERENCES customer (customerID),

dateIssued     date        NOT NULL,

dateDue        date        NOT NULL,

datePaid       date,

status         char(10)    CHECK (status IN ('paid','unpaid','part-paid')) NOT NULL,

descriptionID  char(4)     REFERENCES description (descriptionID) NOT NULL,

subTotal       number(6,2) NOT NULL,

PSTexempt      char(3),     

totalPST       number(6,2)

totalDue       number(6,2) NOT NULL,

comments       varchar2(3000));





CREATE TABLE description

(descriptionID char(4)        PRIMARY KEY,

invoiceNumber  char(4)        REFERENCES invoice(invoiceNumber) NOT NULL,

product        varchar2(1000) NOT NULL,

price          number(6,2)    NOT NULL,

PSTexempt      char(3)        CHECK (PSTexempt IN ('yes','no')) NOT NULL,

PSTamount      number(6,2));

Details:
MySql 5.027

Errors:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'number(6,2))' at line 6

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL,descriptionID char(4) REFERENCES description (descriptionID) NOT NUL' at line 7

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL, product varchar2(1000) NOT NULL, price number(6,2) ' at line 3

What is a number() data type? I've never heard of it

Thanks, your right, apparently number isn't a data type (although it is in Oracle). And I changed the varchar2 cols to text

but i still get the following errors:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'NULL,
descriptionID char(4) REFERENCES description (descriptionID) NOT NUL' at line 7

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'NOT NULL,
product TEXT NOT NULL,
price DECIMAL(6,2) ' at line 3

changed code:

--Invoice Database 



drop table description;

drop table invoice;

drop table website;

drop table customer;





CREATE TABLE Customer

(customerID   char(3)  Primary Key,

firstName     char(30),

lastName      char(30), 

company       char(50),

streetNumber  char(10),

streetName    char(30),

city          char(20),

prov          char(20),

postal        char(7),      

phone         char(14),      

phone2        char(14),     

cell          char(14),      

home          char(14),      

fax           char(14),      

customerDate  date     NOT NULL,

domain        char(40) REFERENCES website (domain));





CREATE TABLE website

(domain         char(40) PRIMARY KEY,

customerID      char(3)  REFERENCES Customer (customerID),

dateRegistered  date     NOT NULL,

dateExpires     date     NOT NULL,

amountPaid      DECIMAL(6,2));





CREATE TABLE invoice

(invoiceNumber char(4)     PRIMARY KEY,

customerID     char(3)     REFERENCES customer (customerID),

dateIssued     date        NOT NULL,

dateDue        date        NOT NULL,

datePaid       date,

status         char(10)    CHECK (status IN ('paid','unpaid','part-paid')) NOT NULL,

descriptionID  char(4)     REFERENCES description (descriptionID) NOT NULL,

subTotal       DECIMAL(6,2) NOT NULL,

PSTexempt      char(3),     

totalPST       DECIMAL(6,2)

totalDue       DECIMAL(6,2) NOT NULL,

comments       TEXT);





CREATE TABLE description

(descriptionID char(4)        PRIMARY KEY,

invoiceNumber  char(4)        REFERENCES invoice(invoiceNumber) NOT NULL,

product        TEXT           NOT NULL,

price          DECIMAL(6,2)   NOT NULL,

PSTexempt      char(3)        CHECK (PSTexempt IN ('yes','no')) NOT NULL,

PSTamount      DECIMAL(6,2));

Any thoughts anybody ? Thanks

drop table description;

drop table invoice;

drop table website;

drop table customer;





CREATE TABLE Customer

(customerID   char(3)  Primary Key,

firstName     char(30),

lastName      char(30),

company       char(50),

streetNumber  char(10),

streetName    char(30),

city          char(20),

prov          char(20),

postal        char(7),      

phone         char(14),      

phone2        char(14),     

cell          char(14),      

home          char(14),

fax           char(14),

customerDate  date     NOT NULL,

domain        char(40) REFERENCES website (domain));





CREATE TABLE website

(domain         char(40) PRIMARY KEY,

customerID      char(3)  REFERENCES Customer (customerID),

dateRegistered  date     NOT NULL,

dateExpires     date     NOT NULL,

amountPaid      DECIMAL(6,2));





CREATE TABLE invoice

(invoiceNumber char(4)     PRIMARY KEY,

customerID     char(3)     REFERENCES customer (customerID),

dateIssued     date        NOT NULL,

dateDue        date        NOT NULL,

datePaid       date,

status_         char(10)   NOT NULL CHECK (status IN ('paid','unpaid','part-paid')),

descriptionID  char(4)     NOT NULL REFERENCES description (descriptionID) ,

subTotal       DECIMAL(6,2) NOT NULL,

PSTexempt      char(3),

totalPST       DECIMAL(6,2),

totalDue       DECIMAL(6,2) NOT NULL,

comments       TEXT);





CREATE TABLE description

(descriptionID char(4)        PRIMARY KEY,

invoiceNumber  char(4)        NOT NULL REFERENCES invoice(invoiceNumber) ,

product        TEXT           NOT NULL,

price          DECIMAL(6,2)   NOT NULL,

PSTexempt      char(3)        NOT NULL CHECK (PSTexempt IN ('yes','no')) ,

PSTamount      DECIMAL(6,2));

NOT NULL goes in front of CHECK or REFERENCES - this will help you : http://dev.mysql.com/doc/refman/5.0/...ate-table.html

Thanks, i got rid of one of the errors, there is still one left though

new code:

--Invoice Database 



drop table description;

drop table invoice;

drop table website;

drop table customer;





CREATE TABLE Customer

(customerID   char(3)  Primary Key,

firstName     char(30),

lastName      char(30), 

company       char(50),

streetNumber  char(10),

streetName    char(30),

city          char(20),

prov          char(20),

postal        char(7),      

phone         char(14),      

phone2        char(14),     

cell          char(14),      

home          char(14),      

fax           char(14),      

customerDate  date     NOT NULL,

domain        char(40) REFERENCES website (domain));





CREATE TABLE website

(domain         char(40) PRIMARY KEY,

customerID      char(3)  REFERENCES Customer (customerID),

dateRegistered  date     NOT NULL,

dateExpires     date     NOT NULL,

amountPaid      DECIMAL(6,2));





CREATE TABLE invoice

(invoiceNumber char(4)      PRIMARY KEY,

customerID     char(3)      REFERENCES customer (customerID),

dateIssued     date         NOT NULL,

dateDue        date         NOT NULL,

datePaid       date,

invStatus      char(10)     NOT NULL CHECK (status IN ('paid','unpaid','part-paid')),

descriptionID  char(4)      NOT NULL REFERENCES description (descriptionID),

subTotal       DECIMAL(6,2) NOT NULL,

PSTexempt      char(3),     

totalPST       DECIMAL(6,2)

totalDue       DECIMAL(6,2) NOT NULL,

invComments       TEXT);





CREATE TABLE description

(descriptionID char(4)        PRIMARY KEY,

invoiceNumber  char(4)        NOT NULL REFERENCES invoice(invoiceNumber),

product        TEXT           NOT NULL,

price          DECIMAL(6,2)   NOT NULL,

PSTexempt      char(3)        NOT NULL CHECK (PSTexempt IN ('yes','no')),

PSTamount      DECIMAL(6,2));

Error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'totalDue DECIMAL(6,2) NOT NULL,
invComments TEXT)' at line 12
Query OK, 0 rows affected (0.02 sec)

If you copy paste my exact code, you'll have no error. Also, how about looking at the error yourself ? You're missing a comma at line 11

Oh I cant thank you enough Xavier. Really appreciate it. Sufficed to say i got a clean run

No problem, but it would help you more if you have the patience to check out the documentation. Best of luck to you