Problem to reference keys in IBM informix [closed]

Question

Problem to reference keys in IBM informix [closed]

Navigation

#1 by (0 votes)

1

My situation is as follows. Structure:

Grupo (GrupoId, GrupoNombre)
Asignatura (AsignaturaId, AsignaturaNombre)
GrupoTieneAsignatura (GrupoId, AsignaturaID)
Planilla(GrupoId(de grupotieneasignatura), AsignaturaID(de grupotieneasignatura), otros)

As you can see in the structure I try to reference a key you could say in cascade. But when referencing in the last one, I get an error. Why can it be?

The code I am using is the following:

  CREATE TABLE asignatura  ( 
    asignaturaid        SERIAL NOT NULL,
    asignaturanombre    VARCHAR(25) NOT NULL,
    asignaturaestado    BOOLEAN DEFAULT T,
    areaid              INTEGER NOT NULL,
    PRIMARY KEY(asignaturaid)
);
CREATE TABLE grupo  ( 
    grupoid         SERIAL NOT NULL,
    gruponombre     VARCHAR(25) NOT NULL,
    grupocantidad   SMALLINT NOT NULL,
    grupoestado     BOOLEAN DEFAULT T,
    PRIMARY KEY(grupoid)
);

CREATE TABLE grupotieneasignatura (
asignaturaid INTEGER NOT NULL,
grupoid INTEGER NOT NULL,
PRIMARY KEY(asignaturaid, grupoid)
);

CREATE TABLE planilla  ( 
    diaid           INTEGER NOT NULL,
    horaid          INTEGER NOT NULL,
    personaci       CHAR(8) NOT NULL,
    aulaid          INTEGER,
    asignaturaid    INTEGER,
    grupoid         INTEGER,
    PRIMARY KEY(diaid,horaid,aulaid,personaci)
);
ALTER TABLE grupotieneasignatura
    ADD CONSTRAINT ( FOREIGN KEY(grupoid)
    REFERENCES grupo(grupoid) CONSTRAINT ex1
     );
ALTER TABLE grupotieneasignatura
    ADD CONSTRAINT ( FOREIGN KEY(asignaturaid)
    REFERENCES asignatura(asignaturaid) CONSTRAINT ex2
     );
ALTER TABLE planilla
    ADD CONSTRAINT ( FOREIGN KEY(personaci)
    REFERENCES persona(personaci) CONSTRAINT externapersona
    );
ALTER TABLE planilla
    ADD CONSTRAINT ( FOREIGN KEY(grupoid)
    REFERENCES grupotieneasignatura(grupoid) CONSTRAINT externagrupo
     );
ALTER TABLE planilla
    ADD CONSTRAINT ( FOREIGN KEY(aulaid)
    REFERENCES aula(aulaid) CONSTRAINT externaaula
     );
ALTER TABLE planilla
    ADD CONSTRAINT ( FOREIGN KEY(asignaturaid)
    REFERENCES grupotieneasignatura(asignaturaid) CONSTRAINT externaasignatura
     );

The error that is throwing me when executing these sql intrusions is the following:

No primary key or single type constraint is found in the table (grupotieneasignatura).

sql informix

asked by Federico Ventura 03.11.2016 в 22:12

source

1 answer

Modal window in swift SDL2 and video memory

score 0 · Answer 1

The error must come from this statement:

ALTER TABLE planilla
ADD CONSTRAINT ( FOREIGN KEY(grupoid)
REFERENCES grupotieneasignatura(grupoid) CONSTRAINT externagrupo
 );

or this:

ALTER TABLE planilla
ADD CONSTRAINT ( FOREIGN KEY(asignaturaid)
REFERENCES grupotieneasignatura(asignaturaid) CONSTRAINT externaasignatura
 );

In both cases, you are trying to create a foreign key to grupotieneasignatura but for a column that alone is not defined as a primary key or a unique key (note what the error says).

Since the primary key for grupotieneasignatura is the combination of both columns grupoid and asignaturaid , most likely what you really want is to define a single foreign key that combines both columns.

So, instead of the 2 sentences mentioned above, replace them with the following:

alter table planilla
add constraint (
  foreign key(asignaturaid, grupoid)
  references grupotieneasignatura(asignaturaid, grupoid)
  constraint externaasignaturagrupo
);

Maybe the syntax I used is not perfect, but the idea should work.

Another possibility is that you really want 2 separate foreign keys, but not to the grupotieneasignatura table. In this case, you probably want the keys to refer to the 2 tables grupo and asignatura separately.

If this is the case, you can modify the 2 sentences with the following:

ALTER TABLE planilla
ADD CONSTRAINT ( FOREIGN KEY(grupoid)
REFERENCES grupo(grupoid) CONSTRAINT externagrupo
 );

ALTER TABLE planilla
ADD CONSTRAINT ( FOREIGN KEY(asignaturaid)
REFERENCES asignatura(asignaturaid) CONSTRAINT externaasignatura
 );

I'll let you decide which of the 2 options is right for you.