Oh...

You're welcome~ :) 
 
-----Original Message-----
From: Rohit Arora [mailto:rohit_arora@i...] 
Sent: Friday, January 24, 2003 11:38 PM
To: sql language

That was gr8. thanx Hyun-Woong Kim.Any more suggestions are also
welcome.

regards
rohit

-----Original Message-----
From: Hyun-Woong Kim [mailto:khw1006@h...]
Sent: Friday, January 24, 2003 6:25 PM
To: sql language
Subject: [sql_language] RE: Database creation from DDL - ERROR ???


Alert table's  bussinessEntityID, ProjectId mean bussinessEntityID,
ProjectId that mail sent to?

and You want if bussinessEntity is deleted, then All related Alert is
delete.

and You want if project is is deleted, then All related Alert is delete.

But you get mail that only bussinessEntity is related to?

If you get mail that both bussinessEntityID and ProjectId are related
to, then

 ALTER TABLE Alert ADD CONSTRAINT A_93374115 FOREIGN KEY
(BusinessEntityId)
REFERENCES BusinessEntity(BusinessEntityId) ON DELETE CASCADE;

is not needed.

If you delete bussinessEntity, then all Project that bussinessEntity
relate to  will be deleted.

Because of  this

ALTER TABLE Project ADD CONSTRAINT A_47137522 FOREIGN KEY
(BusinessEntityId)
REFERENCES BusinessEntity(BusinessEntityId) ON DELETE CASCADE;

then all Alert that Project relate to will be deleted.

Because of this

ALTER TABLE Alert ADD CONSTRAINT A_26723104 FOREIGN KEY (ProjectId)
REFERENCES Project(ProjectId) ON DELETE CASCADE;



-----Original Message-----
From: Rohit Arora [mailto:rohit_arora@i...]
Sent: Friday, January 24, 2003 9:25 PM
To: sql language

Alert table stores e-mail messages which we generate.Whenever a mail
comes
at Exchange server a mail message which is in xml format is generated
which
is traversed by a c# application and their respective fields are pushed
into
database.

-----Original Message-----
From: Hyun-Woong Kim [mailto:khw1006@h...]
Sent: Friday, January 24, 2003 5:52 PM
To: sql language
Subject: [sql_language] RE: Database creation from DDL - ERROR ???


What's Alert table's role?

Which data do you insert into Alert table?

-----Original Message-----
From: Rohit Arora [mailto:rohit_arora@i...]
Sent: Friday, January 24, 2003 6:51 PM
To: sql language

Hi Kim first of all thanx 4 the efforts u put in to solve the problem.

But if I make the (bussinessEntityID,ProjectId) as primary key. I dont
have
any BussinessEntityID in Well table which has a constraint

	ALTER TABLE Well ADD CONSTRAINT A_54865580 FOREIGN KEY
(ProjectId)
REFERENCES Project(ProjectId) ON DELETE CASCADE;

if u see my first mail then u can see the whole database schema.

Thanx and regards
rohit

-----Original Message-----
From: Hyun-Woong Kim [mailto:khw1006@h...]
Sent: Friday, January 24, 2003 2:42 PM
To: sql language
Subject: [sql_language] RE: Database creation from DDL - ERROR ???


First of all, I don't know detail role of your tables so This is just my
opinion.

And my mother tongue is not english... sorry poor english.. :(

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

First, Freignkey column can be null.

But Project table do not need BusinessEntityId?

If Project must have non null BusinessEntityId, Change Project Table
like this

CREATE TABLE Project( ProjectName VARCHAR(100), Status INT, Description
VARCHAR(512), AddressId INT, BusinessEntityId INT NOT NULL, ProjectId
INT NOT NULL,
PRIMARY KEY(BusinessEntityId, ProjectId));

Second, What is Alert table role?

Alert table has  ( ThreadID , AlertType , Subject , CreateDate,
Message, BusinessEntityId, ProjectId , AlertId ) .

When you insert data into Alert table, Where do you get values of
BusinessEntityld and ProjectId?

If you get them from Project table or same value of Project table,
Change Alert constraint like this

ALTER TABLE Alert ADD CONSTRAINT A_26723104 FOREIGN KEY
(BusinessEntityId, ProjectId)
REFERENCES Project(BusinessEntityId, ProjectId) ON DELETE CASCADE;

instead of these

ALTER TABLE Alert ADD CONSTRAINT A_26723104 FOREIGN KEY (ProjectId)
	REFERENCES Project(ProjectId) ON DELETE CASCADE;


ALTER TABLE Alert ADD CONSTRAINT A_93374115 FOREIGN KEY
(BusinessEntityId)
	REFERENCES BusinessEntity(BusinessEntityId) ON DELETE CASCADE;

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

This is just my opinion....




-----Original Message-----
From: Rohit Arora [mailto:rohit_arora@i...]
Sent: Friday, January 24, 2003 5:02 PM
To: sql language

Hi Ken,

Thanks for your reply. Even i noticed this thing but couldn't come up
with a
solution as basically being a asp.net/c# developer i am also doing
database
work and i am not well versed with it. So, can you suggest some changes
which I should made to these tables. Also what I saw was that
BussinessEntityID can be null in alert and project table(it is a foreign
key
there).Is that fine ??(though its not giving any problem). Any help from
ur
side be of much importance for me as i hv to get it done by 2 hours. Any
suggestions most welcome.

Thanx and Regards
Rohit

-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Friday, January 24, 2003 1:25 PM
To: sql language
Subject: [sql_language] RE: Database creation from DDL - ERROR ???


I think the problem is multiple cascade deletes. When you delete a
BusinessEntity, it cascades to Alerts and also to Projects, and then,
because the Project is deleted, to cascades to Alerts again.

I think you may need to rethink your schema because it seems you have:

BusinessEntity 1:n Alerts
and also
BusinessEntity 1:n Projects 1:n Alerts

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Rohit Arora" <rohit_arora@i...>
Subject: [sql_language] RE: Database creation from DDL - ERROR ???


: To make it more specific what I have found it let me allow one of the
: constraint on alert table of the two I have specified
:
: a) ALTER TABLE Alert ADD CONSTRAINT A_26723104 FOREIGN KEY (ProjectId)
: REFERENCES Project(ProjectId) ON DELETE CASCADE;
:
:
: b) ALTER TABLE Alert ADD CONSTRAINT A_93374115 FOREIGN KEY
: (BusinessEntityId)
: REFERENCES BusinessEntity(BusinessEntityId) ON DELETE CASCADE;
:
: Also I have a constraint ALTER TABLE Project ADD CONSTRAINT A_47137522
: FOREIGN KEY (BusinessEntityId)
: REFERENCES BusinessEntity(BusinessEntityId) ON DELETE CASCADE;
:
: It let me add either constraint a or b, whichever i execute first. plz
help.
:
: Regards
: Rohit

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~