Assignment of several data to a single primary key?


Good morning, my question is this, can I give several assignments to a primary key? At this moment I am making a database for an exercise of my university and I wanted to give several assignments to a single primary key but to the time to make the inserts will not let me make them I will leave my code.

 **create table Integrantes(
   nombre varchar (50),
   id_integrante int,
   contraseña varchar (100),
   cc int,
   telefono int,
   correo varchar (100),
   carrera varchar (100),
   id_dependencia int,
   id_formacion int,
   PRIMARY KEY (id_integrante),
   CONSTRAINT fk_Dependencia_Integrantes 
   FOREIGN KEY (id_dependencia) REFERENCES Dependencia(id_dependencia),
   CONSTRAINT fk_Grupo_formacion_Integrantes 
   FOREIGN KEY (id_formacion) REFERENCES Grupo_formacion(id_formacion)

   create table Grupo_investigacion(
   id_grupoinvestigacion int,
   id_integrante int,
   PRIMARY KEY (id_grupoinvestigacion),
   CONSTRAINT fk_Integrantes_Grupo_investigacion 
   FOREIGN KEY (id_integrante) REFERENCES Integrantes(id_Integrante)

   insert into 'Grupo_investigacion' value(1,10);
   insert into 'Grupo_investigacion' value(1,11);**

and he will not let me do the assignment because I'm repeating the primary key you know what I can do?

asked by Andres Camilo Rincon Caballero 13.05.2018 в 08:15

2 answers


If you require a table where a primary key can be registered multiple times, you can but through a many-to-many relationship which will depend entirely on the business logic you are solving; I give you an example of how it would look:

CREATE TABLE integrantes_grupo_investigacion(
   integrante_id INT NOT NULL,
   grupo_investigacion_id INT NOT NULL,
   CONSTRAINT integrantes_grupo_investigacion_integrantes FOREIGN KEY(integrante_id) REFERENCES integrantes(id),
   CONSTRAINT integrantes_grupo_investigacion_Grupo_investigacion FOREIGN KEY(grupo_investigacion_id) REFERENCES Grupo_investigacion(id)

In the previous way, this table from many to many will allow you   for each record, multiple primary keys of the other two tables,   since for this table are foreign keys that must not meet the   rule of being unique records; once again I clarify this solution   It works if your business logic demands that you have a table of   type many to many

answered by 13.05.2018 / 15:21

If you need to insert the same key twice, then you must not use that key; in your example, you seem to need a composite key :

PRIMARY KEY (id_grupoinvestigacion, id_integrante)

In this way you can repeat id_grupoinvestigacion and id_integrante but not the combination of both fields.

answered by 13.05.2018 в 15:00