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
--
---
Aaron Smith
Remove -1- to E-Mail me. Spam Sucks. 7 1388
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
On Fri, 17 Dec 2004 17:02:23 GMT, Aaron Smith
<th**********@smithcentral.net> wrote: 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" <bl****@newsgroup.nospam> wrote in message
news:fc********************************@4ax.com... 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
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
On Fri, 17 Dec 2004 13:20:12 -0600, "David Browne" <davidbaxterbrowne
no potted me**@hotmail.com> wrote: "Samuel R. Neff" <bl****@newsgroup.nospam> wrote in message news:fc********************************@4ax.com.. . 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" <bl****@newsgroup.nospam> wrote in message
news:91********************************@4ax.com... 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
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" <bl****@newsgroup.nospam> wrote in message
news:fc********************************@4ax.com... 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
On Fri, 17 Dec 2004 17:02:23 GMT, Aaron Smith <th**********@smithcentral.net> wrote:
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
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/de...aa-az_3ied.asp
HTH,
Sam
On Mon, 20 Dec 2004 10:54:04 +0700, "Francois"
<fr******@bettinghouses.com_NOSPAM> wrote: 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
Tx for that ! :)
Francois.
"Samuel R. Neff" <bl****@newsgroup.nospam> wrote in message
news:gg********************************@4ax.com... 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/de...aa-az_3ied.asp HTH,
Sam On Mon, 20 Dec 2004 10:54:04 +0700, "Francois" <fr******@bettinghouses.com_NOSPAM> wrote:
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Fraser Hanson |
last post by:
Hello,
I have a table which has a foreign key relationship with itself. I
want and expect my updates to cascade (deletes definitely cascade as
expected) but instead I just get error 1217:...
|
by: Andrew DeFaria |
last post by:
I created the following .sql file to demonstrate a problem I'm having.
According to the manual:
If |ON DELETE CASCADE| is specified, and a row in the parent table
is deleted, then InnoDB...
|
by: Gunnar Vøyenli |
last post by:
Hi!
For the sake of simplicity, I have three tables, Employee, Department and
Work
Employee >---- Department
\ /
\ /
^ ^
Work
|
by: Lee C. |
last post by:
I'm finding this to be extremely difficult to set up. I understand
that Access won't manage the primary key and the cascade updates for a
table. Fine. I tried changing the PK type to number and...
|
by: R.Welz |
last post by:
Hello.
I want to discuss a problem I have with my database design becourse I
feel I cannot decide wheather I am on the right way of doing things.
First of all, I am writing a literature and...
|
by: Michael Fuhr |
last post by:
I'd like to propose that certain GRANTs on a table cascade to the
table's implicit sequences. In the current implementation (as of
7.4.5 and 8.0.0beta3), a table owner must typically issue GRANT...
|
by: Allen Browne |
last post by:
In Access 2000 and later, you can create a relation between tables where
related records can be automatically set to Null rather than deleted when
the primary record is deleted.
I have not seen...
|
by: veaux |
last post by:
Help! I understand how primary keys work, but may need some creative
thinking to help with this. Can't be first time it was asked.
I have a streets table with a primary key of street ID. Table...
|
by: blakerrr |
last post by:
Hello World! I'm a rookie with access and my green skin needs a little help. I have two tables, one called BOM and one called Junction. BOM contains parts and assemblies and subassemblies info and...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |