Ciclic Foreign Keys?

Hi:
Scenario:
-) I have four tables TableA, TableB, TableC and ProductTable.
-) TableA is the main header table of TableB, TableB contains a
reference to a 'Product' in table ProductTable.
-) TableB is, in turn, a header table of TableC, TableC contains a
reference to a 'Product' in table ProductTable.
-) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableB
to TableA, so if the row in TableA is deleted, it also deletes TableB's
corresponding row(s).
-) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableC
to TableB, so if the row in TableB is deleted, it also deletes TableC's
corresponding row(s).
Now I want to add 2 more ForeignKeys from TablaB and TableC to the
ProductTable with CASCADING DELETES, so if the product is deleted it
will automatically delete corresponding rows from TableB and TableC.
I can set the ForeignKey on *one* of the tables, but I can't set it on
both as the servers informs me that this would cause a cyclic action. I
can't see what the problem is. If TableB contains a reference to a
particular product (lets call it 'ProductX') and TableC contains a
reference to another product ('ProductY') there is not cyclic deletion.
Can anyone help me workout what the problem is.
TIA,
MartinH.The problem is that there are multiple cascade paths from ProductTable to
TableC. There isn't any declarative way to specify that the reference
between TableB and ProductTable and the reference between TableC and
ProductTable can never refer to the same product, so SQL Server must assume
that they can, and consequently prevents the referential action declaration.
It looks like you're going to have to use a trigger to implement the cascade
delete.
"Martin Hart" <martin.hartturner@.gmail.com> wrote in message
news:OCJ2eE2kFHA.320@.TK2MSFTNGP09.phx.gbl...
> Hi:
> Scenario:
> -) I have four tables TableA, TableB, TableC and ProductTable.
> -) TableA is the main header table of TableB, TableB contains a
> reference to a 'Product' in table ProductTable.
> -) TableB is, in turn, a header table of TableC, TableC contains a
> reference to a 'Product' in table ProductTable.
> -) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableB
> to TableA, so if the row in TableA is deleted, it also deletes TableB's
> corresponding row(s).
> -) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableC
> to TableB, so if the row in TableB is deleted, it also deletes TableC's
> corresponding row(s).
> Now I want to add 2 more ForeignKeys from TablaB and TableC to the
> ProductTable with CASCADING DELETES, so if the product is deleted it
> will automatically delete corresponding rows from TableB and TableC.
> I can set the ForeignKey on *one* of the tables, but I can't set it on
> both as the servers informs me that this would cause a cyclic action. I
> can't see what the problem is. If TableB contains a reference to a
> particular product (lets call it 'ProductX') and TableC contains a
> reference to another product ('ProductY') there is not cyclic deletion.
> Can anyone help me workout what the problem is.
> TIA,
> MartinH.|||Brian:
Thanks, I now understand why, and thanks to your suggestion 'how' I can
get around the problem.
Thanks again.
Martin.
Brian Selzer escribi:
> The problem is that there are multiple cascade paths from ProductTable to
> TableC. There isn't any declarative way to specify that the reference
> between TableB and ProductTable and the reference between TableC and
> ProductTable can never refer to the same product, so SQL Server must assum
e
> that they can, and consequently prevents the referential action declaratio
n.
> It looks like you're going to have to use a trigger to implement the casca
de
> delete.
>
> "Martin Hart" <martin.hartturner@.gmail.com> wrote in message
> news:OCJ2eE2kFHA.320@.TK2MSFTNGP09.phx.gbl...
>
>
>|||I prefer to avoid cascading referential actions whenever possible because
they introduce an additional level of complexity into deadlock minimization
and avoidance. It's better in my opinion to spell out the deletes or
updates, either in an instead of trigger or in a stored procededure. This
way I control the order in which locks are obtained, thus minimizing
deadlocks.
"Martin Hart" <martin.hartturner@.gmail.com> wrote in message
news:#f7DXN3kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Brian:
> Thanks, I now understand why, and thanks to your suggestion 'how' I can
> get around the problem.
> Thanks again.
> Martin.
> Brian Selzer escribi:
to
assume
declaration.
cascade

Choosing values for primary keys

Hello group:

I've done alot of reading on this subject somewhat and have found that
many people have many different opinions on this subject. My question
centers mainly around using a lookup table to enable users to select a
pre-defined list of values.

I have developed a practice myself of avoiding AutoNumber type data
fields for primary keys where the primary key will be related to a
child table. Nevertheless, what do most users do with lookup tables?
My thoughts are to create a small key value for each value in the
lookup table. For example:

I might have a Carriers table which shows a list of carriers that I
might ship an order by. One of the entries may be 'Air Freight -
Overnight', or 'Air Freight - 2nd Day Air'. I've seen a few examples
where the primary key field for each entry like these would be
autonumber, or at least, a numeric value. What I like to do is create
my own key, like for 'Air Freight - Overnight', I might use 'AFO' for
the key, and for 'Air Freight - 2nd Day Air', I might use 'AF2'. Any
thoughts on this? Mine are that even tho the users may never see this
value - I, as the developer will see it and I tend to prefer a key
value based on real data that means something other than an
auto-incremented number. In referencing the well-known Northwind.mdb
database, I noticed their Categories table used a number field value,
like 1, 2, 3...etc, but their customers table used values like
'ALFKI' to represent their key values.

What are some other thoughts out there? I'm working with Access
currently, but this project is about to move to SQL Server.

JamesI can't speak from much experience (only actually created a few small
tables...) but in large tables, you'll save space using a numeric value
I think. A 32 bit value will give you LOTS of unique numbers for rows.
In your example, 3 ascii characters is still shorter (24 bits.)
However if you end up using lots of long-ish keys, you'll eat up lots of
extra bits.

However, you can see that I use lots of letters to say very little, so
who am I to comment on space?! :)

Just my $.02...trying not to lurk so much!

-gabe

James wrote:
> Hello group:
> I've done alot of reading on this subject somewhat and have found that
> many people have many different opinions on this subject. My question
> centers mainly around using a lookup table to enable users to select a
> pre-defined list of values.
> I have developed a practice myself of avoiding AutoNumber type data
> fields for primary keys where the primary key will be related to a
> child table. Nevertheless, what do most users do with lookup tables?
> My thoughts are to create a small key value for each value in the
> lookup table. For example:
> I might have a Carriers table which shows a list of carriers that I
> might ship an order by. One of the entries may be 'Air Freight -
> Overnight', or 'Air Freight - 2nd Day Air'. I've seen a few examples
> where the primary key field for each entry like these would be
> autonumber, or at least, a numeric value. What I like to do is create
> my own key, like for 'Air Freight - Overnight', I might use 'AFO' for
> the key, and for 'Air Freight - 2nd Day Air', I might use 'AF2'. Any
> thoughts on this? Mine are that even tho the users may never see this
> value - I, as the developer will see it and I tend to prefer a key
> value based on real data that means something other than an
> auto-incremented number. In referencing the well-known Northwind.mdb
> database, I noticed their Categories table used a number field value,
> like 1, 2, 3...etc, but their customers table used values like
> 'ALFKI' to represent their key values.
> What are some other thoughts out there? I'm working with Access
> currently, but this project is about to move to SQL Server.
>
> James|||[posted and mailed, please reply in news]

James (dragonzfang@.hotmail.com) writes:
> I might have a Carriers table which shows a list of carriers that I
> might ship an order by. One of the entries may be 'Air Freight -
> Overnight', or 'Air Freight - 2nd Day Air'. I've seen a few examples
> where the primary key field for each entry like these would be
> autonumber, or at least, a numeric value. What I like to do is create
> my own key, like for 'Air Freight - Overnight', I might use 'AFO' for
> the key, and for 'Air Freight - 2nd Day Air', I might use 'AF2'. Any
> thoughts on this? Mine are that even tho the users may never see this
> value - I, as the developer will see it and I tend to prefer a key
> value based on real data that means something other than an
> auto-incremented number. In referencing the well-known Northwind.mdb
> database, I noticed their Categories table used a number field value,
> like 1, 2, 3...etc, but their customers table used values like
> 'ALFKI' to represent their key values.

In the system I work, we use both mnemonic codes and numeric keys
(which rarely are IDENTITY values, but we generate them ourselves).
But we do not pick them at random.

Basically, if the table is pre-loaded, that is we define the data in
the table, the key is a good. This is because we may have to refer to
the key value in our SQL code (or client code), and using numeric values
may easily cause errors.

On the other hand, if the data in the table is user-entered, the key is
numeric. Because who would generate the codes in this case? There are a
few tables with user-entered data where the key is actually a code,
but this is when there is a natural code to pick. Prime examples are
countries and currencies.

(There are also pre-loaded tables with numeric keys. But I didn't
design them. Or they were accidents. :-)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||James Lankford (dragonzfang@.hotmail.com) writes:
> In my Carriers table example, this is mainly just a lookup table, values
> are not likely to change often. If a new code needs to be defined, then
> the administrator can simply create his/her own unique key for the new
> entry.

We usually have a GUI for this sort of thing, but as you say, a lot this
data is highly static once it is in place.

> In the case of header/detail, parent to child table examples, I can see
> where having an autonumber generated key value is very beneficial. The
> two tables would still be linked via an invoice number, for example -
> but yet the autonumber key ID would serve as the unique identifer for
> the row. If the table becomes corrupted and needs to be rebuilt, or
> exported to another table, then it doesn't matter if the ID #'s change -
> nothing else is really "depending" upon it, and it still serves to
> uniquely identify that row.

For this kind of example, I prefer to have (InvoiceNo, RowNo) as the
key for the child table.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Checking Foreign Keys in script

I have the following in an update script.
ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
CONSTRAINT [FK_SoftwareRentalMaster_ProductLookup] FOREIGN KEY
(
[ProductCode]
) REFERENCES [dbo].[ProductLookup] (
[ProductCode]
)
GO
I would like to check if the FOREIGN KEY exists and delete or use an if
statement to prevent the following error messages
Server: Msg 2714, Level 16, State 4, Line 2
There is already an object named 'FK_SoftwareRentalMaster_ProductLookup' in
the database.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.
How can i do this
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004
Hi,
Use the below sample of script. Replace the table name, constraint name and
column names based on ur requirement.
if exists(select constraint_name from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where constraint_name ='fk_i')
Begin
select 'Foreign Key exists'
select 'dropping the Foreign Key constraint'
alter table x_2 drop constraint fk_i
end
select 'Creating the Foreign key constraint back to table'
Alter table x_2 add constraint fK_i foreign key (i) references x_1(i)
Thanks
Hari
MCDBA
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:uKPPPvFREHA.3728@.TK2MSFTNGP10.phx.gbl...
> I have the following in an update script.
> ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
> CONSTRAINT [FK_SoftwareRentalMaster_ProductLookup] FOREIGN KEY
> (
> [ProductCode]
> ) REFERENCES [dbo].[ProductLookup] (
> [ProductCode]
> )
> GO
> I would like to check if the FOREIGN KEY exists and delete or use an if
> statement to prevent the following error messages
> Server: Msg 2714, Level 16, State 4, Line 2
> There is already an object named 'FK_SoftwareRentalMaster_ProductLookup'
in
> the database.
> Server: Msg 1750, Level 16, State 1, Line 2
> Could not create constraint. See previous errors.
> How can i do this
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004
>

Checking Foreign Keys in script

I have the following in an update script.
ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
CONSTRAINT [FK_SoftwareRentalMaster_ProductLookup] FOREIGN KEY
(
[ProductCode]
) REFERENCES [dbo].[ProductLookup] (
[ProductCode]
)
GO
I would like to check if the FOREIGN KEY exists and delete or use an if
statement to prevent the following error messages
Server: Msg 2714, Level 16, State 4, Line 2
There is already an object named 'FK_SoftwareRentalMaster_ProductLookup' in
the database.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.
How can i do this
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004Hi,
Use the below sample of script. Replace the table name, constraint name and
column names based on ur requirement.
if exists(select constraint_name from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where constraint_name ='fk_i')
Begin
select 'Foreign Key exists'
select 'dropping the Foreign Key constraint'
alter table x_2 drop constraint fk_i
end
select 'Creating the Foreign key constraint back to table'
Alter table x_2 add constraint fK_i foreign key (i) references x_1(i)
Thanks
Hari
MCDBA
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:uKPPPvFREHA.3728@.TK2MSFTNGP10.phx.gbl...
> I have the following in an update script.
> ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
> CONSTRAINT [FK_SoftwareRentalMaster_ProductLookup] FOREIGN KEY
> (
> [ProductCode]
> ) REFERENCES [dbo].[ProductLookup] (
> [ProductCode]
> )
> GO
> I would like to check if the FOREIGN KEY exists and delete or use an if
> statement to prevent the following error messages
> Server: Msg 2714, Level 16, State 4, Line 2
> There is already an object named 'FK_SoftwareRentalMaster_ProductLookup'
in
> the database.
> Server: Msg 1750, Level 16, State 1, Line 2
> Could not create constraint. See previous errors.
> How can i do this
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004
>

Checking Foreign Keys in script

I have the following in an update script.
ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
CONSTRAINT & #91;FK_SoftwareRentalMaster_ProductLooku
p] FOREIGN KEY
(
[ProductCode]
) REFERENCES [dbo].[ProductLookup] (
[ProductCode]
)
GO
I would like to check if the FOREIGN KEY exists and delete or use an if
statement to prevent the following error messages
Server: Msg 2714, Level 16, State 4, Line 2
There is already an object named 'FK_SoftwareRentalMaster_ProductLookup' in
the database.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.
How can i do this
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004Hi,
Use the below sample of script. Replace the table name, constraint name and
column names based on ur requirement.
if exists(select constraint_name from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where constraint_name ='fk_i')
Begin
select 'Foreign Key exists'
select 'dropping the Foreign Key constraint'
alter table x_2 drop constraint fk_i
end
select 'Creating the Foreign key constraint back to table'
Alter table x_2 add constraint fK_i foreign key (i) references x_1(i)
Thanks
Hari
MCDBA
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:uKPPPvFREHA.3728@.TK2MSFTNGP10.phx.gbl...
> I have the following in an update script.
> ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
> CONSTRAINT & #91;FK_SoftwareRentalMaster_ProductLooku
p] FOREIGN KEY
> (
> [ProductCode]
> ) REFERENCES [dbo].[ProductLookup] (
> [ProductCode]
> )
> GO
> I would like to check if the FOREIGN KEY exists and delete or use an if
> statement to prevent the following error messages
> Server: Msg 2714, Level 16, State 4, Line 2
> There is already an object named 'FK_SoftwareRentalMaster_ProductLookup'[
/vbcol]
in[vbcol=seagreen]
> the database.
> Server: Msg 1750, Level 16, State 1, Line 2
> Could not create constraint. See previous errors.
> How can i do this
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004
>