delete contraint

HI all,
Hope I have the correct terminology.
I have related tables, with cascading deletes off. In otherwords, you cannot
delete a parent row if there are related child row.
Now when this happens a correct error is produced.
Is there any way to have a single return statement stipulation which child
table was involved in the cascading delete( or rarther the parent delete
failing)
Reasoning behind this is that because I have many child tables related with
a single parent, it would be a lot easier to know which table cause the
delete to fail, go to that table and determin if row in that particular
table can be deleted.
Currently I have to go to every related child table, and figure out if it
caused the delete to fail, and if ti did, then take the needed action.
I'm really looking for principals here, or for someone to steer me in the
right direction, perhaps some reading material etc.
Thanks
RobertAre you saying that you want to know which referencing table is it that proh
ibits the DELETE
operation? Check out the error message from the DELETE operation. If you hav
e several referencing
tables that has a conflict, you will only get on of them, though:
CREATE TABLE rd(c1 int primary key)
INSERT INTO rd (c1) VALUES(1)
GO
CREATE TABLE rs1(c1 int REFERENCES rd(c1))
CREATE TABLE rs2(c1 int REFERENCES rd(c1))
INSERT INTO rs2 (c1) VALUES(1)
CREATE TABLE rs3(c1 int REFERENCES rd(c1))
INSERT INTO rs3 (c1) VALUES(1)
GO
DELETE FROM rd WHERE c1 = 1
Server: Msg 547, Level 16, State 1, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__rs2__c1__
4D0CD9BB". The conflict
occurred in database "tempdb", table "dbo.rs2", column 'c1'.
The statement has been terminated.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Robert Bravery" <me@.u.com> wrote in message news:egQxAm8MGHA.2828@.TK2MSFTNGP12.phx.gbl...[
color=darkred]
> HI all,
> Hope I have the correct terminology.
> I have related tables, with cascading deletes off. In otherwords, you cann
ot
> delete a parent row if there are related child row.
> Now when this happens a correct error is produced.
> Is there any way to have a single return statement stipulation which child
> table was involved in the cascading delete( or rarther the parent delete
> failing)
> Reasoning behind this is that because I have many child tables related wit
h
> a single parent, it would be a lot easier to know which table cause the
> delete to fail, go to that table and determin if row in that particular
> table can be deleted.
> Currently I have to go to every related child table, and figure out if it
> caused the delete to fail, and if ti did, then take the needed action.
> I'm really looking for principals here, or for someone to steer me in the
> right direction, perhaps some reading material etc.
> Thanks
> Robert
>[/color]

Delclarative RI in SQL Server 2005

Does anyone know if in SQL Server 2005 declarative RI can be used to NULL a
referenced field on delete of a record from the parent table?
Any help is appreciated.
Don
Yes, there is a SET NULL option when the foreign key constraint is created.
The foreign key column(s) must of course be nullable.
Happy Holidays
Dan Guzman
SQL Server MVP
"Don" <Don@.discussions.microsoft.com> wrote in message
news:FC073BAB-8767-44C7-9FDC-CACF6E66AC84@.microsoft.com...
> Does anyone know if in SQL Server 2005 declarative RI can be used to NULL
> a
> referenced field on delete of a record from the parent table?
> Any help is appreciated.
> Don
|||Thank you very much
"Dan Guzman" wrote:

> Yes, there is a SET NULL option when the foreign key constraint is created.
> The foreign key column(s) must of course be nullable.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:FC073BAB-8767-44C7-9FDC-CACF6E66AC84@.microsoft.com...
>
>

Delclarative RI in SQL Server 2005

Does anyone know if in SQL Server 2005 declarative RI can be used to NULL a
referenced field on delete of a record from the parent table?
Any help is appreciated.
DonYes, there is a SET NULL option when the foreign key constraint is created.
The foreign key column(s) must of course be nullable.
Happy Holidays
Dan Guzman
SQL Server MVP
"Don" <Don@.discussions.microsoft.com> wrote in message
news:FC073BAB-8767-44C7-9FDC-CACF6E66AC84@.microsoft.com...
> Does anyone know if in SQL Server 2005 declarative RI can be used to NULL
> a
> referenced field on delete of a record from the parent table?
> Any help is appreciated.
> Don|||Thank you very much
"Dan Guzman" wrote:

> Yes, there is a SET NULL option when the foreign key constraint is created
.
> The foreign key column(s) must of course be nullable.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:FC073BAB-8767-44C7-9FDC-CACF6E66AC84@.microsoft.com...
>
>

Delclarative RI in SQL Server 2005

Does anyone know if in SQL Server 2005 declarative RI can be used to NULL a
referenced field on delete of a record from the parent table?
Any help is appreciated.
DonYes, there is a SET NULL option when the foreign key constraint is created.
The foreign key column(s) must of course be nullable.
--
Happy Holidays
Dan Guzman
SQL Server MVP
"Don" <Don@.discussions.microsoft.com> wrote in message
news:FC073BAB-8767-44C7-9FDC-CACF6E66AC84@.microsoft.com...
> Does anyone know if in SQL Server 2005 declarative RI can be used to NULL
> a
> referenced field on delete of a record from the parent table?
> Any help is appreciated.
> Don|||Thank you very much
"Dan Guzman" wrote:
> Yes, there is a SET NULL option when the foreign key constraint is created.
> The foreign key column(s) must of course be nullable.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:FC073BAB-8767-44C7-9FDC-CACF6E66AC84@.microsoft.com...
> > Does anyone know if in SQL Server 2005 declarative RI can be used to NULL
> > a
> > referenced field on delete of a record from the parent table?
> >
> > Any help is appreciated.
> >
> > Don
>
>

delay in running the sub-packages

Hi,
when call a sub-package from the parent package with the executeoutofprocess=true I have about 5-10 sec delay before sub-package starts running. ( this is a big delay for me as the sub_package is in a foreach loop)
the sub_package itself it's very simple and contains just one script task with a small script in in it.
even if this delay is for validation I still can't understand why it is taking 5-10 ses.
cheers

Kolf wrote:

Hi,
when call a sub-package from the parent package with the executeoutofprocess=true I have about 5-10 sec delay before sub-package starts running. ( this is a big delay for me as the sub_package is in a foreach loop)

the sub_package itself it's very simple and contains just one script task with a small script in in it.
even if this delay is for validation I still can't understand why it is taking 5-10 ses.
cheers

I have to add that the subpackage has got some variables which is specified in package configurations
|||

Have you tried executeoutofprocess = false? Do you have the same delay?

|||And delayvalidation = true?

Degenerate Dimension

Experts:

Pardon me for this basic question. A degenerate dimension as I understand does not have a parent dimension (pls correct me if I am wrong) in the fact table. In that case is it repeated in the fact table?

For example if i have the following fact table

Productkey(FK)

Customerkey(FK)

Timekey(FK)

TicketNumber

All the first three (product,customer and Time) have dimensions while Ticketnumber has a orphan parent. In that case does its value get repeated in the fact table or is it usually a unique value?

Please explain.

Thanks

Ankith

Hello.

You are correct that ticketnumber do not have a parent and is a degenerate dimension.

If you sell the same ticket several times it can appear several times in the fact table. That is normally not the case. Think of a bus ticket, a cinema ticket or an airline ticket. For these later scenarios you will not see a repeted ticketnumber.

HTH

Thomas Ivarsson

|||

Hi Thomas:

Thank you very much for a lucid and beatiful explanation with examples. Appreciate it.

Best Regards

Ankith

default value of the foreign key field in a child table

There are two tables, parent and child table.

Foreign key field of the child table is not a required field. If the
data is submitted w/o a value into the foreign key field, what should
I make it as a default value? Should it be "null"?reneeccwest@.hotmail.com (reneeccwest) wrote in message news:<9f9d6d21.0310081243.7e44194d@.posting.google.com>...
> There are two tables, parent and child table.
> Foreign key field of the child table is not a required field. If the
> data is submitted w/o a value into the foreign key field, what should
> I make it as a default value? Should it be "null"?

If the column is NULLable, then NULL should be acceptable; if you need
to enforce a default value, that suggests you should probably have a
NOT NULL column with a DEFAULT constraint. So it depends on your data
model and business rules.

Simon|||Hi,

it MUST be NULL, otherwise you get a constraint violation; you can
define a default value but it have to be inserted on the master table.

Bye.

reneeccwest@.hotmail.com (reneeccwest) wrote in message news:<9f9d6d21.0310081243.7e44194d@.posting.google.com>...
> There are two tables, parent and child table.
> Foreign key field of the child table is not a required field. If the
> data is submitted w/o a value into the foreign key field, what should
> I make it as a default value? Should it be "null"?