Re: [sqlite] database design question

[James K. Lowden]

On Wed, 12 Jun 2013 13:55:25 +0400
dd  wrote:

> I am trying minimize queries on Authors table. For every deletion of
> record from Books, it needs to check in Authors table. One extra
> query execution is there. I am trying minimize that.

You might not be aware of what you can do in a single query

delete from authors
where Name = 'Hamilton'
and AuthorId not in (select AuthorId from Author_Books)

That query will avoid deletion of authors represented among the books
without running afoul of referential integrity constraint enforcement.  

HTH.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database design question

[dd]

I got it.

Thank you RSmith and Simon Slavin.


On Wed, Jun 12, 2013 at 2:54 PM, Simon Slavin  wrote:

>
> On 12 Jun 2013, at 11:14am, dd  wrote:
>
> > Yes Simon. I am looking for it. ON DELETE RESTRICT.
> >
> > I got the answer.
> >
> > Should I enable foreign key support to use on delete restrict?(
> > http://www.sqlite.org/foreignkeys.html)
>
> I think that that fits with your earlier description: it will make SQLite
> refuse to delete an author if they have books in the database.
>
> > I have x databases without enabling foreign key support. Can I enable
> > foreign key support for x+1 database only?
>
> Turning on FOREIGN KEY support on a database that has no foreign keys does
> no harm.  It might slow some things down a millisecond perhaps.
>
> However, if you have separate applications for separate databases then you
> can turn on FOREIGN KEY support just in your 'books’ application.  In that
> application, just execute the SQL command
>
> PRAGMA foreign_keys = ON
>
> before you open the database file.  Then everything will work as expected.
>
> You must, of course, be using SQLite version 3.6.19 or later.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database design question

[Simon Slavin]

On 12 Jun 2013, at 11:14am, dd  wrote:

> Yes Simon. I am looking for it. ON DELETE RESTRICT.
> 
> I got the answer.
> 
> Should I enable foreign key support to use on delete restrict?(
> http://www.sqlite.org/foreignkeys.html)

I think that that fits with your earlier description: it will make SQLite 
refuse to delete an author if they have books in the database.

> I have x databases without enabling foreign key support. Can I enable
> foreign key support for x+1 database only?

Turning on FOREIGN KEY support on a database that has no foreign keys does no 
harm.  It might slow some things down a millisecond perhaps.

However, if you have separate applications for separate databases then you can 
turn on FOREIGN KEY support just in your 'books’ application.  In that 
application, just execute the SQL command

PRAGMA foreign_keys = ON

before you open the database file.  Then everything will work as expected.

You must, of course, be using SQLite version 3.6.19 or later.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database design question

[RSmith]

Foreign Key constraints are enabled or disabled for a connection to a database using the "PRAGMA foreign_keys = ON" SQL on the 
connection that should be doing the constraining.
After that, only DBs that have tables with foreign key constraints in one or more Tables will be affected by the setting and only 
for the connection which enabled it.


This means that opening the Database from another application might ignore your foreign key constraints if the connection did not 
have it set to ON.


I don't think there exists a way to force constraining from the DB file itself... but I might be wrong, anyone else with insight on 
this would be appreciated.



On 2013/06/12 12:14, dd wrote:

Yes Simon. I am looking for it. ON DELETE RESTRICT.

I got the answer.

Should I enable foreign key support to use on delete restrict?(
http://www.sqlite.org/foreignkeys.html)

I have x databases without enabling foreign key support. Can I enable
foreign key support for x+1 database only?



On Wed, Jun 12, 2013 at 2:01 PM, Simon Slavin  wrote:



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database design question

[dd]

Yes Simon. I am looking for it. ON DELETE RESTRICT.

I got the answer.

Should I enable foreign key support to use on delete restrict?(
http://www.sqlite.org/foreignkeys.html)

I have x databases without enabling foreign key support. Can I enable
foreign key support for x+1 database only?



On Wed, Jun 12, 2013 at 2:01 PM, Simon Slavin  wrote:

>
> On 12 Jun 2013, at 9:49am, dd  wrote:
>
> >>> Book titles are not unique.
> >  I agree. 2. Books   :  columns(BookId_primarykey,
> > Book_id_in_string_format_like_guid)
>
> I’m not sure why you would need a GUID.  You can store
>
> 2. Books   :  columns(BookId_primarykey, Title)
>
> Just don’t put a restriction on it that the title must be unique.  The
> BookId is unique and that is enough.
>
> >
> >>> Why is there a problem when there is an author without any books?
> >  No problem. But, my app need to delete author record from author
> > table when author doesn't have any books. How to handle this? (I can
> verify
> > in author table, whether this author belongs to any other book when book
> > deletion. If no, delete from author table. I am not happy with this
> > solution )
>
> The normal way to do this in a SQLite database would be to use a FOREIGN
> KEY relationship:
>
> 
>
> You would have something like this:
>
> 1. Authors:   columns(AuthorId, Name, SSN)
> 2. Books   :  columns(BookId, Title, AuthorId,
> FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId))
>
> You could set the database up in two ways:
>
> (A) ON DELETE CASCADE would allow you to delete an author with books, and
> would delete their books too.
> (B) ON DELETE RESTRICT would make SQLite issue an error message if you
> tried to delete an author who still had books in the Books table.
>
> SQLite will do this itself automatically.  You do not need to write your
> own code to look at the other table.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database design question

[RSmith]

Hi there,

May I ask, if you have a one-to-one relation of Books vs. Authors and wish to maintain it as such, why do you not just have one 
table with both fields and appropriate indexes?  Searches wouldn't really be much faster, but all this deletion maintenance would go 
right down to near nothing.


On 2013/06/12 11:55, dd wrote:

Thanks for your response.

It will delete from Author_Books when book deleted.

I am trying minimize queries on Authors table. For every deletion of record
from Books, it needs to check in Authors table. One extra query execution
is there. I am trying minimize that.


On Wed, Jun 12, 2013 at 1:00 PM, Clemens Ladisch  wrote:


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database design question

[Simon Slavin]

On 12 Jun 2013, at 9:49am, dd  wrote:

>>> Book titles are not unique.
>  I agree. 2. Books   :  columns(BookId_primarykey,
> Book_id_in_string_format_like_guid)

I’m not sure why you would need a GUID.  You can store

2. Books   :  columns(BookId_primarykey, Title)

Just don’t put a restriction on it that the title must be unique.  The BookId 
is unique and that is enough.

> 
>>> Why is there a problem when there is an author without any books?
>  No problem. But, my app need to delete author record from author
> table when author doesn't have any books. How to handle this? (I can verify
> in author table, whether this author belongs to any other book when book
> deletion. If no, delete from author table. I am not happy with this
> solution )

The normal way to do this in a SQLite database would be to use a FOREIGN KEY 
relationship:



You would have something like this:

1. Authors:   columns(AuthorId, Name, SSN)
2. Books   :  columns(BookId, Title, AuthorId,
FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId))

You could set the database up in two ways:

(A) ON DELETE CASCADE would allow you to delete an author with books, and would 
delete their books too.
(B) ON DELETE RESTRICT would make SQLite issue an error message if you tried to 
delete an author who still had books in the Books table.

SQLite will do this itself automatically.  You do not need to write your own 
code to look at the other table.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database design question

[dd]

Thanks for your response.

It will delete from Author_Books when book deleted.

I am trying minimize queries on Authors table. For every deletion of record
from Books, it needs to check in Authors table. One extra query execution
is there. I am trying minimize that.


On Wed, Jun 12, 2013 at 1:00 PM, Clemens Ladisch  wrote:

> dd wrote:
> > my app need to delete author record from author table when author
> > doesn't have any books. How to handle this? (I can verify in author
> > table, whether this author belongs to any other book when book
> > deletion.
>
> You mean the Author_Books table.
>
> > If no, delete from author table.
>
> That's how it's done.
>
> > I am not happy with this solution
>
> Why not?
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database design question

[Clemens Ladisch]

dd wrote:
> my app need to delete author record from author table when author
> doesn't have any books. How to handle this? (I can verify in author
> table, whether this author belongs to any other book when book
> deletion.

You mean the Author_Books table.

> If no, delete from author table.

That's how it's done.

> I am not happy with this solution

Why not?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database design question

[Clemens Ladisch]

dd wrote:
>   I am working on sample database application. I want to store book names
> and authors.
>
> 1. Authors:   columns(AuthorId_primarykey, Name, SSN)
> 2. Books   :  columns(BookId_primarykey, Title)//Title is unique

Book titles are not unique.

> 3. Author_Books: columns(AuthorId_primarykey, BookId_primarykey)
>
>   I am facing an issue with deletion of records from Books table. One
> author may belongs to morethan one book. How to handle this scenario? Is
> there technique like shared_ptr.

Why is there a problem when there is an author without any books?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users