2

I have created a db, and my problem is that I can insert ANY value in FK.

Sorry for having the values in Spanish.

This is my Database:

CREATE TABLE "Articulo" (
`ID`    INTEGER PRIMARY KEY AUTOINCREMENT,
`nombre`    TEXT NOT NULL,
`precio`    REAL NOT NULL,
`cantidad`  INTEGER NOT NULL,
`nombre_usuario`    TEXT,
FOREIGN KEY(`nombre_usuario`) REFERENCES Usuario)

CREATE TABLE `Categoria` (
    `nombre`    TEXT,
    PRIMARY KEY(nombre)
)

CREATE TABLE "Categoria_Articulo" (
    `ID_articulo`   INTEGER,
    `nombre`    TEXT,
    PRIMARY KEY(ID_articulo,nombre),
    FOREIGN KEY(`ID_articulo`) REFERENCES Articulo,
    FOREIGN KEY(`nombre`) REFERENCES Categoria
)

CREATE TABLE "Pedido" (
        `ID`    INTEGER PRIMARY KEY AUTOINCREMENT,
        `precio_total`  REAL NOT NULL DEFAULT 0,
        `nombre_usuario`    INTEGER NOT NULL,
        `fecha` TEXT NOT NULL,
        FOREIGN KEY(`nombre_usuario`) REFERENCES Usuario
    )

CREATE TABLE "Linea_Pedido" (
    `ID`    INTEGER PRIMARY KEY AUTOINCREMENT,
    `precio_total`  REAL NOT NULL,
    `cantidad`  INTEGER NOT NULL,
    `ID_pedido` INTEGER NOT NULL,
    FOREIGN KEY(`ID_pedido`) REFERENCES Pedido
)

CREATE TABLE "Linea_Pedido_Articulo" (
    `ID_linea_pedido`   INTEGER,
    `ID_articulo`   INTEGER,
    PRIMARY KEY(ID_linea_pedido,ID_articulo),
    FOREIGN KEY(`ID_linea_pedido`) REFERENCES Linea_Pedido,
    FOREIGN KEY(`ID_articulo`) REFERENCES Articulo
)

CREATE TABLE "Linea_Pedido_Articulo" (
    `ID_linea_pedido`   INTEGER,
    `ID_articulo`   INTEGER,
    PRIMARY KEY(ID_linea_pedido,ID_articulo),
    FOREIGN KEY(`ID_linea_pedido`) REFERENCES Linea_Pedido,
    FOREIGN KEY(`ID_articulo`) REFERENCES Articulo
)

CREATE TABLE `Usuario` (
    `nombre_usuario`    TEXT,
    `nombre`    TEXT NOT NULL,
    `apellidos` TEXT NOT NULL,
    `contraseña`    TEXT NOT NULL,
    PRIMARY KEY(nombre_usuario)
)

CREATE TABLE `favorito` (
    `usuario_tiene_favorito`    TEXT,
    `usuario_es_favorito`   TEXT,
    PRIMARY KEY(usuario_tiene_favorito,usuario_es_favorito),
    FOREIGN KEY(`usuario_tiene_favorito`) REFERENCES Usuario,
    FOREIGN KEY(`usuario_es_favorito`) REFERENCES Usuario
)

For example, I am able to insert in Linea_Pedido_Articulo in my all empty database which is an N-N relationship, the values I wanted, when it should show an error.

I have found this similar question, but it is not well answered I think.

Thanks for helping.

  • See this: sqlite.org/foreignkeys.html (especially section 2. Enabling Foreign Key Support). – ypercubeᵀᴹ Oct 5 '17 at 17:44
  • @ypercubeᵀᴹ I have execute that before, but I think not in the right place, where have I to put the PRAGMA foreign_keys = ON;?. Thanks! – Quico Llinares Llorens Oct 5 '17 at 17:46
  • You can use dbfiddle.uk when posting questions. Very helpful, for you and others to answer: dbfiddle.uk/… – ypercubeᵀᴹ Oct 5 '17 at 17:57
3

The problem is in the FK constraint, because they are not enabled. I am using DB Browser for SQLite, so you only have to go here in order to enable it: enter image description here

Then just check that Foreign Keys is enabled: enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.