How to create ForeignKeyConstraint?

  • Thread starter Thread starter max
  • Start date Start date
M

max

Hello,
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.

Hope somebody help.
Thanks in advance,
Max.
 
What kind of database are you using? VB2003 or VB2005?
..Net 1.1 or .Net 2.0?

How are you filling your tables?

Robin S.
 
Thanks for yor answer. I'm using VB2005, .Net 2.0.
Tables structure and data was developed in Access and loaded in a dataset
using the Data Source Configuration Wizard. Then I created a form, dragged
the table from the Dataset which creates a bindingnavigator obj on top of
form; a second form load this form by a button and I correctly see the data.
There are only three columns in the tables: an incremental ID (I gave it
primary key), a number, and a name. Then I need to change, insert and delete
records in the first table and replicate this change in all the other four
tables, that are identical in structure and data. I can change and update
data in the first table (because I see that changes was really on it), but I
cannot replicate in the others. I have already tried to make relations using
Data Designer, but it doesn't work; I have read in some newsgroup that
ForeignKeyConstraint cannot be created by this way.
I'd like to understand where and how insert vb code to create relationship
between columns in the first table to all the others. I can't realize in
which vb module, vb code must be added.
Thanks for your attention and sorry for my English.
Max
 
Do you have no relationships defined between the tables
in Access? Do they not show up in the Dataset Designer?

I'm not going to swear this will work. But you could
try adding your own foreign key constraints. I'm also not
sure where you would do this, but I would guess *after* the
dataset is filled, and *before* you do the binding. (I'm
assuming you're doing the binding in code, but maybe not?)

Here's an example on setting up a foreign key constraint.
This is from David Sceppa's book ADO.Net 2.0 The Core
Reference.

This uses the Customers and Orders tables from the Northwind
database.

'how to define a foreign key constraint
'first, fill your dataset with the two tables involved
'this is mine; this is loading from SQLServer, but
' load it however you're loading it from Access
Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
Dim da As New SqlDataAdapter(ss, cs) 'cs is my connectionstring
da.TableMappings.Add("Table","Customers")
da.TableMappings.Add("Table1","Orders")
Dim ds as New DataSet()
da.Fill(ds)

'now add the foreign key constraint
Dim fk as ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))

Maybe if you play around with the code in this example,
you can figure out some way to get it to work. If you want
more help, you're going to need to post some code.

Robin S.
----------------
 
I'm sorry, but I really can't solve the problem.
I don't have any relationships defined in Access, neither I have set in
Dataset Designer.
Trying to use your code, I'm in trouble about connection string, this is
mine (I put the code in a Form_Load environment):
Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
I get an error in "Provider", it's not recognized by SqlDataAdapter.

I have this code:

Public Class Form_modPrest1
Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
TblPrest1BindingNavigatorSaveItem.Click
Me.Validate()
Me.TblPrest1BindingSource.EndEdit()
Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSet.tblPrest1)
End Sub
Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tblPrest1)

This cames automatically from dragging the tblPrest1 from dataset to the
form, in a datagrid format. I put your code under last line. And it doesn't
work.
Because I have other four tables identical to this, I get also five forms
identical, changing only the names of form (modPrest2...) and names of
tables (tblPrest2...)
Then I load these forms by a menu, and I see the contents of all tables in
datagrid format.
Then I make a change in the tblPrest1, and save the change by clicking in
the floppy disk icon. When I reload the table, changes are effectively in
use.
It seems this procedure already fill the table in dataset... or not?
I can't understand how automatically changing the other four tables without
calling each one by one.
I'm sorry for this but I'm really at the beginning about database
programming.
Please help and thanks for your patience.
 
Argh. Sorry; I didn't realize you were going to use my dataset
code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
But you don't need that stuff if you're using a DataSet that
you set up in your DataSet designer; you only need the code defining
the Foreign Key Restraint. I included the other just to show
position.

I'm assuming you have *two* datatables in your dataset, one
being the parent and one being the child? Your code only
seems to fill one table. Where is the other one?

ds is my dataset name.
Customers is my parent table.
Orders is my child table.
CustomerID is the field in both tables that relates them
to each other.

If you still can't figure it out, please re-post your code
showing the filling of the tables, probably in your form_load
routine.

And if you post more code, if you paste it into notepad and
then copy and paste it into your posting, it will be
indented properly and be easier to read.

Robin S.
-----------------------
 
Ok. I loaded in this form the secon table, too, and I fill it (I hope). Then
nothing, the second table remians unchanged. I'm really getting crazy.
This is the code:

Public Class Form_modPrest1
Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
TblPrest1BindingNavigatorSaveItem.Click
Me.Validate()
Me.TblPrest1BindingSource.EndEdit()
Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSet.tblPrest1)
End Sub
Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles MyBase.Load
Me.TblPrest2TableAdapter.Fill(Me.GestPrestDataSet.tblPrest2)
'TODO: questa riga di codice carica i dati nella tabella
'GestPrestDataSet.tblPrest1'. È possibile spostarla o rimuoverla se
necessario.
Me.TblPrest1TableAdapter.Fill(Me.GestPrestDataSet.tblPrest1)
Dim fk As ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_tbl1_tbl2", _
GestPrestDataSet.Tables("tblPrest1").Columns("NomePrest"), _
GestPrestDataSet.Tables("tblPrest2").Columns("NomePrest"))
End Sub
End Class

Thanks Robin

RobinS said:
Argh. Sorry; I didn't realize you were going to use my dataset
code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
But you don't need that stuff if you're using a DataSet that
you set up in your DataSet designer; you only need the code defining
the Foreign Key Restraint. I included the other just to show
position.

I'm assuming you have *two* datatables in your dataset, one
being the parent and one being the child? Your code only
seems to fill one table. Where is the other one?

ds is my dataset name.
Customers is my parent table.
Orders is my child table.
CustomerID is the field in both tables that relates them
to each other.


If you still can't figure it out, please re-post your code
showing the filling of the tables, probably in your form_load
routine.

And if you post more code, if you paste it into notepad and
then copy and paste it into your posting, it will be
indented properly and be easier to read.

Robin S.
Click to expand...
 
I forgot to tell you to add the constraint to the table definition.
Oops. Here's some code, with the line(s) I missed (look for the
asterisks).

Here's a working example. I set up a dataset for Northwind
with Customers and Orders and deleted the constraint that was
generated so I could create one in code. Customers corresponds
to your table1; Orders corresponds to your table2.

I didn't try modifying any of my data because I don't want to muck
up my data, but it should work. (famous last words)

----------
Dim ds As NorthwindDataSet = New NorthwindDataSet()
Dim custAdapter As CustomersTableAdapter = New CustomersTableAdapter()
custAdapter.Fill(ds.Customers)
Dim orderAdapter As OrdersTableAdapter = New OrdersTableAdapter()
orderAdapter.Fill(ds.Orders)

Dim fk As ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK_Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))

'***THIS IS THE STEP I FORGOT TO INCLUDE -- Oops!
ds.Tables("Orders").Constraints.Add(fk)
ds.EnforceConstraints = True

'Note: You can use the data relation created by the fk
' constraint to read through your data:

Dim rowCustomer As NorthwindDataSet.CustomersRow
Dim rowOrder As NorthwindDataSet.OrdersRow
Dim rel As DataRelation = ds.Relations("FK_Customers_Orders")
For Each rowCustomer In ds.Customers
Console.WriteLine("Orders for {0}", rowCustomer.CompanyName)
For Each rowOrder In rowCustomer.GetChildRows(rel)
Console.WriteLine(" {0} - {1:d}", rowOrder.OrderID,
rowOrder.OrderDate)
Next rowOrder
Next rowCustomer

And here's something else I figured out: You *can* add the constraint
to your DataSet in the DataSet Designer.

To add a DataRelation, right-click on any item in the DataSet designer
(like in your table definition) and select Add and then Relation.

You can select the parent and child tables using the drop-down lists
at the top of the dialog box, and then the datacolumns just below
that.

The lower half of that dialog box lets you control whether the action
will create a DataRelation, a ForeignKeyConstraint, or both. The
default is just DataRelation, but you should choose ForeignKey or
Both. You can also specify values for the UpdateRule, DeleteRule,
and AcceptRejectChangesRule on the FK constraint. It defaults to
Cascade, Cascade, and None, which is fine.

If you have all of your tables in one DataSet that you created
through the DAtaSet designer, you can try adding the constraints
there. If you ever re-create the DataSet, you have to remember
to add the constraints, too, though.

Hope this helps.
Robin S.
-----------------------------------------------------------------
 
I'm still trying Robin, but seems I cannot fill the second tabel after
implementing your code in my form_load. I see it after the console.writeline
routine. Data are present only in 'tblPrest1' tabel, while, moving mouse
around the console window, I see 'nothing' when I go to examinate tblPrest2'
table. In effect, the program skips the section regarding 'each
rowTblPrest2...', because the value is 'nothing'. But I can't understand
why.
Anyway, I used the DataSet designer as the very first time to create
relationships, but it didn't work; I didn't get error messages, just no
change in 'tblPrest2'. Later, looking in newsgroup, I found somebody talking
about it's not possible to create it in this ** friendly ** way, but only by
writing code...
Thanks. I'll let you know.
PS: Are you looking at the newsgroup also in these 'holidays days'?
 
I don't understand why you can't fill both tables, either. Are you
filling both tables *before* adding the constraint?

One thing is, it's a *constraint*, so every entry in the child table
*must* have a corresponding entry in the parent table. IS that true?

Yes, I'll be around during the holidays.
Robin S.
 
The two tables are identical, 73 record, with 3 columns each, prepared in
Access. As I post before, the tables are filled by default by DataSet
designer, before the constraint by this code:

Me.TblPrest1TableAdapter.Fill(Me.GestPrestDataSet.tblPrest1)
Me.TblPrest2TableAdapter.Fill(Me.GestPrestDataSet.tblPrest2)

I'll back to program on tuesday, but I'll take a look at the ng. I'll let
you know.
Thanks and Merry Christmas.
Max
 
max said:
Hello,
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.
Click to expand...
<snip>

I really didn't get what you're trying. And it seems you're mistaking
what a foreign key really is.
From what I got you want five tables identical to each other that get
Click to expand...
updated when one of then is updated. This design seems very odd to me
(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.
 
Thanks Branco,
probably I'm talking about FK without knowing exactly what a FK is. But, if
it is the case, it's a misunderstanding coming from a lot of confusion
generated by reading too many manuals, newsgroups, online help and much
more.
I wish to explain why I need five identicals tables. I want write an
application based on medical issues; in a single medical session, a single
patient could have many different medical visits (I extimated a max number
of five), because in the medical office there are many different doctors
making different visits. So, for example, Mr. Smith, in the same day, could
have three different visits (cardiologist, dentist and dermatologist). In a
form, I have a box for the patients (coming from a 'patient' table), the
date, and the five possible visits that the patient could have in that day.
In these five tables (called tblPrest1... tblPrest5), there are three
columns: a progressive ID of the medical visit, the kind of medical visit,
and the price related. If the boss wants to change the price of most of the
medical visits, for example in the new year, I want that the operator
changes it just in a table and replicate the change in all other four
tables, without going to change the price one table at a time.
The five tables was already created in Access and filled with the actual
medical visits and related price. I'm making an application in vb2005 that
connects to the .mdb file (and it already works), and permits at the
operators the updating, deleting and importing new records as needed.
At this point, after reading your answer, I really don't know if using FK is
what I really need. Effectively, using relations, I noted the pk issues you
described and, because the operators may need to change either the price and
the type of medical visit when needed, I can realize that it's probably not
possible using relations.
The question is: which different methods can I use to achieve this?
I'm sorry with RobinS if I omitted this long explanation.
Thanks,
Max
 
Max wrote:
<back posted/>

It seems to me that there are many approaches to your problem. The
separate tables, although looking practical, would have some serious
issues. One of them is the inhability to automatically update all the
other tables in response to changes in one of the them. Other issue is
the possibility of a patient having more than five visists in a given
day (who knows, sh*te happens...). Even if there are less than five
visits, you'd have the issue of the ID not being synchronized between
tables. A maintenance hell, IMHO.

I didn't understand very well the issue of the visit pricing, but it
seems you may keep everything in a single Visit table and decouple the
items that would be common to all the visits of a given patient in a
single day and put then in a separate table. Something in the likes of:

Visit table:
VisitID (PK)
PatientID (FK to the patients table)
VisitKind (probably an FK to a simple visit description table)
VisitInfoID (an FK to a VisitInfo table)
... other fields unique to a visit, such as visit time, doctor name,
duration, etc

VisitInfo table:
VisitInfoID (PK)
VisitDate
VisitPrice
... other fields common to all visits of a patient in a given day

HTH.


Regards,

Branco.
Thanks Branco,
probably I'm talking about FK without knowing exactly what a FK is. But, if
it is the case, it's a misunderstanding coming from a lot of confusion
generated by reading too many manuals, newsgroups, online help and much
more.
I wish to explain why I need five identicals tables. I want write an
application based on medical issues; in a single medical session, a single
patient could have many different medical visits (I extimated a max number
of five), because in the medical office there are many different doctors
making different visits. So, for example, Mr. Smith, in the same day, could
have three different visits (cardiologist, dentist and dermatologist). In a
form, I have a box for the patients (coming from a 'patient' table), the
date, and the five possible visits that the patient could have in that day.
In these five tables (called tblPrest1... tblPrest5), there are three
columns: a progressive ID of the medical visit, the kind of medical visit,
and the price related. If the boss wants to change the price of most of the
medical visits, for example in the new year, I want that the operator
changes it just in a table and replicate the change in all other four
tables, without going to change the price one table at a time.
The five tables was already created in Access and filled with the actual
medical visits and related price. I'm making an application in vb2005 that
connects to the .mdb file (and it already works), and permits at the
operators the updating, deleting and importing new records as needed.
At this point, after reading your answer, I really don't know if using FK is
what I really need. Effectively, using relations, I noted the pk issues you
described and, because the operators may need to change either the price and
the type of medical visit when needed, I can realize that it's probably not
possible using relations.
The question is: which different methods can I use to achieve this?
I'm sorry with RobinS if I omitted this long explanation.
Thanks,
Max
Click to expand...
<snip>
 
Branco Medeiros wrote:
<back posted/>

Well, the max number of visits (5) in a single day has decided by the boss
of the office, so it would be ok to me, too. So, it's not a problem of mine.
:) I don't understand why, if I have less than five visits, I don't have ID
synchronized between tables. The five tables are identicals from the
beginning, and the ID is always the same in the other tables, so every
changes in a record in one table should replicate to the others...
About the visit pricing: there are more than 70 different type of medical
visits, and each one has a price. So in a row, I have:
VisitID;
Price;
VisitType.
Just three columns.
If the boss decides to change the prices of, for i.e., 45 type of medical
visits, the changes must reflect to the other four tables.
More over these 'famous' five tables, there are only three more tables:
The Patients table (just ID, and name and surname);
The Doctors table (just ID, and name and surname);
The Visit table (called by a form) would consist of:
VisitID;
Date;
Patient name;
VisitType 1...VisitType 5 (combo box; if there are less than 5 visits, the
other combo box will be blank);
Doctor's name.

That's it.
I just need to change the Price column in one table, and make automatically
the changes (update, delete or insert) in the other four tables.
It sounds simple...
Thanks,
Max
 
Forgot to describe how I'm planning to make the changes in the five tables.
I just call the form from a menu 'Tools' (something like that:
Menu---'Tools'---'Change Visit table'), with the datagrid dragged on it,
binding to the first table. I have already made this, and this works fine
for the first table.
 
Max said:
Well, the max number of visits (5) in a single day has decided by the boss
of the office, so it would be ok to me, too. So, it's not a problem of mine.
:) I don't understand why, if I have less than five visits, I don't have ID
synchronized between tables. The five tables are identicals from the
beginning, and the ID is always the same in the other tables, so every
changes in a record in one table should replicate to the others...
Click to expand...

It will always depend on how you generate these IDs (I must warn you
that there are lots of lines of thought in this matter, every one
claiming to be the definitive one).

Personaly, I prefer having a table's ID/PK as an identity column
managed by the system (an autoincrement column, in Access), . Other
people may prefer to generate the keys themselves. Others still will
prefer to use something completely meaningless, say, a GUID, or
whatever (btw, I am, too, among the ones that think that the PK is
meant *only* to pinpoint a given record, but I prefer using a
light-weight identity column for that).

Autoincrement IDs are completely ruled out, for you. Because they're
managed by the DB engine, as soon as you have a situation with, say,
three visits only, the two other tables will become out of synch with
the other three. And it will only get worse after that.

GUIDs are ruled out also, because, while autoincrement IDs are local to
each table, GUIDs are globally unique among every table, so you can't
have the same GUID for different tables (or so it seems. Forgive me if
I'm mistaken).

Finally, your only choice seems to be to generate your own keys, or
have a mix of tables with hand-made keys and one of the tables with
auto-generated keys (but which one?). Hand generated IDs have their
charm, but have some caveats. Most importantly, their generation logic
is left to the user, instead of remaining hidden inside the DB, and
this may give rise to inconsistencies and, ultimately, warts,
dizzyness, loss of sexual performance, the hell on earth... (=))) you
see I'm *a little* biased, here).
About the visit pricing: there are more than 70 different type of medical
visits, and each one has a price.
So in a row, I have:
VisitID;
Price;
VisitType.
Just three columns.
Click to expand...

Is this the table with information about each type of visit?
If the boss decides to change the prices of, for i.e., 45 type of medical
visits, the changes must reflect to the other four tables.
Click to expand...

Forgive my dumbness: I still don't get it. Are these prices supposed to
change (if they ever do) for a given visit, only? Or would they change
for a whole category of visits? Or, still, would the prices change only
for a given day (a Christmass promo, for instance =))) ?
More over these 'famous' five tables, there are only three more tables:
The Patients table (just ID, and name and surname);
The Doctors table (just ID, and name and surname);
The Visit table (called by a form) would consist of:
VisitID;
Date;
Patient name;
VisitType 1...VisitType 5 (combo box; if there are less than 5 visits, the
other combo box will be blank);
Doctor's name.

That's it.
I just need to change the Price column in one table, and make automatically
the changes (update, delete or insert) in the other four tables.
It sounds simple...
Click to expand...
<snip>

It seems you want the price to be the same for all the visits of a
given patient in a given day.

Idealy, this would call for a master/detail relation:

Visit table: VisitID, PatientID, Price, Date

VisitDetail table: VisitDetailID, VisitID (the FK), VisitType, DoctorID


Alternatively (oh, the blasphemy!), you could have:

VisitID, PatientID, Price, Date, Visit1Type, Visit1Doctor, ... ,
Visit5Type, Visit5Doctor

HTH.

Regards,

Branco.
 
Branco,

Do you allow me another approach,

A VisitTable with in it at least
PatientID related as child to PatientTable
DoctorID related as child to DoctorTable
DateTimeOfVisit
NumberOfVisits from 1 to 5 (just to make it understandable)
PrijsID related as child to PriceTable

The other tables including PriceTable where in the latter should not be
forgotten the starting date of the price as a column. Otherwise the system
is to dependend from actual time and date that those are added to the table.

All is than working using the relation although the price should be use a
dataview to get the correct one.

Seems to me a quiet standard approach for a small database.

Cor
 
Back
Top