create database on sqlite3 (foreign key issues)

Question

Hi everyone

I have 3 tables:

CREATE TABLE Destination (DestinationID INTEGER NOT NULL PRIMARY KEY,
                          Destination TEXT NOT NULL,
                          NoGoing INTEGER NOT NULL);

CREATE TABLE User (UserID INTEGER NOT NULL PRIMARY KEY,
                   UserName TEXT NOT NULL);

CREATE TABLE Query (UserID INTEGER,
                    DestinationID INTEGER,
                    noPeople INTEGER NOT NULL,
                    PRIMARY KEY (UserID, DestinationID),
                    CONSTRAINT UserID REFERENCES User(UserID),
                    CONSTRAINT DestinationID REFERENCES Destination (DestinationID),
                    CONSTRAINT noPeople = SUM"NoGoing");

So the Query Table should be auto generated when a User Table interacts with the Destination Table since all the values for the Query Table is autoGenerated.

But i am not sure on how to achieve that using sqlite statements. The Constaint for the noPeople ...i not sure of how to go about calculating the SUM of NoGoing in Destination Table.

I heard that usig triggers can solve the above issue but i am still confused by it.

How do i improve on my codes?

Answer 1

See my answer to a similar question on using triggers here.