Cascade updates on primary keys..

[Aaron Smith]

Is there a way to do this safely? I'm using MSDE and have two tables
that are linked via 3 fields. If one of those fields change, I need the
update to cascade through the child file. There is a relation on the
tables on the server and on the tables in the dataset... Every time I
try to do an Update, if those fields have changed or if none have
changed, I get this error: "The query processor cound not produce a
query plan from the optimizer because a query cannot update a text,
ntext, or image column and a clustering key at the same time. I remember
having this before and I had to remove the key columns from the update
command, however that was a situation where the primary key will never
change. This one will...

I don't know if it's related or not, but doing an AddNew on the
bindingcontext of the parent file no longer adds a new row. It gets
added, but the current position is always 0. Trying to navigate to that
row doesn't work either...

Aaron

 

[Samuel R. Neff]

You shouldn't link tables on multiple fields--it's a non-normalized
and waisteful design. Define a single primary key field such as an
int identity field and use that as the fk to the other table.

If you need to maintain uniqueness on the other three fields define a
unique constraint--they don't need to be the pk to be unique.

HTH,

Sam

 

[David Browne]

You shouldn't link tables on multiple fields--it's a non-normalized
and waisteful design. Define a single primary key field such as an
int identity field and use that as the fk to the other table.

1) There's nothing "non-normalized" about a compound primary key.

2) There's nothing "wasteful" about a compound primary key.

But,

3) Primary keys should be immutable, and should never use cascade updates.

So if you must update a compound key, you will need to introduce a surrogate
primary key (eg int identity primary key).


David

 

[Samuel R. Neff]

When the primary key is used as a foreign key in another table, then
the use of a compound primary key is both wasteful and
non-normalized--it duplicates data unnecessarily.

Sam

 

[David Browne]

When the primary key is used as a foreign key in another table, then
the use of a compound primary key is both wasteful and
non-normalized--it duplicates data unnecessarily.

It's not "non-normalized" because the rules for normalization take no notice
of whether a key is simple or compound. It just doesn't affect the
normalization.

It's not "wasteful" beacuse what you loose in storing the longer foreign key
on the table, you often make up for in having fewer total indexes on the
tables. Also it simplifies and dramatically speeds filtering the related
table by the compound key columns.


EG

select sum(amount)
from order_details
where customer = 1

instead of

select sum(amount)
from order_details
where order_id in ( select id
from order
where customer = 1)

David

 

[Francois]

Hi Sam,

I would be interested in knowing how to create a unique constraint on a
field without making the field the PK.

Thanks,

Francois

 

[Samuel R. Neff]

In DDL add the keyword UNIQUE after the contraint name and before the
column names. In EM there's a checkbox for unique.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp

HTH,

Sam

 

[Francois]

Tx for that !

Francois.

In DDL add the keyword UNIQUE after the contraint name and before the
column names. In EM there's a checkbox for unique.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp