Server: Msg 170, Level 15, State 1, Line 11

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Server: Msg 170, Level 15, State 1, Line 11

Author

Topic

amroz
Starting Member

6 Posts

Posted - 2009-07-03 : 05:31:39

Hello Everyone,
I think it might be my silly mistake, but still it is driving me crazy.

I have three tables.
First
----------------
create table sms_product(

pd_id int identity(10000,1) primary key,
cat_id int default 0,
pd_name varchar(100) unique,
pd_description varchar(200),
pd_price money,
pd_qty int,
pd_unit varchar(20),
pd_date smalldatetime default getdate(),
pd_last_update smalldatetime default getdate(),
foreign key (cat_id)
references sms_category (cat_id)
on delete cascade
);

---------------

Second

---------------

CREATE TABLE sms_vendor (
vendor_id int IDENTITY (500, 1) primary key ,
vendor_name varchar (200) ,
vendor_contact_person varchar (100) ,
vendor_address varchar (300) ,
vendor_phone varchar (10) ,
vendor_mobile varchar (10) ,
vendor_fax varchar (10) ,
vendor_remarks text
);

--------------

And third

--------------

create table sms_supplies (
vendor_id int ,
product_id int ,
product_quantity int ,
product_unit varchar (20) ,
product_price money ,
supply_date smalldatetime default getdate(),
checked_by varchar(100),
foreign key product_id references sms_product(pd_id)--err line
on delete cascade,
foreign key vendor_id references sms_vendor(vendor_id)
on delete cascade
);

--------------------------

Everything is fine (as far as I can think) with the first two tables.
But the third table gives this error

----------------------

Server: Msg 170, Level 15, State 1, Line 192
Line 192: Incorrect syntax near 'product_id'.

Kindly Help.
Regards,

Amroz K. Siddiqui

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-03 : 05:35:30

The problem is probably not the tables.
It's the statement that is run on line 192 in your code.

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-03 : 05:41:12

Hi amroz

Can you double click the error it’s automatically pointing to the error in your code line 192...

amroz
Starting Member

6 Posts

Posted - 2009-07-03 : 05:42:34

quote:
Originally posted by Peso

The problem is probably not the tables.
It's the statement that is run on line 192 in your code.

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

This is line 192

foreign key product_id references sms_product(pd_id)--err line

Amroz K. Siddiqui

amroz
Starting Member

6 Posts

Posted - 2009-07-03 : 05:44:08

quote:
Originally posted by rajdaksha

Hi amroz

Can you double click the error it’s automatically pointing to the error in your code line 192...

On double clicking that same line is highlighted

Amroz K. Siddiqui

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-03 : 05:44:56

Ok. Try naming the key to another name than existing column, such as

foreign key fk_product_id references sms_product(pd_id) --err line

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

amroz
Starting Member

6 Posts

Posted - 2009-07-03 : 05:49:53

quote:
Originally posted by Peso

Ok. Try naming the key to another name than existing column, such as

foreign key fk_product_id references sms_product(pd_id) --err line

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

OK
I did this
create table sms_supplies (
vendor_id int ,
product_id int ,
product_quantity int ,
product_unit varchar (20) ,
product_price money ,
supply_date smalldatetime default getdate(),
checked_by varchar(100),
foreign key fk_product_id references sms_product(pd_id)
on delete cascade,
foreign key vendor_id references sms_vendor(vendor_id)
on delete cascade
);

and got this error

Server: Msg 170, Level 15, State 1, Line 192
Line 192: Incorrect syntax near 'fk_product_id'.

Which is the same.

Amroz K. Siddiqui

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-03 : 05:59:01

create table sms_supplies (
vendor_id int ,
product_id int ,
product_quantity int ,
product_unit varchar (20) ,
product_price money ,
supply_date smalldatetime default getdate(),
checked_by varchar(100),
foreign key (product_id) references sms_product(pd_id)
on delete cascade,
foreign key (vendor_id) references sms_vendor(vendor_id)
on delete cascade
);

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-03 : 06:03:05

If you don't care about the names of foreign keys, try this

drop table sms_supplies ;
drop table sms_product;
drop table sms_vendor
drop table sms_category;

create table sms_category( cat_id int identity(10000,1) primary key)

create table sms_product(
pd_id int identity(10000,1) primary key,
cat_id int default 0 references sms_category (cat_id) on delete cascade,
pd_name varchar(100) unique,
pd_description varchar(200),
pd_price money,
pd_qty int,
pd_unit varchar(20),
pd_date smalldatetime default getdate(),
pd_last_update smalldatetime default getdate()
);

CREATE TABLE sms_vendor (
vendor_id int IDENTITY (500, 1) primary key ,
vendor_name varchar (200) ,
vendor_contact_person varchar (100) ,
vendor_address varchar (300) ,
vendor_phone varchar (10) ,
vendor_mobile varchar (10) ,
vendor_fax varchar (10) ,
vendor_remarks text 
);
create table sms_supplies (
vendor_id int references sms_vendor(vendor_id) on delete cascade,
product_id int references sms_product(pd_id) on delete cascade,
product_quantity int ,
product_unit varchar (20) ,
product_price money ,
supply_date smalldatetime default getdate(),
checked_by varchar(100)
);

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

amroz
Starting Member

6 Posts

Posted - 2009-07-03 : 06:04:49

Dear Peso and rajdaksha,
I am really sorry for troubling you guys.

With a little change my queries executed successfully.

Though I don't know why.
May be if you can explain me.

I did this
------------------

create table sms_supplies (
vendor_id int foreign key references sms_vendor(vendor_id) on delete cascade ,
product_id int foreign key references sms_product(pd_id) on delete cascade,
product_quantity int ,
product_unit varchar (20) ,
product_price money ,
supply_date smalldatetime default getdate(),
checked_by varchar(100),
);

-------------------------------

And all worked fine.

Thank you very much.

Amroz K. Siddiqui

amroz
Starting Member

6 Posts

Posted - 2009-07-03 : 06:07:57

quote:
Originally posted by rajdaksha

create table sms_supplies (
vendor_id int ,
product_id int ,
product_quantity int ,
product_unit varchar (20) ,
product_price money ,
supply_date smalldatetime default getdate(),
checked_by varchar(100),
foreign key (product_id) references sms_product(pd_id)
on delete cascade,
foreign key (vendor_id) references sms_vendor(vendor_id)
on delete cascade
);