error database

Question

question

kid_sagitarius asked Apr 23, '12

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

sql-server-2005 sql-server-express

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton commented · Apr 23, 2012 at 01:28 AM

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.

1 ·

Answer 1 · 2012-04-23T02:38:23Z

JohnM answered Apr 23, '12

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!

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

kid_sagitarius commented · Apr 23, 2012 at 02:03 AM

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

0 ·

JohnM commented · Apr 23, 2012 at 02:56 AM

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.

0 ·

kid_sagitarius commented · Apr 23, 2012 at 07:36 AM

@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

0 ·

ThomasRushton ♦♦ commented · Apr 23, 2012 at 09:11 AM

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

0 ·

Answer 2 · 2012-04-23T09:18:14Z

ThomasRushton answered Apr 23, '12

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)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question

error database

2 Answers

Write an Answer

question details

question

error database

2 Answers

Write an Answer

question details

Related Questions