How to assign a primary key composed as a foreign key in another table with MySQL?

Question

How to assign a primary key composed as a foreign key in another table with MySQL?

Navigation

#1 by (0 votes)

0

What happens is that I have a primary key composed in a table and that key must be foreign in another table. Could someone help me please?

Example:

CREATE TABLE 'detallehorario' (
  'Id_Grupo' int(11) NOT NULL,
  'Dia' varchar(15) COLLATE latin1_bin NOT NULL,
  'HoraEntrada' time NOT NULL,
  'HoraSalida' time NOT NULL,
  PRIMARY KEY ('Id_Grupo','Dia'),
  KEY 'Id_Grupo_idx' ('Id_Grupo'),
  CONSTRAINT 'Id_Grupo' FOREIGN KEY ('Id_Grupo') REFERENCES 'grupo' ('Id_Grupo') ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

This key is composed of two fields, worth the redundancy and I need to have it as foreign in the Student table; The code that I try to implement is the following:

CREATE TABLE 'alumno' (
  'Matricula' varchar(15) COLLATE latin1_bin NOT NULL,
  'Nombre' varchar(50) COLLATE latin1_bin NOT NULL,
  'ApellidoPaterno' varchar(50) COLLATE latin1_bin NOT NULL,
  'ApellidoMaterno' varchar(50) COLLATE latin1_bin NOT NULL,
  'Id_Grupo' varchar(45) COLLATE latin1_bin NOT NULL,
  'Dia' varchar(15) COLLATE latin1_bin NOT NULL,
  PRIMARY KEY ('Matricula'),
      CONSTRAINT 'Id_Grupo' FOREIGN KEY ('Id_Grupo','Dia') REFERENCES 'detallegrupo' ('Id_Grupo','Dia') ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

Will anyone have an idea of why I get the error? Thank you in advance

sql mysql-workbench

asked by Juan Manuel Miranda 22.09.2018 в 05:06

source

1 answer

Values separated by comma to filter - PHP - Wordpress how can I set a distinct to the result of a select that contains another distinct?

score 0 · Answer 1

Assuming that the detallehorario table does not need changes, you might try something like:

CREATE TABLE 'alumno' (
  'Matricula' varchar(15) COLLATE latin1_bin NOT NULL,
  'Nombre' varchar(50) COLLATE latin1_bin NOT NULL,
  'ApellidoPaterno' varchar(50) COLLATE latin1_bin NOT NULL,
  'ApellidoMaterno' varchar(50) COLLATE latin1_bin NOT NULL,
  'Id_Grupo' int(11) NOT NULL,
  'Dia' varchar(15) COLLATE latin1_bin NOT NULL,
  PRIMARY KEY ('Matricula'),
      CONSTRAINT 'Id_Grupo_Fk' FOREIGN KEY ('Id_Grupo','Dia') REFERENCES 'detallehorario' ('Id_Grupo','Dia') ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

Three changes: 1. Data type in Group_ID: varchar per int 2. Name of the constraint: Id_Grupo by Id_Grupo_Fk 3. Reference table of the composite key: detallegrupo by detallehorario