[sqlite] Referential integrity based on a view.

[Muthuveerappan Alagappan]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users