I can not add the foreign key of the user table

1

Before the user table create the corresponding tables to add the foraneas

    create table acreditacion(idacre int not null auto_increment,
                     nomacre varchar(50) not null,
                     primary key(idacre)) ENGINE = InnoDB;

    -- 2. creando tabla coordinacion

    create table coordinacion(idcoord int not null auto_increment,
                     nomcoord varchar(50) not null,
                     ubicacion varchar(20) not null,
                     primary key(idcoord)) ENGINE = InnoDB;

    -- 3. creando titulo_universitario

    create table titulo_universitario(idtitulo int not null auto_increment, 
                             nomtitulo varchar(20) not null,
                             primary key(idtitulo)) ENGINE = InnoDB;

    -- 4. creando tabla perfil

    create table perfil(nroperfil int not null auto_increment, 
               nomperfil varchar(15) not null,
               primary key(nroperfil)) ENGINE = InnoDB;

    -- 5. creando tabla dedicacion

    create table dedicacion(iddedicacion int not null auto_increment,
                   nomdedicacion varchar(20) not null,
                   primary key(iddedicacion)) ENGINE = InnoDB;

    -- 6. creando tabla programa

    create table programa(idprograma int not null auto_increment,
                 tipoprograma varchar(5) not null,
                 nomprograma varchar(100) not null,
                 primary key(idprograma)) ENGINE = InnoDB;

    -- 7. creando tabla cargo

   create table cargo(idcargo int not null auto_increment,
              nomcargo varchar(20) not null,
              primary key(idcargo)) ENGINE = InnoDB;

    -- 8. creando tabla proyecto

   create table proyecto(idproyecto int not null auto_increment,
                 nomproyecto text not null,
                 resumen varchar(200) null,
                 idacre int null,
                 primary key(idproyecto),
                 foreign key(idacre) references acreditacion(idacre)) ENGINE = InnoDB;

   -- 9. creando tabla nucleo_academico

   create table nucleo_academico(idnucleo int not null auto_increment,
                         nomnucleo varchar(150) not null,
                         idcoord int not null,
                         primary key(idnucleo),
                         foreign key(idcoord) references coordinacion(idcoord)) ENGINE = InnoDB;

So far everything is going well now when I try to create the user table which is the following:

   -- 10. creando tabla de usuario

   create table usuario(ci int not null,
                prnombre varchar(20) not null,
                segnombre varchar(20) null,
                prapellido varchar(20) not null,
                segapellido varchar(20) null,
                sexo char not null,
                fecnac date not null,
                rolobservacion varchar(100) not null,
                fechaing date not null,
                fecha_ult_ascenso date not null,
                tipo char not null,
                correo varchar(150) not null,
                contraseña varbinary(20) not null,
                pregunta text not null,
                respuesta varchar(200) not null,
                telefono varchar(15) not null,
                tipo_est varchar(50) not null,
                idcoord int not null,
                nroperfil int not null,
                iddedicacion int not null,
                idprograma int not null,
                idcargo int not null,
                ciasesor int null,
                primary key(ci),
                foreign key(idcoord) references coordinacioncepec(idcoord),
                foreign key(nroperfil) references perfil(nroperfil),
                foreign key(iddedicacion) references dedicacion(iddedicacion),
                foreign key (idprograma) references programa(idprograma),
                foreign key(idcargo) references cargo(idcargo)) ENGINE = InnoDB;

I have reviewed the code and I think I do not have syntax errors, thank you for your attention, happy day!

    
asked by Frankis Anthony 14.03.2018 в 00:24
source

2 answers

0

regardless of when you declare a foreign key you must use the complete CONSTRAINT statement plus the name of the union of tables as you can see in the code.

You were also wrong in the name of the coordination table, in the creation of the table you just put coordination and in the foreign keys you put coordinacioncepec , the name must be the same as you are linking that foreign key to a table that already exists

Ideally, when you declare a primary key, do not forget the attribute of AUTO_INCREMENT

Here I put the code already functional because it creates the tables in general

CREATE DATABASE demo;

USE demo;

create table acreditacion(
idacre int not null auto_increment,
nomacre varchar(50) not null,
primary key(idacre))ENGINE = InnoDB;

    -- 2. creando tabla coordinacion

create table coordinacion
(idcoord int not null auto_increment,
nomcoord varchar(50) not null,
ubicacion varchar(20) not null,
primary key(idcoord)) ENGINE = InnoDB;

    -- 3. creando titulo_universitario

create table titulo_universitario
(idtitulo int not null auto_increment, 
nomtitulo varchar(20) not null,
primary key(idtitulo)) ENGINE = InnoDB;

    -- 4. creando tabla perfil

create table perfil(nroperfil int not null auto_increment, 
               nomperfil varchar(15) not null,
               primary key(nroperfil)) ENGINE = InnoDB;

    -- 5. creando tabla dedicacion

    create table dedicacion(iddedicacion int not null auto_increment,
                   nomdedicacion varchar(20) not null,
                   primary key(iddedicacion)) ENGINE = InnoDB;

    -- 6. creando tabla programa

    create table programa(idprograma int not null auto_increment,
                 tipoprograma varchar(5) not null,
                 nomprograma varchar(100) not null,
                 primary key(idprograma)) ENGINE = InnoDB;

    -- 7. creando tabla cargo

   create table cargo(idcargo int not null auto_increment,
              nomcargo varchar(20) not null,
              primary key(idcargo)) ENGINE = InnoDB;

    -- 8. creando tabla proyecto

   create table proyecto(idproyecto int not null auto_increment,
                 nomproyecto text not null,
                 resumen varchar(200) null,
                 idacre int null,
                 primary key(idproyecto),
                 foreign key(idacre) references acreditacion(idacre)) ENGINE = InnoDB;

   -- 9. creando tabla nucleo_academico

   create table nucleo_academico(idnucleo int not null auto_increment,
                         nomnucleo varchar(150) not null,
                         idcoord int not null,
                         primary key(idnucleo),
                         foreign key(idcoord) references coordinacion(idcoord)) ENGINE = InnoDB;




create table usuario(
                                ci int not null AUTO_INCREMENT,
                prnombre varchar(20) not null,
                segnombre varchar(20) null,
                prapellido varchar(20) not null,
                segapellido varchar(20) null,
                sexo char not null,
                fecnac date not null,
                rolobservacion varchar(100) not null,
                fechaing date not null,
                fecha_ult_ascenso date not null,
                tipo char not null,
                correo varchar(150) not null,
                contraseña varbinary(20) not null,
                pregunta text not null,
                respuesta varchar(200) not null,
                telefono varchar(15) not null,
                tipo_est varchar(50) not null,
                idcord int not null,
                nroperfi int not null,
                iddedicacio int not null,
                idprogram int not null,
                idcarg int not null,
                ciaseso int null,
                CONSTRAINT pk_usuarios primary key(ci),
                CONSTRAINT fk_usuarios_coordinacion foreign key(idcord) references coordinacion(idcoord),
                CONSTRAINT fk_usuarios_perfil foreign key(nroperfi) references perfil(nroperfil),
                CONSTRAINT fk_usuarios_dedicacion foreign key(iddedicacio) references dedicacion(iddedicacion),
                CONSTRAINT fk_usuarios_programa foreign key (idprogram) references programa(idprograma),
                CONSTRAINT fk_usuarios_cargo foreign key(idcarg) references cargo(idcargo)
                ) ENGINE = InnoDB;
    
answered by 14.03.2018 / 01:54
source
1

in the code part try to change

            idcoord int not null,
            nroperfil int not null,
            iddedicacion int not null,
            idprograma int not null,
            idcargo int not null,
            ciasesor int null,

for the next one if it works for you,

            idcoord int(11) not null,
            nroperfil int(11) not null,
            iddedicacion int(11) not null,
            idprograma int(11) not null,
            idcargo int(11) not null,
            ciasesor int(11) null,

the other would be to use the alter table

            alter table "nombre table "add                
            foreign key(idcoord) references coordinacioncepec(idcoord),
            foreign key(nroperfil) references perfil(nroperfil),
            foreign key(iddedicacion) references dedicacion(iddedicacion),
            foreign key (idprograma) references programa(idprograma),
            foreign key(idcargo) references cargo(idcargo)
    
answered by 14.03.2018 в 01:53