x

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, 2012 at 01:02 AM in Default

kid_sagitarius gravatar image

kid_sagitarius
10 1 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, 2012 at 01:28 AM Shawn_Melton
(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

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, 2012 at 09:18 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

(comments are locked)
10|1200 characters needed characters left

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, 2012 at 02:38 AM

JohnM gravatar image

JohnM
6.6k 1 3 7

@Shawn

thanks for your anwser, but if i use master database it's still the same. still error.
Apr 23, 2012 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, 2012 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, 2012 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, 2012 at 09:11 AM ThomasRushton ♦
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1944
x51

asked: Apr 23, 2012 at 01:02 AM

Seen: 704 times

Last Updated: Apr 23, 2012 at 09:18 AM