Hi
I am a newbie to sqlite, I am having difficulty in trying to
build referential integrity based on a view.
sqlite allows me to create referential integrity based on a
view ( vu_cars ), however it doesn't allow me to insert any records into the
table car_properties.
I am using the sqlite version 3.7.2
I have explained below with an example:
Desired Result:
Green colored insert statements should be allowed
Red colored insert statement should throw an error "Foreign Key
mismatch"
Actual result:
Green colored and red colored statements are both not allowed.
Question:
Let me know if I am missing something or making a blunder.
Thanks.
I have a table called "vehicles" which is the master table.
CREATE TABLE vehicles(
type TEXT,
model_number INTEGER,
price DOUBLE,
PRIMARY KEY(type, model_number)
);
INSERT INTO vehicles(type, model_number, price) VALUES ("CAR", 1, 100);
INSERT INTO vehicles(type, model_number, price) VALUES ("BIKE", 1, 50);
INSERT INTO vehicles(type, model_number, price) VALUES ("CAR", 5, 200);
INSERT INTO vehicles(type, model_number, price) VALUES ("BIKE", 7, 50);
I have a view called "vu_cars" which lists only the CAR from the table
"vehicles".
CREATE VIEW vu_cars
AS
SELECT *
FROM vehicles
WHERE
type = "CAR";
I have a table called "car_properties" which contains the car
properties and has referential integrity based on the view "vu_cars"
CREATE TABLE car_properties(
model_number INTEGER,
number_of_doors INTEGER,
PRIMARY KEY(model_number),
FOREIGN KEY (model_number) REFERENCES
vu_cars(model_number)
);
--Below should be inserted successfully:
INSERT INTO car_properties(model_number, number_of_doors) VALUES (1, 4);
INSERT INTO car_properties(model_number, number_of_doors) VALUES (5, 2);
--This should throw an error:
INSERT INTO car_properties(model_number, number_of_doors) VALUES (7, 2);
Thanks and Regards,
Muthu
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
