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.
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. |
|
|
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.
|
|
|
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. |
|
|
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 |
|
|
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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOUSE [CE_PR]GOALTER 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOUSE [CE_PR]GOALTER 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOUSE [CE_PR]GOALTER 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOUSE [CE_PR]GOALTER 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOUSE [CE_PR]GOALTER TABLE [dbo].[JAULA] WITH CHECK ADD CONSTRAINT [FK_JAULA_CLIENTE] FOREIGN KEY([NumCliente])REFERENCES [dbo].[CLIENTE] ([NumCliente])GOALTER 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOUSE [CE_PR]GOALTER 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOUSE [CE_PR]GOALTER 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOUSE [CE_PR]GOALTER 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOUSE [CE_PR]GOALTER 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOUSE [CE_PR]GOALTER 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 Subquote: 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.
|
|
|
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. |
|
|
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! |
|
|
|
|
|
|
|