problems with the primary key

Question

problems with the primary key

Navigation

#1 by (2 votes)

2

good morning classmates I have an issue, what happens is that I am doing a database for my project in MySQL but I have a problem, in the requested tables and arealogistica in this area I have a single worker or person in charge of this area and when arrive several orders would have to repeat the primary key of the logistics area (idAreaLogistica).

this is the areaLogistica table:

CREATE TABLE AreaLogistica(idAreaLogistica INT PRIMARY KEY NOT NULL,
 identificacionEmpleado INT NOT NULL,
 nombreEmpleado VARCHAR(60) NOT NULL,
 correo VARCHAR(20),
 telefono VARCHAR(20) NOT NULL,
 cumpleaños DATE NOT NULL);

is the requested table:

 CREATE TABLE Pedido(codigoPedido INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
 fechaPedido DATE NOT NULL,
 descripcionPedido VARCHAR(300),
 fk_idCliente VARCHAR(30) NOT NULL,
 FOREIGN KEY (fk_idCliente) REFERENCES Cliente(idCliente));

these would be the data of the areaLogistica table

insert into arealogistica(idAreaLogistica, identificacionEmpleado, nombreEmpleado, correo, telefono, cumpleaños, fk_codigoPedido, fk_nit)
values(2,1037890755, 'Jeferson Andres Moreno', '[email protected]', '3105678456', '1998-06-09', 1, '900.017.253-6'),
(2,1037890755, 'Jeferson Andres Moreno', '[email protected]', '3105678456', '1998-06-09', 2, '900.017.253-6'),
(2,1037890755, 'Jeferson Andres Moreno', '[email protected]', '3105678456', '1998-06-09', 3, '900.017.253-6');

the relationship is many to many. logically I get the error that I can not repeat the primary key.

Thanks in advance and sorry but I'm just starting on the subject.

mysql mysql-workbench

asked by Jhon James Hernandez 30.03.2018 в 23:10

source

1 answer

Problem when concatenating a variable with javascript text Problem with SQLite query

score 2 · Answer 1

According to your proposed scenario, there should be 3 tables the 2 that   they are called Logistics and Order area and a third that acts as a table   pivot

When you make a foreign key you can not link it with a varchar as   you are doing it with the field that you declare in fk_idClient, because not   are of the same type, the primary keys are integers not varchar

In the same way the fields that you declare as primary key, is no longer   necessary to indicate that they are not null and if it is necessary to   indicate that they will be incremental auto fields

I make this proposal for a solution

CREATE TABLE AreaLogistica
(
    idAreaLogistica INT PRIMARY KEY AUTO_INCREMENT,
    identificacionEmpleado INT NOT NULL,
    nombreEmpleado VARCHAR(60) NOT NULL,
    correo VARCHAR(20),
    telefono VARCHAR(20) NOT NULL,
    cumpleanios DATE NOT NULL
);

 CREATE TABLE Pedido(
     codigoPedido INT PRIMARY KEY AUTO_INCREMENT,
     fechaPedido DATE NOT NULL,
     descripcionPedido VARCHAR(300),
     fk_idCliente INT NOT NULL,
     CONSTRAINT fk_pedido_cliente FOREIGN KEY (fk_idCliente) REFERENCES Cliente(idCliente) 
 );


 --Esta sería la tabla pivote, bajo el argumento de que tu relación sea verdaderamente de muchos a muchos
 CREATE TABLE logistica_pedido(
    id INT PRIMARY KEY AUTO_INCREMENT,
    fk_idAreaLogistica INT NOT NULL,
    fk_codigoPedido INT NOT NULL,
    CONSTRAINT fk_logistica_pedido_AreaLogistica FOREIGN KEY(fk_idAreaLogistica) REFERENCES AreaLogistica(idAreaLogistica),
    CONSTRAINT fk_logistica_pedido_Pedido FOREIGN KEY(fk_codigoPedido) REFERENCES AreaLogistica(codigoPedido)
 );

Thus in the pivot table will be where you can repeat the   primary keys in the form of foreign keys of the other two tables;   I give you the context that was explained to me in this same site

Relation many to many

Several users can modify several posts

In this link I leave you the question I asked on this platform and how they helped me understand a little more about this topic

Relationship one to Many Many to many relationship

I would recommend you to rethink your Database and about the doubts that you have, do what I post to perfect the identification of entities and their relationships