x
login about faq Site discussion (meta-askssc)

error database

hi everybody. i want to create this database on sql 2005 and i keep getting and error which i don't know what is it. hope somebody can help me. thank you. here is the syntax, ( i get only 1. create database 2. CREATE TABLE category_master 3.CREATE TABLE user_master correct.

  create database Shop

  CREATE TABLE category_master

(  
cate_id Int,  
cat_type Varchar(50),  
PRIMARY KEY(cate_id)   
);  

  CREATE TABLE checkout_master
(   
  checkout_id Int,  
  checkout_cust_id Int,   
  checkout_date Date,  
  checkout_amount Int,  
  checkout_payment_success Int,  
checout_trnsaction_id Varchar(50),  
PRIMARY KEY(checkout_id),  
FOREIGN KEY(checkout_cust_id) REFERENCES Categories(checkout_master)   
);  


CREATE TABLE checkout_transcation  
(   
ct_id Int,  
ct_checkout_id Int,  
ct_product_id Date,  
ct_quantity Int,  
ct_rate Int  
PRIMARY KEY(ct_id),  
FOREIGN KEY (checkout_id) REFERENCES (checkout_master) ,  
FOREIGN KEY (prod_id)     REFERENCES  (product_master)  
   
);  


CREATE TABLE customer_master  
(   
cust_id	Int,  
cust_name Int,  
cust_user_name	Varchar(50),  
cust_password	Varchar(50),  
cust_email_id	Varchar(50),  
cust_last_login_date1	Date,  
cust_last_login_date2	Date,  
cust_pass_chg_date	Date,  

PRIMARY KEY(cust_id),  
);  


CREATE TABLE member_shipping  
(   
mb_id	Int,  
mb_cust_id	Int,  
mb_address_1	Varchar(150),  
mb_address_2	Varchar(150),  
mb_city	Varchar(50),  
mb_state	Varchar(50),  
mb_postal_code	Varchar(6),  
mb_contact_no	Varchar(50),  
  
PRIMARY KEY(mb_id),  
FOREIGN KEY(cust_id) REFERENCES Categories(customer_master)   
);  


CREATE TABLE product_master  
(   
prod_id	Int,  
prod_cat_id	Int,  
prod_name	Varchar(50),  
prod_description	Varchar(150),  
prod_price	Int,  
prod_tax	Int,  
prod_create_by	Int,  
prod_create_date	Date,  


PRIMARY KEY(prod_id),  
FOREIGN KEY  (cat_id) REFERENCES (category_master),  
FOREIGN KEY  (user_id)  REFERENCES (user_master)  
 
);  


CREATE TABLE shipping_master  
(   
ship_id	Int,  
ship_vendor_id	Int,  
ship_checkout_id Int,  
ship_cust_id	Int,  
ship_address1	Varchar(100),  
ship_address2	Varchar(100),  
ship_city	Varchar(100),  
ship_state	Varchar(100),  
ship_postal_code	Varchar(6),  
ship_contct_no	Varchar(50),  

PRIMARY KEY(ship_id),  
FOREIGN KEY  (vendor_id) REFERENCES (vendor_master)  
FOREIGN KEY  (check_id) REFERENCES (checkout_master)  
FOREIGN KEY  (cust_id) REFERENCES (customer_master)  
FOREIGN KEY  (user_id) REFERENCES (user_master)  
 
);  

CREATE TABLE shipping_trnsaction  
(   
st_id	Int,  
st_ship_id	Int,  
st_prod_id	Int,  
st_quantity	Int,  

PRIMARY KEY(st_id),  
FOREIGN KEY  (ship_id) REFERENCES (shipping_master),  
FOREIGN KEY  (prod_id) REFERENCES (product_master)  
 
);  

CREATE TABLE shopping_cart_master  
(   
sc_id	Int,  
sc_cust_id	Int,  
sc_date	date,  
sc_check_out	Int,  


PRIMARY KEY(sc_id),  
FOREIGN KEY (cust_id) REFERENCES (customer_master)  
 
);  

CREATE TABLE shopping_cart_transcation  
(   
sct_id	Int,  
sct_sc_id	Int,  
sct_product_id	Int,  
sct_quatity	Int,  

PRIMARY KEY(sct_id),  
FOREIGN KEY   (sc_id), REFERENCES (shopping_cart_master),  
FOREIGN KEY  (prod_id) REFERENCES (product_master)  
  
);  


CREATE TABLE user_master  
(   
user_id	Int,  
user_name	Varchar(50),  
user_password	Varchar(50),  
user_type	Varchar(50),  


PRIMARY KEY(user_id)   
);  


CREATE TABLE vendor_master  
(   
vendor_id	Int,  
vendor_name	Varchar(50),  
vendor_address_1	Varchar(100),  
vendor_address_2	Varchar(100),  
vendor_city	Varchar(50),  
vendor_state	Varchar(50),  
vendor_postal_code	Varchar(6),  
vendor_contact_no	Varchar(50),  
vendor_email	Varchar(50),  
vendor_create_by	int,  
vendor_create_date	Date,  

PRIMARY KEY(vendor_id),  
FOREIGN KEY (user_id) REFERENCES (user_master)  
 
);  
more ▼

asked Apr 23 '12 at 01:02 AM in Default

kid_sagitarius gravatar image

kid_sagitarius
10 1 2

Well you first create the database but never issue a USE statement for the database you just created. So you are creating the tables probably in the master database.

Apr 23 '12 at 01:28 AM Shawn_Melton
(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

Evening,

In looking at your code, I noticed a couple of things.

  1. In the second table create statement, you reference the Categories table in the foreign key definition but that table doesn't exist within the script, thus this portion of the script will fail.
  2. In all subsequent create statements where you include a foreign key constraint, you left out the reference table that it needs to belong to.

IE: FOREIGN KEY (cust_id) REFERENCES (customer_master)

should be

FOREIGN KEY  (cust_id) REFERENCES TABLE_NAME(customer_master)

If you want to try to fix those particular issues and see if that resolves your problem, let us know. If not, post back what error you might be getting and we can try to assist your further!

Thanks!

more ▼

answered Apr 23 '12 at 02:38 AM

JohnM gravatar image

JohnM
4.6k 1 3 7

@Shawn

thanks for your anwser, but if i use master database it's still the same. still error.

Apr 23 '12 at 02:03 AM kid_sagitarius

As another side note, @Shawn_Melton is correct. If your default database is 'Master' then all of the objects after the create table statement would be create in Master, which you probably don't want. As he mentioned, I would put a 'USE Shop' statement in your script to handle that.

Apr 23 '12 at 02:56 AM JohnM

@john thanks a lot.

all the table build successfully. only the problem here is foreign key. example here

CREATE TABLE checkout_master  
(   
checkout_id Int,  
checkout_cust_id Int,   
checkout_date Datetime,  
checkout_amount Int,  
checkout_payment_success Int,  
checkout_trnsaction_id Varchar(50),  
PRIMARY KEY(checkout_id),  
FOREIGN KEY(checkout_cust_id) REFERENCES checkout_master (checkout_cust_id)   
);  

when i execute the foreign key command the error that i get,

Msg 156, Level 15, State 1, Line 1  
Incorrect syntax near the keyword 'FOREIGN'.  

what is the problem? thanks

Apr 23 '12 at 07:36 AM kid_sagitarius

The example in this comment has you creating a table with a foreign key constraint referencing itself...

Apr 23 '12 at 09:11 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

If you're creating all your Foreign Key constraints within the Create Table statements, then you need to be very careful to make sure that you create the base tables first before attempting to reference them.

For example (in pseudocode):

CREATE TABLE Order (OrderID int, ProductID int REFERENCES Product(ProductID), Quantity int)
CREATE TABLE Product (ProductID int, Description varchar(max))

This will fail because the Order table declaration / definition references the Product table which doesn't exist at this point.

If your schema is complex, it may be worth separating out the two stages so that all tables are built before the referential integrity is added, thus: (again, pseudocode):

CREATE TABLE Order (OrderID int, ProductID int, Quantity int)
CREATE TABLE Product (ProductID int, Description varchar(max))
ALTER TABLE Order ADD CONSTRAINT FK_Order_ProductID 
                  FOREIGN KEY ProductID REFERENCES Product(ProductID)
more ▼

answered Apr 23 '12 at 09:18 AM

ThomasRushton gravatar image

ThomasRushton ♦
30.2k 7 10 40

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x1843
x49

asked: Apr 23 '12 at 01:02 AM

Seen: 376 times

Last Updated: Apr 23 '12 at 09:18 AM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.