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.
Best Answer
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:
Then just check that Foreign Keys is enabled: