Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > create database/tables script not working (was "MySQL")

Reply
Page 1 of 2 1 2
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 05-05-07, 07:03
philmetz philmetz is offline
Registered User
  
Join Date: Apr 2007
Posts: 15
Question create database/tables script not working (was "MySQL")
i got the following MySQL code but its not working when i like compile/execute it:

PHP Code:
CREATE DATABASE IF NOT EXISTS KWEADB;
USE KWEADB;

CREATE TABLE IF NOT EXISTS staffmember (
staffid        SMALLINT(4) UNSIGNED NOT NULL,
staff_name    VARCHAR(30) NOT NULL,
service        VARCHAR(30) NOT NULL,
PRIMARY KEY     (staffid)
) TYPE=InnoDB;


CREATE TABLE IF NOT EXISTS placement (
openingid    SMALLINT(4) UNSIGNED NOT NULL,
start_date    DATE,
end_date    DATE,
total_bonds    INT(6),
rent        SMALLINT(4),
PRIMARY KEY     (openingid)
) TYPE=InnoDB;


CREATE TABLE IF NOT EXISTS tenant (
tenantid        SMALLINT(4) UNSIGNED NOT NULL,
openingid    SMALLINT(4) UNSIGNED NOT NULL,
tenant_name    VARCHAR(30) NOT NULL,
postal_address    VARCHAR(75) NOT NULL,
DOB        DATE,
gender        ENUM('m', 'f'),
marital_status    VARCHAR(8),
id_of_people    TINYINT(2),
tstatus        ENUM('placed', 'waiting'),
phone        VARCHAR(10),
comments    VARCHAR(100),
PRIMARY KEY     (tenantid),
FOREIGN KEY    (openingid) REFERENCES placement (openingid)
) TYPE=InnoDB;


CREATE TABLE IF NOT EXISTS property (
propertyid        SMALLINT(4) UNSIGNED NOT NULL,
ptype            VARCHAR(15),
id_of_rooms        TINYINT(2),
address            VARCHAR(75),
comments        VARCHAR(100),
PRIMARY KEY         (propertyid)
) TYPE=InnoDB;


CREATE TABLE IF NOT EXISTS owner (
ownerid        SMALLINT(4) UNSIGNED NOT NULL,
owner_name    VARCHAR(30) NOT NULL,
owner_address    VARCHAR(30) NOT NULL,
owner_phone    VARCHAR(10),
PRIMARY KEY     (ownerid)
) TYPE=InnoDB;


CREATE TABLE IF NOT EXISTS possesion (
propertyid    SMALLINT(4) UNSIGNED NOT NULL,
ownerid        SMALLINT(4) UNSIGNED NOT NULL,
PRIMARY KEY     (propertyid, ownerid),
FOREIGN KEY     (propertyid) REFERENCES property (propertyid),
FOREIGN KEY    (ownerid) REFERENCES owner (ownerid)
) TYPE=InnoDB;


CREATE TABLE IF NOT EXISTS report (
reportid        SMALLINT(4) UNSIGNED NOT NULL,
ownerid        SMALLINT(4) UNSIGNED NOT NULL,
rent        SMALLINT(4) UNSIGNED,
rent_end    DATE,
service_fee    INT(4) UNSIGNED,
inspection    VARCHAR(100),
PRIMARY KEY     (reportid),
FOREIGN KEY    (ownerid) REFERENCES owner (ownerid)
) TYPE=InnoDB;


CREATE TABLE IF NOT EXISTS waitinglist (
waitingid    SMALLINT(4) UNSIGNED NOT NULL,
openingid    SMALLINT(4) UNSIGNED NOT NULL,
start_date    DATE,
property_type    VARCHAR(15),
rent_offered    SMALLINT(4) UNSIGNED,
PRIMARY KEY     (waitingid),
FOREIGN KEY    (openingid) REFERENCES placement (openingid)
) TYPE=InnoDB;


CREATE TABLE IF NOT EXISTS futuretenant (
propertyid    SMALLINT(4) UNSIGNED NOT NULL,
waitingid    SMALLINT(4) UNSIGNED NOT NULL,
PRIMARY KEY     (propertyid, waitingid),
FOREIGN KEY     (propertyid) REFERENCES property (propertyid),
FOREIGN KEY    (waitingid) REFERENCES waitinglist (waitingid)
) TYPE=InnoDB;


CREATE TABLE IF NOT EXISTS priority (
propertyid    SMALLINT(4) UNSIGNED NOT NULL,
staffid        SMALLINT(4) UNSIGNED NOT NULL,
PRIMARY KEY     (propertyid, staffid),
FOREIGN KEY     (propertyid) REFERENCES property (propertyid),
FOREIGN KEY    (staffid) REFERENCES staffmember (staffid)
) TYPE=InnoDB; 
Can someone help with it?
THANKS
Last edited by philmetz; 05-05-07 at 19:02.
Reply With Quote
  #2 (permalink)  
Old 05-05-07, 08:33
r937 r937 is offline
SQL Consultant
  
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
Quote:
Originally Posted by philmetz
Can someone help with it?
probably

what did you want help with?
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old 05-05-07, 10:45
healdem healdem is offline
Jaded Developer
  
Join Date: Nov 2004
Location: out on a limb
Posts: 9,262
Quote:
Originally Posted by philmetz
i got the following MySQL code but its not working when i like compile/execute it:.....Can someone help with it?
THANKS
what error message do you get?
on which line?
how are you subnitting this SQL.. via Query Browser? php MyAdmin, a scrtipt a program?
on finding the error messaeg have you checked that error message on the MySQL website, and if so does that give you any hint as to what may be wrong?
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #4 (permalink)  
Old 05-05-07, 10:47
r937 r937 is offline
SQL Consultant
  
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
Quote:
Originally Posted by healdem
what error message do you get?
i'm guessing the problems begin with the first column name in the first table
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #5 (permalink)  
Old 05-05-07, 10:56
philmetz philmetz is offline
Registered User
  
Join Date: Apr 2007
Posts: 15
Question ok
am using MySQL-front
this is the error:
PHP Code:
CREATE TABLE IF NOT EXISTS staff_member (
staff                                 
staff_name    VARCHAR(30) NOT NULL,
service        VARCHAR(30) NOT NULL,
PRIMARY KEY     (staff    


CREATE TABLE IF NOT EXISTS placement (
opening                                
start_date    DATE,
end_date    DATE,
total_bonds    INT(6),
rent        SMALLINT(4),
PRIMARY KEY     (opening    


CREATE TABLE IF NOT EXISTS tenant (
tenant                                 
placement              
tenant_name    VARCHAR(30) NOT NULL,
postal_address    VARCHAR(75) NOT NULL,
D.O.B        DATE,
gender        ENUM('m','f'),
marital_status    VARCHAR(8),
                       
status        ENUM(‘placed’, ‘waiting’)
phone        VARCHAR(10),
comments    VARCHAR(100),
PRIMARY KEY     (tenant   
FOREIGN KEY    (placement                                   ))
Error: 1064 - 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 'staff_name    VARCHAR(30) NOT NULL,
service        VARCHAR(30) NOT NULL
CREATE TABLE IF NOT EXISTS property (
property                                 
type            VARCHAR(15),
                       
address            VARCHAR(75),
comments        VARCHAR(100),
PRIMARY KEY         (property    


CREATE TABLE IF NOT EXISTS owner (
owner                                 
owner_name    VARCHAR(30) NOT NULL,
owner_address    VARCHAR(30) NOT NULL,
owner_phone    VARCHAR(10),
PRIMARY KEY     (owner   
FOREIGN KEY    (property                                 ))
Error: 1064 - 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 'type            VARCHAR(15),
                       
address            VARCHAR
CREATE TABLE IF NOT EXISTS possesion (
property                                
owner                                 
PRIMARY KEY     (property         ),
FOREIGN KEY     (property                                 ),
FOREIGN KEY    (owner                           ))
Error: 1064 - 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 'owner                                 
PRIMARY KEY     (property 
CREATE TABLE IF NOT EXISTS report (
report                                 
owner                                 
rent        SMALLINT(4) UNSIGNED,
rent_end    DATE,
service_fee    INT(4) UNSIGNED,
inspection    VARCHAR(100),
PRIMARY KEY     (report   
FOREIGN KEY    (property                                 ),
FOREIGN KEY    (owner                           ))
Error: 1064 - 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 'owner                                 
rent        SMALLINT(4) UNSIG
CREATE TABLE IF NOT EXISTS waiting_list (
waiting                                
opening                                
start_date    DATE,
property_type    VARCHAR(15),
rent_offered    SMALLINT(4) UNSIGNED,
PRIMARY KEY     (waiting   
FOREIGN KEY    (opening                                 ))
Error: 1064 - 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 'opening                                
start_date    DATE,
prop
CREATE TABLE IF NOT EXISTS future_tenant (
property                                
waiting                                
PRIMARY KEY     (property           ),
FOREIGN KEY     (property                                 ),
FOREIGN KEY    (waiting                                 ))
Error: 1064 - 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 'waiting                                
PRIMARY KEY     (property
CREATE TABLE IF NOT EXISTS priority (
property                                
staff                                 
PRIMARY KEY     (property         ),
FOREIGN KEY     (property                                 ),
FOREIGN KEY    (staff                                  ))
Error: 1064 - 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 'staff 
How can i fix it?
Reply With Quote
  #6 (permalink)  
Old 05-05-07, 11:09
r937 r937 is offline
SQL Consultant
  
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
Quote:
Originally Posted by philmetz
Error: 1064 - 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 'staff[/PHP]

How can i fix it?
do you understand where this error is occurring?

first column name in first table

look up the rules for column names in the mysql manual here --> http://dev.mysql.com/doc/refman/5.0/en/identifiers.html
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #7 (permalink)  
Old 05-05-07, 11:57
philmetz philmetz is offline
Registered User
  
Join Date: Apr 2007
Posts: 15
wait is it i cant use underscored ( _ )? or could it be the hash (#)?
Last edited by philmetz; 05-05-07 at 12:10.
Reply With Quote
  #8 (permalink)  
Old 05-05-07, 12:43
guelphdad guelphdad is offline
Registered User
  
Join Date: Mar 2004
Posts: 440
I believe you can't use periods in column names.

also to note when you have fixed your error. None of your foreign keys will work correctly. You must specify ENGINE=innodb for each of your tables for foreign keys to be enforced.
Reply With Quote
  #9 (permalink)  
Old 05-05-07, 16:14
healdem healdem is offline
Jaded Developer
  
Join Date: Nov 2004
Location: out on a limb
Posts: 9,262
Quote:
Originally Posted by r937
i'm guessing the problems begin with the first column name in the first table
..........really?
why is that then?
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #10 (permalink)  
Old 05-05-07, 16:54
philmetz philmetz is offline
Registered User
  
Join Date: Apr 2007
Posts: 15
Question
hmm yea am havvin a problem with InnoDB This is what i changed my code too:
PHP Code:
CREATE DATABASE IF NOT EXISTS KWEADB;
USE KWEADB;

CREATE TABLE IF NOT EXISTS staffmember (
staffid        SMALLINT(4) UNSIGNED NOT NULL,
staff_name    VARCHAR(30) NOT NULL,
service        VARCHAR(30) NOT NULL,
PRIMARY KEY     (staffid)
) TYPE=InnoDB;


CREATE TABLE IF NOT EXISTS placement (
openingid    SMALLINT(4) UNSIGNED NOT NULL,
start_date    DATE,
end_date    DATE,
total_bonds    INT(6),
rent        SMALLINT(4),
PRIMARY KEY     (openingid)
) TYPE=InnoDB;


CREATE TABLE IF NOT EXISTS tenant (
tenantid        SMALLINT(4) UNSIGNED NOT NULL,
placementid    SMALLINT(4),
tenant_name    VARCHAR(30) NOT NULL,
postal_address    VARCHAR(75) NOT NULL,
D.O.B        DATE,
gender        ENUM('m','f'),
marital_status    VARCHAR(8),
id_of_people    TINYINT(2),
status        ENUM('placed', 'waiting')
phone        VARCHAR(10),
comments    VARCHAR(100),
PRIMARY KEY     (tenantid),
FOREIGN KEY    (placementid) REFERENCES placement (placementid)
) TYPE=InnoDB;


CREATE TABLE IF NOT EXISTS property (
propertyid        SMALLINT(4) UNSIGNED NOT NULL,
type            VARCHAR(15),
id_of_rooms        TINYINT(2),
address            VARCHAR(75),
comments        VARCHAR(100),
PRIMARY KEY         (propertyid)
) TYPE=InnoDB;


CREATE TABLE IF NOT EXISTS owner (
ownerid        SMALLINT(4) UNSIGNED NOT NULL,
owner_name    VARCHAR(30) NOT NULL,
owner_address    VARCHAR(30) NOT NULL,
owner_phone    VARCHAR(10),
PRIMARY KEY     (ownerid),
FOREIGN KEY    (propertyid) REFERENCES property (propertyid)
) TYPE=InnoDB;



CREATE TABLE IF NOT EXISTS possesion (
propertyid    SMALLINT(4) UNSIGNED NOT NULL,
ownerid        SMALLINT(4) UNSIGNED NOT NULL,
PRIMARY KEY     (propertyid, ownerid),
FOREIGN KEY     (propertyid) REFERENCES property (propertyid),
FOREIGN KEY    (ownerid) REFERENCES owner (ownerid)
) TYPE=InnoDB;


CREATE TABLE IF NOT EXISTS report (
reportid        SMALLINT(4) UNSIGNED NOT NULL,
ownerid        SMALLINT(4) UNSIGNED NOT NULL,
rent        SMALLINT(4) UNSIGNED,
rent_end    DATE,
service_fee    INT(4) UNSIGNED,
inspection    VARCHAR(100),
PRIMARY KEY     (reportid),
FOREIGN KEY    (propertyid) REFERENCES property (propertyid),
FOREIGN KEY    (ownerid) REFERENCES owner (ownerid)
) TYPE=InnoDB;


CREATE TABLE IF NOT EXISTS waitinglist (
waitingid    SMALLINT(4) UNSIGNED NOT NULL,
openingid    SMALLINT(4) UNSIGNED NOT NULL,
start_date    DATE,
property_type    VARCHAR(15),
rent_offered    SMALLINT(4) UNSIGNED,
PRIMARY KEY     (waitingid),
FOREIGN KEY    (openingid) REFERENCES placement (openingid)
) TYPE=InnoDB;


CREATE TABLE IF NOT EXISTS futuretenant (
propertyid    SMALLINT(4) UNSIGNED NOT NULL,
waitingid    SMALLINT(4) UNSIGNED NOT NULL,
PRIMARY KEY     (propertyid, waitingid),
FOREIGN KEY     (propertyid) REFERENCES property (propertyid),
FOREIGN KEY    (waitingid) REFERENCES placement (waitingid)
) TYPE=InnoDB;


CREATE TABLE IF NOT EXISTS priority (
propertyid    SMALLINT(4) UNSIGNED NOT NULL,
staffid        SMALLINT(4) UNSIGNED NOT NULL,
PRIMARY KEY     (propertyid, staffid),
FOREIGN KEY     (propertyid) REFERENCES property (propertyid),
FOREIGN KEY    (staffid) REFERENCES staff_member (staffid)
) TYPE=InnoDB; 



what wrong with it. this is the error am getting:


PHP Code:
CREATE DATABASE IF NOT EXISTS KWEADB
USE KWEADB
CREATE TABLE IF NOT EXISTS staffmember (
staffid        SMALLINT(4) UNSIGNED NOT NULL,
staff_name    VARCHAR(30) NOT NULL,
service        VARCHAR(30) NOT NULL,
PRIMARY KEY     (staffid)
) TYPE=InnoDB
CREATE TABLE IF NOT EXISTS placement (
openingid    SMALLINT(4) UNSIGNED NOT NULL,
start_date    DATE,
end_date    DATE,
total_bonds    INT(6),
rent        SMALLINT(4),
PRIMARY KEY     (openingid)
) TYPE=InnoDB
CREATE TABLE IF NOT EXISTS tenant (
tenantid        SMALLINT(4) UNSIGNED NOT NULL,
placementid    SMALLINT(4),
tenant_name    VARCHAR(30) NOT NULL,
postal_address    VARCHAR(75) NOT NULL,
D.O.B        DATE,
gender        ENUM('m','f'),
marital_status    VARCHAR(8),
id_of_people    TINYINT(2),
status        ENUM('placed', 'waiting')
phone        VARCHAR(10),
comments    VARCHAR(100),
PRIMARY KEY     (tenantid),
FOREIGN KEY    (placementid) REFERENCES placement (placementid)
) TYPE=InnoDB
Error: 1064 - 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 '.B        DATE,
gender        ENUM('m','f'),
marital_status    VARCHAR(8),

CREATE TABLE IF NOT EXISTS property (
propertyid        SMALLINT(4) UNSIGNED NOT NULL,
type            VARCHAR(15),
id_of_rooms        TINYINT(2),
address            VARCHAR(75),
comments        VARCHAR(100),
PRIMARY KEY         (propertyid)
) TYPE=InnoDB
CREATE TABLE IF NOT EXISTS owner (
ownerid        SMALLINT(4) UNSIGNED NOT NULL,
owner_name    VARCHAR(30) NOT NULL,
owner_address    VARCHAR(30) NOT NULL,
owner_phone    VARCHAR(10),
PRIMARY KEY     (ownerid),
FOREIGN KEY    (propertyid) REFERENCES property (propertyid)
) TYPE=InnoDB
CREATE TABLE IF NOT EXISTS possesion (
propertyid    SMALLINT(4) UNSIGNED NOT NULL,
ownerid        SMALLINT(4) UNSIGNED NOT NULL,
PRIMARY KEY     (propertyid, ownerid),
FOREIGN KEY     (propertyid) REFERENCES property (propertyid),
FOREIGN KEY    (ownerid) REFERENCES owner (ownerid)
) TYPE=InnoDB
CREATE TABLE IF NOT EXISTS report (
reportid        SMALLINT(4) UNSIGNED NOT NULL,
ownerid        SMALLINT(4) UNSIGNED NOT NULL,
rent        SMALLINT(4) UNSIGNED,
rent_end    DATE,
service_fee    INT(4) UNSIGNED,
inspection    VARCHAR(100),
PRIMARY KEY     (reportid),
FOREIGN KEY    (propertyid) REFERENCES property (propertyid),
FOREIGN KEY    (ownerid) REFERENCES owner (ownerid)
) TYPE=InnoDB
CREATE TABLE IF NOT EXISTS waitinglist (
waitingid    SMALLINT(4) UNSIGNED NOT NULL,
openingid    SMALLINT(4) UNSIGNED NOT NULL,
start_date    DATE,
property_type    VARCHAR(15),
rent_offered    SMALLINT(4) UNSIGNED,
PRIMARY KEY     (waitingid),
FOREIGN KEY    (openingid) REFERENCES placement (openingid)
) TYPE=InnoDB
CREATE TABLE IF NOT EXISTS futuretenant (
propertyid    SMALLINT(4) UNSIGNED NOT NULL,
waitingid    SMALLINT(4) UNSIGNED NOT NULL,
PRIMARY KEY     (propertyid, waitingid),
FOREIGN KEY     (propertyid) REFERENCES property (propertyid),
FOREIGN KEY    (waitingid) REFERENCES placement (waitingid)
) TYPE=InnoDB
CREATE TABLE IF NOT EXISTS priority (
propertyid    SMALLINT(4) UNSIGNED NOT NULL,
staffid        SMALLINT(4) UNSIGNED NOT NULL,
PRIMARY KEY     (propertyid, staffid),
FOREIGN KEY     (propertyid) REFERENCES property (propertyid),
FOREIGN KEY    (staffid) REFERENCES staff_member (staffid)
) TYPE=InnoDB 
Reply With Quote
  #11 (permalink)  
Old 05-05-07, 17:32
r937 r937 is offline
SQL Consultant
  
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
Quote:
Originally Posted by philmetz
what wrong with it. this is the error am getting:
you do not seem to understand that the error messages always tell you exactly where the error is occurring

Error: 1064 - 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 '.B DATE,

this is pretty explicit

please have a look through your script and try to find where this is happening

then let us know what you think the error might mean

you simply must learn this process of debugging, without it you will not go very far
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #12 (permalink)  
Old 05-05-07, 18:11
philmetz philmetz is offline
Registered User
  
Join Date: Apr 2007
Posts: 15
am guessing it could be a problem with the ENUM cos its gone green?
Reply With Quote
  #13 (permalink)  
Old 05-05-07, 18:16
r937 r937 is offline
SQL Consultant
  
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
Quote:
Originally Posted by philmetz
am guessing it could be a problem with the ENUM cos its gone green?
no, it's before that

where would you find the two characters .B?
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #14 (permalink)  
Old 05-05-07, 18:17
r937 r937 is offline
SQL Consultant
  
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
post #8 also contains a large clue
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #15 (permalink)  
Old 05-05-07, 18:26
philmetz philmetz is offline
Registered User
  
Join Date: Apr 2007
Posts: 15
yea i know, iv taken out the periods but still get an error
Reply With Quote
Reply
Page 1 of 2 1 2

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.