problem with insert - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

problem with insert

Author

Topic

veronika.np
Starting Member

29 Posts

Posted - 2011-06-25 : 11:53:44

hi friends,
i have a 3 tables

item:


CREATE TABLE [dbo].[item](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nchar](10) COLLATE Arabic_CI_AS NOT NULL,
	[itemcategoryid] [int] NOT NULL)

insert into item values ('item1','1')
insert into item values ('item2','2')

itemcategory


CREATE TABLE [dbo].[itemcategory](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nchar](10) COLLATE Arabic_CI_AS NOT NULL)



insert into itemcategory  values ('test1')
insert into itemcategory  values ('test2')
insert into itemcategory  values ('test3')
insert into itemcategory  values ('test4')

and order table


CREATE TABLE [dbo].[order](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[itemid] [int] NULL,
	[itemcategoryid] [int] NULL)

insert into [order] values('1',null)
insert into order values('2',null)

when i want to insert in order table ,i want to if for itemcategoryid
that i want to insert there is not item in item table it print'yes'

for example when i exec this query i wan to print no becase i don`t define item for itemcategoryid =4

select count(*) from item i where i.itemcategoryid in(1,4)

i dont know how do this.please help me.i need your helping.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-25 : 12:11:28

You should NOT have a column itemcategory in the table order.
In table item the column itemcategoryid should be a foreign key to itemcategory.itemcategoryid

Then it is not possible to insert an itemid into table item without having a valid itemcategoryid.

The table order should have itemid as a foreign key to item.itemid

Then it is not possible to insert an itemid into table order that is not valid.

Then all your problems are gone...

Hope this helps

No, you're never too old to Yak'n'Roll if you're too young to die.

veronika.np
Starting Member

29 Posts

Posted - 2011-06-25 : 12:56:43

quote:
Originally posted by webfred

You should NOT have a column itemcategory in the table order.
In table item the column itemcategoryid should be a foreign key to itemcategory.itemcategoryid

Then it is not possible to insert an itemid into table item without having a valid itemcategoryid.

The table order should have itemid as a foreign key to item.itemid

Then it is not possible to insert an itemid into table order that is not valid.

Then all your problems are gone...

Hope this helps

No, you're never too old to Yak'n'Roll if you're too young to die.

yes,they are foregin key.but i want if a insert itemcategoryid in order table that it hasn`t item in item table.it prevents.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-25 : 13:16:55

Please, you don't need the itemcategoryid in the table order because an item already knows its category.

No, you're never too old to Yak'n'Roll if you're too young to die.

veronika.np
Starting Member

29 Posts

Posted - 2011-06-25 : 13:18:25

quote:
Originally posted by webfred

Please, you don't need the itemcategoryid in the table order because an item already knows its category.

No, you're never too old to Yak'n'Roll if you're too young to die.

i need it.because some time i insert itemid annd some time user can insert itemcategoryid.user can chose.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-25 : 13:34:58

OK. So my idea is a trigger to avoid invalid inserts:

create trigger ins_order on [order] for insert as
begin
	if exists(select * from inserted ins
					   left join item it on it.itemcategoryid = ins.itemcategoryid
					   where it.itemcategoryid is null)
	begin
		RAISERROR('Given Category has no items!',16,1)
		rollback transaction
	end
end

-- try to insert an invalid value:
insert [order](itemid,itemcategoryid) select null,4
-- errors: Given Category has no items!

-- try to insert a valid value
insert [order](itemid,itemcategoryid) select null,1
-- no error

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources