Relationship between two tables.

[mozaharul]

Hi,
I try to setup relationship (one-to-many) between two tables as in the code:

Sub App_Start
Form1.Show
dbpath="C:\Documents and Settings\user\Desktop"
con.New1
cmd.New1("",con.Value)
con.Open("data source =" & dbpath & "\SQL_Rel")
cmd.CommandText="create table if not exists Auther (AuthID integer,name string, primary key ('AuthID'))"
cmd.ExecuteNonQuery
cmd.CommandText="create table if not exists Books (AuthID integer,Books string, foreign key ('AuthID') references Auther ('AuthID') on delete cascade)"
cmd.ExecuteNonQuery
End Sub


On deletion of record in the author table it is supposed to delete the related record(s) in the Books table automatically. I manually inserted one record in both tables and when deleted from Author table the corresponding (?) record(s) is not deleted from the Books table. I’m sure some thing wrong is there. Please correct me.


Best regards,

 

[Ariel_Z]

SQLite does not support this syntax.
Please see this:
SQLite CVSTrac

 

[mozaharul]

Thanks for the link.
If you further can help me about how to incorporate the Trigger in Basic4ppc. Some thing I tried but gives error :

Sub App_Start
Form1.Show
dbpath="C:\Documents and Settings\user\Desktop"
con.New1
cmd.New1("",con.Value)
con.Open("data source =" & dbpath & "\SQL_Rel")
cmd.CommandText="create table if not exists Author (AuthID integer not null,name string, primary key (AuthID))"
cmd.ExecuteNonQuery
cmd.CommandText="create table if not exists Books (AuthID integer not null,Books string, foreign key (AuthID) references Auther (AuthID))"
cmd.ExecuteNonQuery
cmd.CommandText = "CREATE TRIGGER if not exists FKI_Author_Books"&
BEFORE INSERT ON books
For EACH ROW BEGIN
Select RAISE(ROLLBACK)
WHERE(Select AuthID FROM author WHERE AuthID = new.AuthID) IS NULL;
End;
cmd.ExecuteNonQuery

End Sub



best regards,

 

[Ariel_Z]

Is this the entire code? If it is a copy-paste, then there are some Basic4ppc syntax errors that will not allow it to compile. Could you post the source file? What error do you get?

 

[mozaharul]

Thank you again.
I attached the small application.

Please help.


best regards,

 

[mozaharul]

Please help about the problem.


best regards,

 

[Ariel_Z]

Well, this IS the source code! But this is not Basic4ppc ... You mixed SQL and Basic4ppc so there are lines (17, 18, 19) that should be part of the SQL but are written as Basic4ppc.
Apart from this, the database is missing.
Could you post a zip file that includes both code and database?

 

[Erel]

The following lines:

cmd.CommandText = "CREATE TRIGGER if not exists FKI_Author_Books"&
    BEFORE INSERT ON books
    For EACH ROW BEGIN
        Select RAISE(ROLLBACK)
        WHERE(Select AuthID FROM author WHERE AuthID = new.AuthID) IS NULL;
    End;

Should be changed to:

    cmd.CommandText = "CREATE TRIGGER if not exists FKI_Author_Books" & CRLF & _
    "BEFORE INSERT ON books" & CRLF & _
    "For EACH ROW BEGIN" & CRLF & _
        "Select RAISE(ROLLBACK)" & CRLF & _
        "WHERE(Select AuthID FROM author WHERE AuthID = new.AuthID) IS NULL;" & CRLF & _
    "End;"