ForeignKeyConstraint要求子键值(0)必须存在于父表中 [英] ForeignKeyConstraint requires the child key values (0) to exist in the parent table
本文介绍了ForeignKeyConstraint要求子键值(0)必须存在于父表中的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我无法插入ROW主文件-详细信息.
I can`t INSERT ROW master - details.
private string strSelectComprobante()
{
string strSQL;
strSQL = " SELECT ";
strSQL += " TbComprobante.NroCpte,";
strSQL += " TbComprobante.TipoCpte,";
strSQL += " TbComprobante.Fecha,"; //0
strSQL += " TbComprobante.Cliente,"; //1
strSQL += " TbComprobante.Usuario,";
strSQL += " TbComprobante.Descripcion";
strSQL += " FROM";
strSQL += " TbComprobante";
strSQL += " WHERE";
strSQL += " TbComprobante.TipoCpte = 'S'";
if (!blnTodos)
{
strSQL += " AND TbComprobante.NroCpte = 0 ";
}
return strSQL;
}
private string strUpdateComprobante()
{
string strSQL;
strSQL = " UPDATE TbComprobante SET ";
strSQL += " TbComprobante.Fecha = ?Fecha,"; //0
strSQL += " TbComprobante.Cliente = ?Cliente,"; //1
strSQL += " TbComprobante.Usuario = ?Usuario,";
strSQL += " TbComprobante.Descripcion = ?Descripcion";
strSQL += " WHERE";
strSQL += " TbComprobante.NroCpte = ?NroCpte";
strSQL += " AND TbComprobante.TipoCpte = 'S'";
return strSQL;
}
private string strInsertComprobante()
{
string strSQL;
strSQL = " INSERT INTO TbComprobante ( ";
strSQL += " NroCpte,"; //0
strSQL += " TipoCpte,";
strSQL += " Fecha,"; //2
strSQL += " Cliente,"; //3
strSQL += " Usuario,";
strSQL += " Descripcion)";
strSQL += " VALUES(";
strSQL += " ?NroCpte,"; //0
strSQL += " ?TipoCpte,";
strSQL += " ?Fecha,"; //2
strSQL += " ?Cliente,"; //3
strSQL += " ?Usuario,";
strSQL += " ?Descripcion)";
return strSQL;
}
private string strSelectDetalle()
{
string strSQL;
strSQL = " SELECT ";
strSQL += " TbDetalle.NroCpte,"; //0
strSQL += " TbDetalle.NroDetalle,"; //1
strSQL += " TbDetalle.TipoCpte,";
strSQL += " TbDetalle.Producto,"; //2
strSQL += " TbProducto.Denominacion,";
strSQL += " TbProducto.Unidad,";
strSQL += " TbDetalle.CostoUnt,"; //5
strSQL += " TbProducto.PrecioUnt,";
strSQL += " TbDetalle.Cantidad,";
strSQL += " TbDetalle.Descuento,";
strSQL += " TbDetalle.Monto";
strSQL += " FROM";
strSQL += " TbDetalle";
strSQL += " INNER JOIN TbProducto ON";
strSQL += " TbDetalle.Producto = TbProducto.Producto";
strSQL += " WHERE";
strSQL += " TbDetalle.TipoCpte = 'S'";
if (!blnTodos)
{
strSQL += " AND TbDetalle.NroCpte = 0 ";
}
strSQL += " ORDER BY";
strSQL += " TbDetalle.Producto";
return strSQL;
}
private string strUpdateDetalle()
{
string strSQL;
strSQL = " UPDATE TbDetalle SET ";
strSQL += " TbDetalle.TipoCpte = 'S',";
strSQL += " TbDetalle.CostoUnt = ?CostoUnt,"; //3
strSQL += " TbDetalle.PrecioUnt = ?PrecioUnt,";
strSQL += " TbDetalle.Cantidad = ?Cantidad,";
strSQL += " TbDetalle.Descuento = ?Descuento,";
strSQL += " TbDetalle.Monto = ?Monto";
strSQL += " WHERE";
strSQL += " TbDetalle.NroCpte = ?NroCpte";
strSQL += " AND TbDetalle.TipoCpte = 'S'";
strSQL += " AND TbDetalle.NroDetalle = ?NroDetalle";
return strSQL;
}
private string strDeleteDetalle()
{
string strSQL;
strSQL = " DELETE FROM TbDetalle ";
strSQL += " WHERE";
strSQL += " TbDetalle.NroCpte = ?NroCpte";
strSQL += " AND TbDetalle.TipoCpte = 'S'";
strSQL += " AND TbDetalle.NroDetalle = ?NroDetalle";
return strSQL;
}
private string strInsertDetalle()
{
string strSQL;
strSQL = " INSERT INTO TbDetalle ( ";
strSQL += " TbDetalle.NroCpte,"; //0
strSQL += " TbDetalle.NroDetalle,";
strSQL += " TbDetalle.TipoCpte,";
strSQL += " TbDetalle.Producto,"; //2
strSQL += " TbDetalle.CostoUnt,"; //5
strSQL += " TbDetalle.PrecioUnt,";
strSQL += " TbDetalle.Cantidad,";
strSQL += " TbDetalle.Descuento,";
strSQL += " TbDetalle.Monto)";
strSQL += " VALUES(";
strSQL += " ?NroCpte,";
strSQL += " ?NroDetalle,";
strSQL += " 'S',";
strSQL += " ?Producto,";
strSQL += " ?CostoUnt,";
strSQL += " ?PrecioUnt,";
strSQL += " ?Cantidad,";
strSQL += " ?Descuento,";
strSQL += " ?Monto)";
return strSQL;
}
private int GrabarNroCpte(long intNroCpte)
{
string strSQL;
MySqlCommand MyCmd;
int intRowsAfects = 0;
strSQL = " UPDATE TbNroCpte";
strSQL += " SET NroCpte = " + intNroCpte;
strSQL += " WHERE TipoCpte = 'S'";
try
{
MyCmd = new MySqlCommand();
MyCmd.Connection = Modulos.ClsGlobal.MyNetCnx;
MyCmd.CommandText = strSQL;
intRowsAfects = MyCmd.ExecuteNonQuery();
return intRowsAfects;
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "GrabarNroCpte", MessageBoxButtons.OK, MessageBoxIcon.Information);
return intRowsAfects;
}
}
private int GrabarComprobante(long intNroCpte)
{
int intRowsAfects = 0;
try
{
//Actualiza comprobante
CMDComprobante = new MySqlCommand(strUpdateComprobante(), Modulos.ClsGlobal.MyNetCnx);
CMDComprobante.Parameters.Add("?Fecha", MySqlDbType.Datetime);
CMDComprobante.Parameters["?Fecha"].Value = DtFecha.Value;
CMDComprobante.Parameters.Add("?Cliente", MySqlDbType.VarChar, 50, "Cliente");
CMDComprobante.Parameters.Add("?Usuario", MySqlDbType.VarChar, 5, "Usuario");
CMDComprobante.Parameters.Add("?Descripcion", MySqlDbType.VarChar, 100, "Descripcion");
CMDComprobante.Parameters.Add("?NroCpte", MySqlDbType.Int32, 4, "NroCpte").SourceVersion = DataRowVersion.Original;
SDAComprobante.UpdateCommand = CMDComprobante;
//Inserta comprobante
CMDComprobante = new MySqlCommand(strInsertComprobante(), Modulos.ClsGlobal.MyNetCnx);
CMDComprobante.Parameters.Add("?Fecha", MySqlDbType.Datetime);
CMDComprobante.Parameters["?Fecha"].Value = DtFecha.Value;
CMDComprobante.Parameters.Add("?Cliente", MySqlDbType.VarChar, 50, "Cliente");
CMDComprobante.Parameters.Add("?Usuario", MySqlDbType.VarChar, 5, "Usuario");
CMDComprobante.Parameters.Add("?Descripcion", MySqlDbType.VarChar, 100, "Descripcion");
CMDComprobante.Parameters.Add("?NroCpte", MySqlDbType.Int32, 4);
CMDComprobante.Parameters["?NroCpte"].Value = intNroCpte;
SDAComprobante.InsertCommand = CMDComprobante;
DataTable UpdateComprobante;
UpdateComprobante = dsData.Tables["Comprobante"].GetChanges(DataRowState.Modified);
if (UpdateComprobante != null)
{
intRowsAfects = SDAComprobante.Update(UpdateComprobante);
dsData.Tables["Comprobante"].AcceptChanges();
}
DataTable InsertComprobante;
InsertComprobante = dsData.Tables["Comprobante"].GetChanges(DataRowState.Added);
if (InsertComprobante != null)
{
intRowsAfects = SDAComprobante.Update(InsertComprobante);
dsData.Tables["Comprobante"].AcceptChanges();
}
return intRowsAfects;
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "GrabarComprobante", MessageBoxButtons.OK, MessageBoxIcon.Information);
return intRowsAfects;
}
}
private int GrabarDetalle(long intNroCpte)
{
int intRowsAfects = 0;
try
{
//Actualiza Detalle
CMDDetalle = new MySqlCommand(strUpdateDetalle(), Modulos.ClsGlobal.MyNetCnx);
CMDDetalle.Parameters.Add("?NroDetalle", MySqlDbType.Int32, 4, "NroDetalle");
CMDDetalle.Parameters.Add("?Producto", MySqlDbType.VarChar, 10, "Producto");
CMDDetalle.Parameters.Add("?CostoUnt", MySqlDbType.Decimal, 7, "CostoUnt");
CMDDetalle.Parameters.Add("?PrecioUnt", MySqlDbType.Decimal, 2, "PrecioUnt");
CMDDetalle.Parameters.Add("?Cantidad", MySqlDbType.Int32, 4, "Cantidad");
CMDDetalle.Parameters.Add("?Descuento", MySqlDbType.Int32, 3, "Descuento");
CMDDetalle.Parameters.Add("?Monto", MySqlDbType.Decimal, 2, "Monto");
CMDDetalle.Parameters.Add("?NroCpte", MySqlDbType.Int32, 4, "NroCpte");
SDADetalle.UpdateCommand = CMDDetalle;
//Eliminar fila de Detalle
CMDDetalle = new MySqlCommand(strDeleteDetalle(), Modulos.ClsGlobal.MyNetCnx);
CMDDetalle.Parameters.Add("?NroCpte", MySqlDbType.Int32, 4, "NroCpte");
CMDDetalle.Parameters.Add("?NroDetalle", MySqlDbType.Int32, 4, "NroDetalle");
SDADetalle.DeleteCommand = CMDDetalle;
//Insertar fila en Detalle
CMDDetalle = new MySqlCommand(strInsertDetalle(), Modulos.ClsGlobal.MyNetCnx);
CMDDetalle.Parameters.Add("?NroCpte", MySqlDbType.Int32, 4);
CMDDetalle.Parameters["?NroCpte"].Value = intNroCpte;
CMDDetalle.Parameters.Add("?NroDetalle", MySqlDbType.Int32, 4,"NroDetalle");
CMDDetalle.Parameters.Add("?Producto", MySqlDbType.VarChar, 10, "Producto");
CMDDetalle.Parameters.Add("?CostoUnt", MySqlDbType.Decimal, 7, "CostoUnt");
CMDDetalle.Parameters.Add("?PrecioUnt", MySqlDbType.Decimal, 2, "PrecioUnt");
CMDDetalle.Parameters.Add("?Cantidad", MySqlDbType.Int32, 4, "Cantidad");
CMDDetalle.Parameters.Add("?Descuento", MySqlDbType.Int32, 3, "Descuento");
CMDDetalle.Parameters.Add("?Monto", MySqlDbType.Decimal, 2, "Monto");
SDADetalle.InsertCommand = CMDDetalle;
DataTable ChangeRecordsDetalle;
ChangeRecordsDetalle = dsData.Tables["Detalle"].GetChanges(DataRowState.Modified);
if (ChangeRecordsDetalle != null)
{
intRowsAfects = SDADetalle.Update(ChangeRecordsDetalle);
dsData.Tables["Detalle"].AcceptChanges();
}
DataTable DeleteRecordsDetalle;
DeleteRecordsDetalle = dsData.Tables["Detalle"].GetChanges(DataRowState.Deleted);
if (DeleteRecordsDetalle != null)
{
intRowsAfects = SDADetalle.Update(DeleteRecordsDetalle);
dsData.Tables["Detalle"].AcceptChanges();
}
DataTable InsertRecordsDetalle;
InsertRecordsDetalle = dsData.Tables["Detalle"].GetChanges(DataRowState.Added);
if (InsertRecordsDetalle != null)
{
intRowsAfects = SDADetalle.Update(InsertRecordsDetalle);
dsData.Tables["Detalle"].AcceptChanges();
}
return intRowsAfects;
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "GrabarDetalle", MessageBoxButtons.OK, MessageBoxIcon.Information);
return intRowsAfects;
}
}
private Boolean CrearDSRegSalida()
{
this.Cursor = Cursors.WaitCursor;
MySqlCommand CMDComprobante;
try
{
blnTodos = false;
CMDComprobante = new MySqlCommand(strSelectComprobante(), Modulos.ClsGlobal.MyNetCnx);
SDAComprobante = new MySqlDataAdapter();
SDAComprobante.MissingSchemaAction = MissingSchemaAction.AddWithKey;
SDAComprobante.SelectCommand = CMDComprobante;
SDAComprobante.FillSchema(dtComprobante, SchemaType.Source);
SDAComprobante.Fill(dtComprobante);
SDADetalle = new MySqlDataAdapter(strSelectDetalle(), Modulos.ClsGlobal.MyNetCnx);
SDADetalle.FillSchema(dtDetalle, SchemaType.Mapped);
SDADetalle.Fill(dtDetalle);
dsData.Tables.Add(dtComprobante);
dsData.Tables.Add(dtDetalle);
dtComprobante.PrimaryKey = new DataColumn[] { dtComprobante.Columns["NroCpte"] };
dtDetalle.PrimaryKey = new DataColumn[] { dtDetalle.Columns["NroCpte"], dtDetalle.Columns["NroDetalle"] };
//Create RelationShip Comprobante - Detalle
DataColumn parentColumn = new DataColumn();
parentColumn = dsData.Tables["Comprobante"].Columns["NroCpte"];
//parentColumn.AutoIncrement = true;
//parentColumn.AutoIncrementSeed = 1;
//parentColumn.AutoIncrementStep = 1;
parentColumn.Unique = true;
DataColumn childColumn = new DataColumn();
childColumn = dsData.Tables["Detalle"].Columns["NroCpte"];
//childColumn.AutoIncrement = true;
//childColumn.AutoIncrementSeed = 1;
//childColumn.AutoIncrementStep = 1;
childColumn.Unique = false;
DataColumn childColumnKey = new DataColumn();
childColumnKey = dsData.Tables["Detalle"].Columns["NroDetalle"];
childColumnKey.AutoIncrement = true;
childColumnKey.AutoIncrementSeed = 1;
childColumnKey.AutoIncrementStep = 1;
//childColumn.Unique = true;
DataRelation relation = new DataRelation("REL_Cmpte_Detalle", parentColumn, childColumn, true);
dsData.Relations.Add(relation);
relation.ChildKeyConstraint.AcceptRejectRule = AcceptRejectRule.Cascade;
relation.ChildKeyConstraint.DeleteRule = Rule.Cascade;
relation.ChildKeyConstraint.UpdateRule = Rule.Cascade;
//ForeignKeyConstraint fk = new ForeignKeyConstraint("ForeignKey", dsData.Tables["Comprobante"].Columns["NroCpte"], dsData.Tables["Detalle"].Columns["NroCpte"]);
//ForeignKeyConstraint fk = new ForeignKeyConstraint("ForeignKey", parentColumn, childColumn);
//fk.DeleteRule = Rule.Cascade;
//fk.UpdateRule = Rule.Cascade;
//dtDetalle.Constraints.Add(fk);
//Create RelationShip Comprobante - controls forms
TxtNroCpte.DataBindings.Add(new Binding("Text", dsData.Tables["Comprobante"].DefaultView, "NroCpte"));
CmbCliente.DataBindings.Add(new Binding("Text", dsData.Tables["Comprobante"].DefaultView, "Cliente"));
TxtDescripcion.DataBindings.Add(new Binding("Text", dsData.Tables["Comprobante"].DefaultView, "Descripcion"));
DtFecha.DataBindings.Add(new Binding("Value", dsData.Tables["Comprobante"].DefaultView, "Fecha"));
DGVDetalle.DataSource = dsData.Tables["Detalle"].DefaultView;
DGVDetalle.Columns[ColNroCpte.Index].DataPropertyName = "NroCpte";
DGVDetalle.Columns[ColNroDetalle.Index].DataPropertyName = "NroDetalle";
DGVDetalle.Columns[ColProducto.Index].DataPropertyName = "Producto";
DGVDetalle.Columns[ColDenominacion.Index].DataPropertyName = "Denominacion";
DGVDetalle.Columns[ColUnidad.Index].DataPropertyName = "Unidad";
DGVDetalle.Columns[ColCosto.Index].DataPropertyName = "CostoUnt";
DGVDetalle.Columns[ColPrVenta.Index].DataPropertyName = "PrecioUnt";
DGVDetalle.Columns[ColCantidad.Index].DataPropertyName = "Cantidad";
DGVDetalle.Columns[ColDescuento.Index].DataPropertyName = "Descuento";
DGVDetalle.Columns[ColMonto.Index].DataPropertyName = "Monto";
//bindingmanager
BMBComprobante = this.BindingContext[dsData.Tables["Comprobante"].DefaultView];
BMBComprobante.PositionChanged += new EventHandler(Binding_PositionChanged);
dtComprobante.TableNewRow += new DataTableNewRowEventHandler(dtComprobante_NewRow);
dtDetalle.TableNewRow += new DataTableNewRowEventHandler(dtDetalle_NewRow);
this.Cursor = Cursors.Default;
return true;
}
catch (Exception e)
{
MessageBox.Show(e.Message, "CrearDSRegSalida", MessageBoxButtons.OK, MessageBoxIcon.Error);
this.Cursor = Cursors.Default;
return false;
}
}
private void Binding_PositionChanged(object sender, System.EventArgs e)
{
string filter;
DataRow selectedRow;
if (this.BindingContext[dsData.Tables["Comprobante"].DefaultView].Position >= 0)
{
selectedRow = dtComprobante.Rows[this.BindingContext[dsData.Tables["Comprobante"].DefaultView].Position];
strCurrentNroCpte = selectedRow["NroCpte"].ToString();
filter = "NroCpte=" + strCurrentNroCpte;
dsData.Tables["Detalle"].DefaultView.RowFilter = filter;
}
}
private void dtComprobante_NewRow(object sender, DataTableNewRowEventArgs e)
{
e.Row["TipoCpte"] = "S";
e.Row["Fecha"] = DateTime.Now;
e.Row["Usuario"] = Modulos.ClsGlobal.Usuario;
e.Row["Descripcion"] = "nuevo";
}
private void dtDetalle_NewRow(object sender, DataTableNewRowEventArgs e)
{
if (strCurrentNroCpte.Trim().Length != 0)
{
Boolean result = long.TryParse(strCurrentNroCpte, out intNroCpte);
e.Row["NroCpte"] = intNroCpte;
e.Row["TipoCpte"] = "S";
}
}
private Boolean GrabarRegSalida()
{
int returnValue = 0;
try
{
BMBComprobante.EndCurrentEdit();
BMBDetalle.EndCurrentEdit();
if (dsData.HasChanges())
{
using (TransactionScope scope = new TransactionScope())
{
Modulos.ClsGlobal.MyNetCnx.Open();
if (blnNuevoCpte)
{
intNroCpte = objF.GetNumeroCpte("S");
returnValue = GrabarNroCpte(intNroCpte);
}
else
returnValue = 1;
returnValue = GrabarComprobante(intNroCpte);
returnValue += GrabarDetalle(intNroCpte);
scope.Complete();
}
Modulos.ClsGlobal.MyNetCnx.Close();
}
if (returnValue > 0)
return true;
else
return false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "GrabarRegSalida", MessageBoxButtons.OK, MessageBoxIcon.Information);
Modulos.ClsGlobal.MyNetCnx.Close();
return false;
}
}
private void DGVDetalle_Enter(object sender, EventArgs e)
{
this.BindingContext[dsData, "Comprobante"].EndCurrentEdit();
//BMBComprobante.EndCurrentEdit();
dsData.AcceptChanges();
Boolean str = dtComprobante.HasErrors;
}
当我想插入一个新的主行时，就会出现此错误:ForeignKeyConstraint REL_Cmpte_Detalle要求在父表中存在子键值(0)
When I like to insert a new master row, then i have this error: ForeignKeyConstraint REL_Cmpte_Detalle requires the child key values (0) to exist in the parent table
请帮助我.
谢谢.
-
DROP TABLE IF EXISTS `tbcomprobante`;
CREATE TABLE `tbcomprobante` (
`idTbComprobante` int(10) unsigned NOT NULL auto_increment,
`NroCpte` int(10) unsigned NOT NULL,
`TipoCpte` varchar(1) NOT NULL,
`Factura` varchar(10) default NULL,
`Fecha` datetime NOT NULL,
`Proveedor` varchar(50) default NULL,
`Cliente` varchar(50) default NULL,
`Usuario` varchar(5) NOT NULL,
`Descripcion` varchar(100) default NULL,
PRIMARY KEY USING BTREE (`idTbComprobante`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=latin1;
-
DROP TABLE IF EXISTS `tbdetalle`;
CREATE TABLE `tbdetalle` (
`idTbDetalle` int(10) unsigned NOT NULL auto_increment,
`NroCpte` int(10) unsigned NOT NULL,
`Producto` varchar(10) NOT NULL,
`Cantidad` int(10) unsigned NOT NULL default '0',
`CostoUnt` decimal(10,7) default '0.0000000',
`Monto` decimal(10,2) default '0.00',
`TipoCpte` varchar(1) NOT NULL,
`PrecioUnt` decimal(10,2) default '0.00',
`Descuento` int(10) unsigned NOT NULL,
`NroDetalle` int(10) unsigned NOT NULL,
PRIMARY KEY USING BTREE (`idTbDetalle`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1;
推荐答案
这是因为已将外键限制添加到您的主从表关系中，因此当您将新行插入数据集中时，它会期望有效外键关系.
This is because a Foreign Key restriction has been added to your master-detail table relation, so when you insert the new row into your dataset it is expecting a valid foreign key relationship.
即父表有一行具有相应ID的行.在这种情况下为"0".在这里，您应该检查密钥是否存在.
i.e. that the parent table has a row with the corresponding ID. In this case '0'. Here you should check that the key exists.
您可以临时关闭约束填表时:
DataSet1.EnforceConstraints = False
这篇关于ForeignKeyConstraint要求子键值(0)必须存在于父表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
