Re: How to create ForeignKeyConstraint?
- From: "Branco Medeiros" <branco.medeiros@xxxxxxxxx>
- Date: 25 Dec 2006 17:03:45 -0800
max wrote:
Hello,<snip>
I am a newbye, and I'm trying to write a simple application.
I have five tables with three columns; all tables are identical; I need to
change some data in the first table and let VB updates the same data in all
other four tables in the right places.
I know it would be possible by using the ForeignKeyConstraint object. I have
created the tables using the DataSet Visual Tool and I know it doesn't
create any ForeignKeyConstraint obj. I have found many codes examples on it,
but I don't know how to merge the code they in an existing dataset.
I really didn't get what you're trying. And it seems you're mistaking
what a foreign key really is.
updated when one of then is updated. This design seems very odd to meFrom what I got you want five tables identical to each other that get
(why one would want five identical tables in the same database?), but I
assume you have your reasons. It's this, or I completely misunderstood
the layout of the tables -- for instance, they may be related somehow,
in a way you didn't describe (or I failled to understand).
Anyway, a foreign key constraint won't give exectly what you want.
A foreign key (or FK) is a column in a table that holds *primary keys*
of another table. This way you become assured that the foreign element
really exists in the other table (also called foreign, or parent
table).
Consider for example a table B, with a column IDB (it's primary key, or
PK) and other columns with data. I may have a table A, that references
rows in table B. The columns in A that references the rows in B will be
foreign keys in A to B.
For instance, table A could have the columns IDA ( it's PK), IDB (an FK
to table B, whose value would be, as you probalbly guessed, taken from
column B.IDB), and other columns, that we won't care here.
In this layout, B would be "a" parent table for A. Each row in A would
have to have a valid value in the IDB column, that is, the value in
A.IDB would have to be either NULL or an existing value in B.IDB.
More over, if a row in B was to be deleted and there existed rows in A
that referenced that row, an error would occur (well, not necessarily.
Read on). In the same token, if a row in B had the value of IDB
updated, an error would occur if there existed references to that row
in A.
The name of this game is "referencial integrity".
Notice that you may configure your FK constraint to delete the items
from table A that referenced items in table B when those are deleted.
Also, you may configure it to automagically update the value in A.IDB
whenever the corresponding B.IDB becomes updated.
This may seem exactly what you want, but, as I said before, an FK can
only reference the *primary key* of the foreign table, and this doesn't
seem to be the case of your tables.
Even if you managed to define FKs from your table to other tables in
your setup, you'd still have a problem. Because you're defining the FK
constraint in code (not in the database), you'd have to load the data
from *all* the referenced tables into the application before the
constraint could be applied. This may be feasible, but if you have
large tables it may become prohibitive, performance-wise.
It seems a more detailed description of what you really are up to is
needed here...
HTH.
Regards,
Branco.
.
- Follow-Ups:
- Re: How to create ForeignKeyConstraint?
- From: Max
- Re: How to create ForeignKeyConstraint?
- References:
- How to create ForeignKeyConstraint?
- From: max
- How to create ForeignKeyConstraint?
- Prev by Date: Changing user interface
- Next by Date: Re: Changing user interface
- Previous by thread: Re: How to create ForeignKeyConstraint?
- Next by thread: Re: How to create ForeignKeyConstraint?
- Index(es):
Relevant Pages
|
