On Mon, May 10, 2010 at 10:58 AM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> It should not be possible to have circular dependencies. Because you somehow
> got the data in in the first place, and /that/ wouldn't have been possible
> had you had circular dependencies. Part of normalising your data structure
> includes making sure that you haven't duplicated data.
I meant foreign keys that cause tables to have circular relationships.
For example, a customer might have a list of credit cards (the credit
cards table has a customer id) and the customer has a default credit
card (the customer table has a credit card id). I realize you could
make a third table to store the "default credit card" relationship,
but as you observed, I'm looking at the general case.
> I do note that you appear to be trying to solve an extremely general case, as
> if you, the programmer, have no idea why your schema is the way it is. I
> have to warn you that if you're going to solve the general case, you are
> going to run into situations which are not solvable without considering
> individual rows of a table.
Indeed, hence why it's so complicated without using deferred
constraints. Unfortunately, enabling deferred constraints leads to
later detection of errors during typical development. With immediate
constraints, even using null in fields temporarily might not solve the
issue, since there may be not-null constraints to deal with. In any
case, suffice to say that it is indeed quite complicated. Getting
back to one of the points that started this conversation, the complex
nature of operations needed to transactionally modify tables with
foreign key integrity preservation suggests to me that this would be
something the database engine could provide a helping hand with. It
wouldn't necessarily need to be full support for alter table, other
options presented previously would also help.
> But this gets back to an earlier point of mine: why go through any of this
> performance at all ? Why do you need to create temporary copies of databases
> only to originally delete and replace the originals ?
The simplified example I provided had no changes to the tables, but in
the real scenario, at least one of the tables will need some sort of
modification.
Thanks for the detailed replies. I appreciate your insight.
Patrick Earl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
