deleting from a child table which have two parents

[ajith3000]

hi all,I'm doin a project with vb.net and sql as back end.i have a problem in deleting records from table.see i got a child table roommaintenance and two parent tables deptmaintenance ,wardmaintenanc e the DDL for the tables are given below



CREATE TABLE deptmaintenance
(
departmentid varchar(10) NOT NULL ,
departmentname varchar(20) NULL ,
roomrate integer NULL ,
notes varchar(max) NULL
)
go



ALTER TABLE deptmaintenance
ADD CONSTRAINT XPKdeptmaintena nce PRIMARY KEY CLUSTERED (departmentid ASC)
go


CREATE TABLE wardmaintenance
(
wardid varchar(10) NOT NULL ,
wardno integer NULL ,
departmentid varchar(10) NOT NULL ,
departmentname varchar(20) NULL ,
notes varchar(max) NULL
)
go



ALTER TABLE wardmaintenance
ADD CONSTRAINT XPKwardmaintena nce PRIMARY KEY CLUSTERED (wardid ASC)
go

CREATE TABLE roommaintenance
(
roomid varchar(10) NOT NULL ,
roomno integer NULL ,
departmentid varchar(10) NULL ,
departmentname varchar(20) NULL ,
wardid varchar(10) NULL ,
wardno integer NULL ,
roomcost money NULL ,
notes varchar(max) NULL
)
go



ALTER TABLE roommaintenance
ADD CONSTRAINT XPKroommaintena nce PRIMARY KEY CLUSTERED (roomid ASC)
go
ALTER TABLE roommaintenance
ADD CONSTRAINT R_3 FOREIGN KEY (departmentid) REFERENCES deptmaintenance (departmentid)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go

right now i cant give "ON DELETE CASCADE" for roommaintenance table which is showing an error about some r5 constraint,but i dont want to CASCADE. what i want is if i delete from the child table it should not reflect in the parent tables. please help me on this