|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Dataset ForeignKey Constrainta second numeric field. The second numeric field can refer to another dataRow in the same table (or can be left "blank" with a default value of 0). It's similar to the Employees table that might contain Manager Mike and Employee Joe: ID Name ManagerID 1 Mike 0 2 Joe 1 Since I'm adding dataRows to later be submitted to a database (where I won't know the value of the PrimaryKey until the record is actually inserted) I think I need a ForeignKey relation that goes from and to my datatable linking the ID and ManagerID columns. The problem is, when I do this, I can't insert the initial row because it tells me that the ManagerID value of 0 doesn't reference an existing row where ID = 0. I'm a little lost. I tried turning off EnforceConstraints but that doesn't cascade the changes when I insert rows into my database. What is the best practice for a situation like this? Thanks in advance. 0 is a perfectly legal value and as you asked for integrity....
Instead most if not all DBs are using the NULL marker (this is DBNull.Value ..NET side) to note "unused" values... Try : http://en.wikipedia.org/wiki/Null_%28SQL%29 -- Patrice "JohnMSyrasoft" <JohnMSyras***@discussions.microsoft.com> a écrit dans le message de news: AE98A247-7F0C-4A92-B897-981B6867F***@microsoft.com...Show quote > Hi, I have a dataTable that contains both an autonumber primarykey field > and > a second numeric field. The second numeric field can refer to another > dataRow in the same table (or can be left "blank" with a default value of > 0). > > It's similar to the Employees table that might contain Manager Mike and > Employee Joe: > > ID Name ManagerID > 1 Mike 0 > 2 Joe 1 > > Since I'm adding dataRows to later be submitted to a database (where I > won't > know the value of the PrimaryKey until the record is actually inserted) I > think I need a ForeignKey relation that goes from and to my datatable > linking > the ID and ManagerID columns. The problem is, when I do this, I can't > insert > the initial row because it tells me that the ManagerID value of 0 doesn't > reference an existing row where ID = 0. > > I'm a little lost. I tried turning off EnforceConstraints but that > doesn't > cascade the changes when I insert rows into my database. > > What is the best practice for a situation like this? Thanks in advance. My issue isn't with what happens when my data is written to the database.
The question is about adding new dataRows to a table with the described constraint. I can't add (using the example above), a "Manager" row, that is a row that specifies a ManagerID that doesn't exist in the dataTable. Any other thoughts? Show quote "Patrice" wrote: > 0 is a perfectly legal value and as you asked for integrity.... > > Instead most if not all DBs are using the NULL marker (this is DBNull.Value > ..NET side) to note "unused" values... > > Try : > http://en.wikipedia.org/wiki/Null_%28SQL%29 > > -- > Patrice > > "JohnMSyrasoft" <JohnMSyras***@discussions.microsoft.com> a écrit dans le > message de news: AE98A247-7F0C-4A92-B897-981B6867F***@microsoft.com... > > Hi, I have a dataTable that contains both an autonumber primarykey field > > and > > a second numeric field. The second numeric field can refer to another > > dataRow in the same table (or can be left "blank" with a default value of > > 0). > > > > It's similar to the Employees table that might contain Manager Mike and > > Employee Joe: > > > > ID Name ManagerID > > 1 Mike 0 > > 2 Joe 1 > > > > Since I'm adding dataRows to later be submitted to a database (where I > > won't > > know the value of the PrimaryKey until the record is actually inserted) I > > think I need a ForeignKey relation that goes from and to my datatable > > linking > > the ID and ManagerID columns. The problem is, when I do this, I can't > > insert > > the initial row because it tells me that the ManagerID value of 0 doesn't > > reference an existing row where ID = 0. > > > > I'm a little lost. I tried turning off EnforceConstraints but that > > doesn't > > cascade the changes when I insert rows into my database. > > > > What is the best practice for a situation like this? Thanks in advance. > > > I meant that if a user doesn't have a manager you should use DBNull.Value
(.NET side) so that NULL will be written in the DB... The rule of the DB and the DataTable constraints are the same, that if if the field have a value the constraint should be enforced, if the field doesn't have a value the constraint is considered satisfied... -- Patrice "JohnMSyrasoft" <JohnMSyras***@discussions.microsoft.com> a écrit dans le message de news: A4A9A02D-8423-49A3-A1D7-62F359497***@microsoft.com...Show quote > My issue isn't with what happens when my data is written to the database. > The question is about adding new dataRows to a table with the described > constraint. I can't add (using the example above), a "Manager" row, that > is > a row that specifies a ManagerID that doesn't exist in the dataTable. > > Any other thoughts? > > "Patrice" wrote: > >> 0 is a perfectly legal value and as you asked for integrity.... >> >> Instead most if not all DBs are using the NULL marker (this is >> DBNull.Value >> ..NET side) to note "unused" values... >> >> Try : >> http://en.wikipedia.org/wiki/Null_%28SQL%29 >> >> -- >> Patrice >> >> "JohnMSyrasoft" <JohnMSyras***@discussions.microsoft.com> a écrit dans le >> message de news: AE98A247-7F0C-4A92-B897-981B6867F***@microsoft.com... >> > Hi, I have a dataTable that contains both an autonumber primarykey >> > field >> > and >> > a second numeric field. The second numeric field can refer to another >> > dataRow in the same table (or can be left "blank" with a default value >> > of >> > 0). >> > >> > It's similar to the Employees table that might contain Manager Mike and >> > Employee Joe: >> > >> > ID Name ManagerID >> > 1 Mike 0 >> > 2 Joe 1 >> > >> > Since I'm adding dataRows to later be submitted to a database (where I >> > won't >> > know the value of the PrimaryKey until the record is actually inserted) >> > I >> > think I need a ForeignKey relation that goes from and to my datatable >> > linking >> > the ID and ManagerID columns. The problem is, when I do this, I can't >> > insert >> > the initial row because it tells me that the ManagerID value of 0 >> > doesn't >> > reference an existing row where ID = 0. >> > >> > I'm a little lost. I tried turning off EnforceConstraints but that >> > doesn't >> > cascade the changes when I insert rows into my database. >> > >> > What is the best practice for a situation like this? Thanks in >> > advance. >> >> >> Thanks, Patrice! That was exactly what I needed.
When I add my original row, I leave the ManagerID column in the dataTable as DBNull (using Nothing). This allows the constraint check to succeed. When I submit to the database, I catch the newly inserted autoNumber value and update the dataTable. These changes casade to any related rows. Exactly what I needed to get going. Thanks again. Show quote "Patrice" wrote: > I meant that if a user doesn't have a manager you should use DBNull.Value > (.NET side) so that NULL will be written in the DB... > > The rule of the DB and the DataTable constraints are the same, that if if > the field have a value the constraint should be enforced, if the field > doesn't have a value the constraint is considered satisfied... > > -- > Patrice > > "JohnMSyrasoft" <JohnMSyras***@discussions.microsoft.com> a écrit dans le > message de news: A4A9A02D-8423-49A3-A1D7-62F359497***@microsoft.com... > > My issue isn't with what happens when my data is written to the database. > > The question is about adding new dataRows to a table with the described > > constraint. I can't add (using the example above), a "Manager" row, that > > is > > a row that specifies a ManagerID that doesn't exist in the dataTable. > > > > Any other thoughts? > > > > "Patrice" wrote: > > > >> 0 is a perfectly legal value and as you asked for integrity.... > >> > >> Instead most if not all DBs are using the NULL marker (this is > >> DBNull.Value > >> ..NET side) to note "unused" values... > >> > >> Try : > >> http://en.wikipedia.org/wiki/Null_%28SQL%29 > >> > >> -- > >> Patrice > >> > >> "JohnMSyrasoft" <JohnMSyras***@discussions.microsoft.com> a écrit dans le > >> message de news: AE98A247-7F0C-4A92-B897-981B6867F***@microsoft.com... > >> > Hi, I have a dataTable that contains both an autonumber primarykey > >> > field > >> > and > >> > a second numeric field. The second numeric field can refer to another > >> > dataRow in the same table (or can be left "blank" with a default value > >> > of > >> > 0). > >> > > >> > It's similar to the Employees table that might contain Manager Mike and > >> > Employee Joe: > >> > > >> > ID Name ManagerID > >> > 1 Mike 0 > >> > 2 Joe 1 > >> > > >> > Since I'm adding dataRows to later be submitted to a database (where I > >> > won't > >> > know the value of the PrimaryKey until the record is actually inserted) > >> > I > >> > think I need a ForeignKey relation that goes from and to my datatable > >> > linking > >> > the ID and ManagerID columns. The problem is, when I do this, I can't > >> > insert > >> > the initial row because it tells me that the ManagerID value of 0 > >> > doesn't > >> > reference an existing row where ID = 0. > >> > > >> > I'm a little lost. I tried turning off EnforceConstraints but that > >> > doesn't > >> > cascade the changes when I insert rows into my database. > >> > > >> > What is the best practice for a situation like this? Thanks in > >> > advance. > >> > >> > >> > > >  |
|||||||||||||||||||||||
Other interesting topics