Duplicate value ERROR, but I need to identify the field

Navigation
1

My question is very simple but I guess we are all here to support each other and keep learning.

My problem is in this line of code: 

INSERT INTO DEPENDIENTES VALUES (78900456,'Oscar','M',15-01-89,'Hijo');

the error bounces me because the number of Certificate 78900456 , already enter it in the previous record.

I guess the solution would be "remove the PK from the DEPENDIENTES Certificate variable, and you're ready to add it", because YES, but it will also allow me to add the same record over and over again.

EYE I CAN ADD THAT ID NUMBER several times, because the person can have several children, BUT NOT ALL THE DATA CAN BE EQUAL "Name_Dep, Sex, DateN, Relationship"

For a moment I also thought about removing the primary key from the TABLE OF PERSONS, but it would not be correct if I did not identify them as unique.?

  • Yes or if I have to put as FK to Cedula (DEPENDIENTES) of CEDULA of PERSONS because it is the only way to make the reference.

I hope you can help me because I need to add that last line of code but do not allow me because it's a duplicate value.

NOTE: THERE ARE NO MORE TABLES ARE ONLY THOSE 3 

CREATE TABLE DEPARTAMENTOS
(Cod_dep CHAR(2) not null primary key,
Nombre_Dep CHAR(15) not null,
Cedula_Jefe CHAR(8) not null);

CREATE TABLE PERSONAS
(Cedula CHAR(8) not null primary key,
Nombre CHAR(25) not null,
Primer_Apellido CHAR(25) not null,
Segundo_Apellido CHAR(25) not null,
Sexo CHAR(1) CHECK (Sexo IN ('M','F')),
Direccion CHAR(40) not null,
Telefono CHAR(9) not null,
Salario INT not null,
Cedula_Sup CHAR(8) not null,
Cod_dep CHAR(2) not null,
FOREIGN KEY (Cod_dep) REFERENCES DEPARTAMENTOS(Cod_Dep));

CREATE TABLE DEPENDIENTES
(Cedula CHAR(8) not null primary key,
Nombre_Dep CHAR(25) not null,
Sexo CHAR(1) CHECK (Sexo IN ('M','F')),
FechaN DATETIME,
Parentesco CHAR(15) not null,
FOREIGN KEY (Cedula) REFERENCES PERSONAS(Cedula));


INSERT INTO DEPARTAMENTOS VALUES (0,'Gerencia', 43890231);
INSERT INTO DEPARTAMENTOS VALUES (1,'Teleinformatica', 75556734);
INSERT INTO DEPARTAMENTOS VALUES (2,'Desarrollo', 23423445); 
INSERT INTO DEPARTAMENTOS VALUES (3,'Soporte Tecnico', 71134534); 

INSERT INTO PERSONAS VALUES (71134534,'Juan','Mesa','Uribe','M','Cra 25 22-1','2567532',1600000,23423445,3);
INSERT INTO PERSONAS VALUES (78900456,'Carlos','Betancur','Agudelo','M','Cir. 5 12-5','4445775',1500000,75556743,1);
INSERT INTO PERSONAS VALUES (73456789,'Mario','Gómez','Angel','M','Cr. 53 23-1','3456789',1200000,23423445,2);

INSERT INTO DEPENDIENTES VALUES (78900456,'Juanita','F',12-04-95,'Hija');
INSERT INTO DEPENDIENTES VALUES (78900456,'Oscar','M',15-01-89,'Hijo');
    
asked by Alexis Taboada 22.10.2017 в 21:55
source

2 answers

0

I would say that your dependent table will never be fine if you necessarily have a one-to-many relationship between the table people and them. One solution would be to incorporate a column that conforms to the parent's ID + a correlative number, which would be a primary key (combined) For now cedula is an FK and although it is necessary to relate it, the dependent himself also has a cedula that is unrepeatable, that would be the true PK of your table. I do not know, look at yourself

    
answered by 22.10.2017 в 22:03
0

I would recommend removing that pk and creating an sp and validate and if they are all the same that is not inserted and if something is different, insert it. something like this: 

create procedure insertar
@cedula nvarchar(14),
@nombre nvarchar(250),
@apellido nvarchar(250),
@pais nvarchar(250
as
begin
declare @count int
set @count=(select count(*) from tabla where cedula=@cedula and nombre=@nombre and apellido=@apellido and pais=@pais)
if @count >0
begin
select 'ya hay un registro';

end
else
begin
insert into tabla values(@cedula,@nombre,@apellido,@pais);
end

Also in an SP you can validate something else that you think is possible.

    
answered by 22.10.2017 в 23:23
JSON invalidated in DataTable Error Implementing JavaScript to display Modal