Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Error: The UPDATE statemenet conflicted with...

Author  Topic 

acefrankpr
Starting Member

4 Posts

Posted - 2010-10-08 : 22:41:45
Hello,

The problem I'm confronting is a referential error. I created a database in SQL Server which comunicates with a program I'm creating in Visual Basic.net. When I try to save data from the forms in VB.net this error appears:

The UPDATE statement conflicted with the REFERENCE constraint "FK_SERVICIO_CLIENTE". the conflict occurred in database "CE_PR", table "dbo.SERVICIO", column 'NumCliente'. The statement has terminated.

I have 12 tables conected with their respective foreign keys but I really don't understand what is happening. Hopefully some heroe can help me, thx!!!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-09 : 00:14:12
A foreign key says "any value you put HERE, must exist THERE"

"here" is the table you're trying to update, "there" is the table referenced by the foreign key.

in management studio, expand the table in the object explorer, then expand keys, right-click any foreign keys and script to new window. this will show you the table/column that enforces the values in the table you're trying to update.

or you can query the sys.foreign_keys system view for this info.
Go to Top of Page

acefrankpr
Starting Member

4 Posts

Posted - 2010-10-09 : 11:36:21
Thank you Russell. I'm in the Foreign Key Relationships window. I'm watching the tables and columns here. I see that there is a section that says INSERT And UPDATE Specification. Should I do something in this area. The relationships look ok. I really can't see the problem here. The only thing that may be causing problems is that I have one parent table supplying to 5 different tables. Other than that, I don't know what may be happening. It sucks to be a rookie!!! Thanks for the help!

quote:
Originally posted by russell

A foreign key says "any value you put HERE, must exist THERE"

"here" is the table you're trying to update, "there" is the table referenced by the foreign key.

in management studio, expand the table in the object explorer, then expand keys, right-click any foreign keys and script to new window. this will show you the table/column that enforces the values in the table you're trying to update.

or you can query the sys.foreign_keys system view for this info.

Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-09 : 13:02:41
You don't really have a problem per se...te foreign key is enforcing a relationship.

For a value to exist in the child table, it must also already exist in the parent table.

This prevents incorrect data and orphaned data.

For example, if you had a US States table, and a Store Location table, you would want to make sure that the store is in a valid state. You put the 50 states (and DC) in the states table and create a FK from the store locations table to the states table. This makes it impossible to inadvertantly allow bad data -- a state that doesn't exist in this example.

If you post the DDL for your tables, and the update statement you're trying to perform, we can give you more specific help.

You can right-click the tables and choose "script as" to generate the DDL statements.
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-10-09 : 13:12:59
Review the table referenced by the column NumCliente. You update statement is probably trying to change that value to a value that does not exist in the table referenced by that column.

For example, if you are trying to change the NumClient column from value 1 - to value 2, and the NumCliente table does not have a row with that value - your update statement will fail.

Jeff
Go to Top of Page

acefrankpr
Starting Member

4 Posts

Posted - 2010-10-09 : 18:26:25
Here is the DDL of the tables:

***Table CLIENTE:***

USE [CE_PR]
GO
/****** Object: Table [dbo].[CLIENTE] Script Date: 10/09/2010 18:08:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CLIENTE](
[NumCliente] [int] NOT NULL,
[Nombre] [nchar](30) COLLATE Modern_Spanish_CI_AS NULL,
[Dirección] [nchar](100) COLLATE Modern_Spanish_CI_AS NULL,
[Dirección_2] [nchar](100) COLLATE Modern_Spanish_CI_AS NULL,
[FechaDeNacimiento] [datetime] NULL,
[Teléfono] [nchar](14) COLLATE Modern_Spanish_CI_AS NULL,
[Profesión] [nchar](25) COLLATE Modern_Spanish_CI_AS NULL,
[FechaDeComienzo] [datetime] NULL,
CONSTRAINT [PK_CLIENTE] PRIMARY KEY CLUSTERED
(
[NumCliente] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

***Table CABALLO:***

USE [CE_PR]
GO
/****** Object: Table [dbo].[CABALLO] Script Date: 10/09/2010 18:13:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CABALLO](
[NumCaballo] [int] NOT NULL,
[Nombre] [nchar](50) COLLATE Modern_Spanish_CI_AS NULL,
[FechaDeNacimiento] [datetime] NULL,
[Comida] [nchar](100) COLLATE Modern_Spanish_CI_AS NULL,
[Medicamentos] [nchar](100) COLLATE Modern_Spanish_CI_AS NULL,
[Vacunas] [nchar](100) COLLATE Modern_Spanish_CI_AS NULL,
[FechaDeUltimaVacuna] [datetime] NULL,
[NombreDelVeterinario] [nchar](30) COLLATE Modern_Spanish_CI_AS NULL,
[NumCliente] [int] NOT NULL,
CONSTRAINT [PK_CABALLO] PRIMARY KEY CLUSTERED
(
[NumCaballo] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [CE_PR]
GO
ALTER TABLE [dbo].[CABALLO] WITH CHECK ADD CONSTRAINT [FK_CABALLO_CLIENTE] FOREIGN KEY([NumCliente])
REFERENCES [dbo].[CLIENTE] ([NumCliente])

***Table DIARIO:***

USE [CE_PR]
GO
/****** Object: Table [dbo].[DIARIO] Script Date: 10/09/2010 18:14:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DIARIO](
[NumDiario] [int] NOT NULL,
[Fecha] [datetime] NULL,
[HoraDeEntrada] [datetime] NULL,
[HoraDeSalida] [datetime] NULL,
[TareasRealizadas] [nchar](500) COLLATE Modern_Spanish_CI_AS NULL,
[Observaciones] [nchar](500) COLLATE Modern_Spanish_CI_AS NULL,
[NumSupervisor] [int] NOT NULL,
CONSTRAINT [PK_DIARIO] PRIMARY KEY CLUSTERED
(
[NumDiario] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [CE_PR]
GO
ALTER TABLE [dbo].[DIARIO] WITH CHECK ADD CONSTRAINT [FK_DIARIO_SUPERVISOR] FOREIGN KEY([NumSupervisor])
REFERENCES [dbo].[SUPERVISOR] ([NumSupervisor])

***Table EMPLEADO:***

USE [CE_PR]
GO
/****** Object: Table [dbo].[EMPLEADO] Script Date: 10/09/2010 18:15:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EMPLEADO](
[NumEmpleado] [int] NOT NULL,
[Nombre] [nchar](30) COLLATE Modern_Spanish_CI_AS NULL,
[Dirección] [nchar](100) COLLATE Modern_Spanish_CI_AS NULL,
[Direción_2] [nchar](100) COLLATE Modern_Spanish_CI_AS NULL,
[Teléfono] [nchar](14) COLLATE Modern_Spanish_CI_AS NULL,
[SeguroSocial] [nchar](11) COLLATE Modern_Spanish_CI_AS NULL,
[FechaDeNacimiento] [datetime] NULL,
[FechaInicio] [datetime] NULL,
[Salario] [money] NULL,
[Username] [nchar](10) COLLATE Modern_Spanish_CI_AS NULL,
[Password] [nchar](10) COLLATE Modern_Spanish_CI_AS NULL,
[NumSupervisor] [int] NOT NULL,
CONSTRAINT [PK_EMPLEADO] PRIMARY KEY CLUSTERED
(
[NumEmpleado] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [CE_PR]
GO
ALTER TABLE [dbo].[EMPLEADO] WITH CHECK ADD CONSTRAINT [FK_EMPLEADO_SUPERVISOR] FOREIGN KEY([NumSupervisor])
REFERENCES [dbo].[SUPERVISOR] ([NumSupervisor])

***Table ESTABLO:***

USE [CE_PR]
GO
/****** Object: Table [dbo].[ESTABLO] Script Date: 10/09/2010 18:16:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ESTABLO](
[NumEstablo] [int] NOT NULL,
[Localización] [nchar](20) COLLATE Modern_Spanish_CI_AS NULL,
[Capacidad] [nchar](20) COLLATE Modern_Spanish_CI_AS NULL,
[NumSupervisor] [int] NOT NULL,
CONSTRAINT [PK_ESTABLO] PRIMARY KEY CLUSTERED
(
[NumEstablo] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [CE_PR]
GO
ALTER TABLE [dbo].[ESTABLO] WITH CHECK ADD CONSTRAINT [FK_ESTABLO_SUPERVISOR] FOREIGN KEY([NumSupervisor])
REFERENCES [dbo].[SUPERVISOR] ([NumSupervisor])

***Table JAULA:***

USE [CE_PR]
GO
/****** Object: Table [dbo].[JAULA] Script Date: 10/09/2010 18:17:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[JAULA](
[NumJaula] [int] NOT NULL,
[NumEstablo] [int] NOT NULL,
[NumCliente] [int] NOT NULL,
CONSTRAINT [PK_JAULA] PRIMARY KEY CLUSTERED
(
[NumJaula] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [CE_PR]
GO
ALTER TABLE [dbo].[JAULA] WITH CHECK ADD CONSTRAINT [FK_JAULA_CLIENTE] FOREIGN KEY([NumCliente])
REFERENCES [dbo].[CLIENTE] ([NumCliente])
GO
ALTER TABLE [dbo].[JAULA] WITH CHECK ADD CONSTRAINT [FK_JAULA_ESTABLO] FOREIGN KEY([NumEstablo])
REFERENCES [dbo].[ESTABLO] ([NumEstablo])

***Table MENSUALIDAD:***

USE [CE_PR]
GO
/****** Object: Table [dbo].[MENSUALIDAD] Script Date: 10/09/2010 18:18:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MENSUALIDAD](
[NumMensualidad] [int] NOT NULL,
[Mes] [nchar](30) COLLATE Modern_Spanish_CI_AS NULL,
[Año] [nchar](4) COLLATE Modern_Spanish_CI_AS NULL,
[CantidadMensual] [money] NULL,
[Balance] [money] NULL,
[Pago] [money] NULL,
[NumCliente] [int] NOT NULL,
CONSTRAINT [PK_MENSUALIDAD] PRIMARY KEY CLUSTERED
(
[NumMensualidad] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [CE_PR]
GO
ALTER TABLE [dbo].[MENSUALIDAD] WITH CHECK ADD CONSTRAINT [FK_MENSUALIDAD_CLIENTE] FOREIGN KEY([NumCliente])
REFERENCES [dbo].[CLIENTE] ([NumCliente])

***Table NOMINA_E:***

USE [CE_PR]
GO
/****** Object: Table [dbo].[NOMINA_E] Script Date: 10/09/2010 18:18:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NOMINA_E](
[NumTiempo_E] [int] NOT NULL,
[Fecha] [datetime] NULL,
[HorasAcumuladas] [int] NULL,
[DíasTrabajados] [int] NULL,
[Pago] [money] NULL,
[NumEmpleado] [int] NOT NULL,
CONSTRAINT [PK_TIEMPO] PRIMARY KEY CLUSTERED
(
[NumTiempo_E] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [CE_PR]
GO
ALTER TABLE [dbo].[NOMINA_E] WITH CHECK ADD CONSTRAINT [FK_NOMINA_EMPLEADO] FOREIGN KEY([NumEmpleado])
REFERENCES [dbo].[EMPLEADO] ([NumEmpleado])

***Table NOMINA_S:***

USE [CE_PR]
GO
/****** Object: Table [dbo].[NOMINA_S] Script Date: 10/09/2010 18:19:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NOMINA_S](
[NumTiempo_S] [int] NOT NULL,
[Fecha] [datetime] NULL,
[HorasAcumuladas] [int] NULL,
[DíasTrabajados] [int] NULL,
[Pago] [money] NULL,
[NumSupervisor] [int] NOT NULL,
CONSTRAINT [PK_NOMINA_S] PRIMARY KEY CLUSTERED
(
[NumTiempo_S] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [CE_PR]
GO
ALTER TABLE [dbo].[NOMINA_S] WITH CHECK ADD CONSTRAINT [FK_NOMINA_S_SUPERVISOR] FOREIGN KEY([NumSupervisor])
REFERENCES [dbo].[SUPERVISOR] ([NumSupervisor])

***Table PETICION:***

USE [CE_PR]
GO
/****** Object: Table [dbo].[PETICION] Script Date: 10/09/2010 18:20:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PETICION](
[NumPeticion] [int] NOT NULL,
[Fecha] [datetime] NULL,
[Peticion] [nchar](300) COLLATE Modern_Spanish_CI_AS NULL,
[NumCliente] [int] NULL,
CONSTRAINT [PK_PETICION] PRIMARY KEY CLUSTERED
(
[NumPeticion] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [CE_PR]
GO
ALTER TABLE [dbo].[PETICION] WITH CHECK ADD CONSTRAINT [FK_PETICION_CLIENTE] FOREIGN KEY([NumCliente])
REFERENCES [dbo].[CLIENTE] ([NumCliente])

***Table SERVICIO:***

USE [CE_PR]
GO
/****** Object: Table [dbo].[SERVICIO] Script Date: 10/09/2010 18:21:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SERVICIO](
[NumServicio] [int] NOT NULL,
[Fecha] [datetime] NULL,
[HoraDeEntrada] [datetime] NULL,
[HoraDeSalida] [datetime] NULL,
[TipoDeServicio] [nchar](200) COLLATE Modern_Spanish_CI_AS NULL,
[Tareas] [nchar](200) COLLATE Modern_Spanish_CI_AS NULL,
[NumEmpleado] [int] NULL,
CONSTRAINT [PK_SERVICIO] PRIMARY KEY CLUSTERED
(
[NumServicio] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [CE_PR]
GO
ALTER TABLE [dbo].[SERVICIO] WITH CHECK ADD CONSTRAINT [FK_SERVICIO_EMPLEADO] FOREIGN KEY([NumEmpleado])
REFERENCES [dbo].[EMPLEADO] ([NumEmpleado])

***Table SUPERVISOR:***

USE [CE_PR]
GO
/****** Object: Table [dbo].[SUPERVISOR] Script Date: 10/09/2010 18:22:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SUPERVISOR](
[NumSupervisor] [int] NOT NULL,
[Nombre] [nchar](30) COLLATE Modern_Spanish_CI_AS NULL,
[Dirección] [nchar](100) COLLATE Modern_Spanish_CI_AS NULL,
[Dirección_2] [nchar](100) COLLATE Modern_Spanish_CI_AS NULL,
[Teléfono] [nchar](14) COLLATE Modern_Spanish_CI_AS NULL,
[SeguroSocial] [nchar](11) COLLATE Modern_Spanish_CI_AS NULL,
[FechaDeNacimiento] [datetime] NULL,
[FechaInicio] [datetime] NULL,
[Salario] [money] NULL,
[Username] [nchar](10) COLLATE Modern_Spanish_CI_AS NULL,
[Password] [nchar](10) COLLATE Modern_Spanish_CI_AS NULL,
CONSTRAINT [PK_SUPERVISOR] PRIMARY KEY CLUSTERED
(
[NumSupervisor] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


And the UPDATE command I'm trying to perform via VB.net is:

Dim sqlConnection As New SqlClient.SqlConnection(qw)
Dim cmd As New SqlClient.SqlCommand

Try
' Assign Connection String from a Connection control
' where supposedly the wizard already build the
' It is imperative to use Try and Catch
' Connection String.
sqlConnection.ConnectionString = qw

' Open the connection
sqlConnection.Open()
' Build the SQL command and assign it to the
' Command Text property of the Command object
cmd.CommandText = "update CLIENTE " _
& " set NumCliente= '" & Me.txtnumcliente.Text & "', Nombre = '" _
& Me.txtnombre.Text & "', Profesión = '" & Me.txtprofesion.Text & "', " _
& " FechaDeComienzo = '" & Me.txtfechadecomienzo.Text & "', " _
& " FechaDeNacimiento = '" & Me.txtfechadenacimiento.Text & "', " _
& " Teléfono = '" & Me.txttelefono.Text & "', " _
& " Dirección = '" & Me.txtdireccion.Text & "', " _
& " Dirección_2 = '" & Me.txtdireccion2.Text & "' "


' Tie the Command object to the specific
' Connection object
cmd.Connection = sqlConnection
' And, execute the Command object as a non-query,
' meaning that no returned values are expected.
cmd.ExecuteNonQuery()

Catch e1 As Exception
' It is imperative to use Try and Catch
' because the error message will get lost otherwise
MsgBox("error " & e1.Message, MsgBoxStyle.Critical, "Error Message")
Finally
' Always close a Connection when you are done.
sqlConnection.Close()
Call LlenarTabla()
End Try
End Sub
Private Sub LlenarTabla()

'Sql Statement to select the records from the Inventory table.

Dim sqlStr As String = "SELECT * FROM CLIENTE"

'Data adapter created using the connection string and Sql.

Try
' Specify a connection string.
' Create a new data adapter based on the specified query.

Dim clientadapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter _
(sqlStr, qw)

' Create a command builder to generate SQL update, insert, and
' delete commands based on selectCommand. These are used to
' update the database.

Dim cmd As New SqlClient.SqlCommand

'this line of code populates a new data table and binds it to the BindingSource
Dim table As New DataTable()
table.Locale = System.Globalization.CultureInfo.InvariantCulture
clientadapter.Fill(table)

Catch ex As SqlClient.SqlException
MessageBox.Show("To run this example, replace the value of the " + _
"connectionString variable with a connection string that is " + _
"valid for your system.")
End Try

End Sub




quote:
Originally posted by russell

You don't really have a problem per se...te foreign key is enforcing a relationship.

For a value to exist in the child table, it must also already exist in the parent table.

This prevents incorrect data and orphaned data.

For example, if you had a US States table, and a Store Location table, you would want to make sure that the store is in a valid state. You put the 50 states (and DC) in the states table and create a FK from the store locations table to the states table. This makes it impossible to inadvertantly allow bad data -- a state that doesn't exist in this example.

If you post the DDL for your tables, and the update statement you're trying to perform, we can give you more specific help.

You can right-click the tables and choose "script as" to generate the DDL statements.

Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-09 : 21:27:46
1. the code you posted doesn't match the error you showed us in the 1st post.

2. you don't have a WHERE clause in your update statement. Not good. probably lucky it didn't work.

3. the way you're building the SQL string is a wide open invitation for SQL injection attacks. Please research that term. VERY important.

Finally, best I can say is look at what column the foreign key references and you'll see that the value you're trying to pass in doesn't exist there. Either add it, or use a valid value.
Go to Top of Page

acefrankpr
Starting Member

4 Posts

Posted - 2010-10-10 : 09:50:04
So the problem is in the SQL string, not in the database design?

I will check that. Thank you so much!
Go to Top of Page
   

- Advertisement -