create and relate 2 temporary tables

Question

create and relate 2 temporary tables

Navigation

#1 by (1 votes)
#2 by (1 votes)

2

good day I am creating 2 temporary tables the first table is called QUESTIONS

CREATE temporary table PREGUNTAS(
idpregunta int auto_increment,
primary key(idpregunta),
pregunta varchar(200)
);

the second one is called OPTION

CREATE temporary table OPCION (
idopcion int auto_increment,
primary key(idopcion), 
opcion varchar(50),
valor int,
foreign key(idpregunta)references PREGUNTAS(idpregunta)
);

I need the QUESTIONS table to have relation with OPTION, I realize it as it is in option but it marks me error saying that idpregunta does not exist in the table, I hope and they can help me to correct. Or is it an error due to the fact that it is a temporary table? THESE TABLES ARE CARRIED OUT BEFORE CLOSING CONNECTION.

mysql

asked by Leslie Sharai 31.07.2017 в 17:56

source

2 answers

Why do you give me the error [object NodeList]? Series fibonacci in a matrix n * n, C #

score 1 · Answer 1

The error is by this line of yours:

foreign key(idpregunta)references PREGUNTAS(idpregunta)

Try with:

CREATE temporary table OPCION (
idopcion int auto_increment,
primary key(idopcion), 
idpregunta int,
opcion varchar(50),
valor int,
foreign key(idpregunta) references PREGUNTAS(idpregunta)
);

Greetings.

score 1 · Answer 2

Reviewing the MySQL documentation we see that > Foreign keys can not be assigned to temporary tables :

Foreign keys definitions are subject to the following conditions:


Foreign key relationships involve a parent table that holds the   central data values, and a child table with identical values pointing   back to its parent. The FOREIGN KEY clause is specified in the child   table The parent and child tables must use the same storage engine.   They must not be TEMPORARY tables.



...

Foreign key definitions are subject to the following    conditions :


Foreign key relationships involve a primary table that   contains the core data values and a secondary table with   identical values that point back to your matrix. The clause   FOREIGN KEY is specified in the secondary table. The father and   secondary should use the same storage engine. They should not   be TEMPORARY tables .



...

That is, even if you apply the correct syntax for foreign keys in temporary tables:

CREATE temporary table PREGUNTAS(
idpregunta int auto_increment,
primary key(idpregunta),
pregunta varchar(200)
);

CREATE temporary table OPCION (
idopcion int auto_increment,
    idpregunta INT,
primary key(idopcion), 
opcion varchar(50),
valor int,
foreign key(idpregunta)references PREGUNTAS(idpregunta)
);

You will be entitled to the following error:

Can not add foreign key constraint