Re: Too much integrity?

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Sep 23, 1:28 am, Phil <.@.> wrote:
Jamie Collins wrote:
...Agreed, it sounds like multiple 1:1 relationships between the
'items' and the specialist lists' items...

Actually, Pat's earlier message convinced me that what I have IS an m2m
relationship, only I wasn't seeing it that way.

I'm still not not seeing it that way but it's your model :)

The underlying problem is that the SQL language has no concept of
multiple assignment and although the SQL-92 standard includes
deferrable constraints Jet does not support them.

I'm not 100% sure what that means, but I'm pretty sure I could work
around my problem with transactions - if I were at the point of writing
code; but apparently Access has no way of manually starting or ending a
transaction. So far, I've been doing my testing by removing the
relationships, adding test data, and recreating the relationships
afterward - which is a pain in the neck.

You can indeed explicitly start a transaction e.g. ANSI-92 Query Mode
syntax supports BEGIN TRANSACTION, COMMIT TRANSACTION and ROLLBACK
TRANSACTION. The problem you'll have is that instead of being able to
defer constraints (possible with full SQL-92 syntax SET CONSTRAINTS
<constraint names list> DEFERRED) you would have to DROP the
constraints within the transaction, which would cause a table lock.
Further note that a Jet PROCEDURE can only comprise one SQL statement,
whereas the above logic requires at least six SQL statements e.g.
pseudo code:

BEGIN TRANSACTION
;
ALTER TABLE MyTable DROP CONSTRAINT my_constraint
;
-- sql update(s) here e.g.
INSERT INTO MyTable (my_col) VALUES (0)
;
ALTER TABLE MyTable ADD CONSTRAINT my_constraint CHECK (...);
;
-- adding a constraint does not necessarily mean
-- it will get checked, therefore do something to
-- trigger it here e.g.
UPDATE MyTable
SET my_col = my_col
;
COMMIT TRANSACTION
;

The mechanisms to execute multiple statements in appropriate order,
control of flow, error handling etc must be done in the 'front end'
rather than SQL code :(

Jamie.

--


.

Relevant Pages

  • Re: row vs row.column level locking ++ constraints and TAPIs
    ... Any transaction which would update ... >> in the year 2003 (deadline for the HIPPA implementation). ... column dependencies & constraints -- these don't violate relational ... Any database that does not have proper ...
    (comp.databases.oracle.server)
  • Re: isolation level serializable
    ... end loop; ... on to disable all constraints on the destination tables, ... set transaction isolation level serializable; ... Each transaction should commit or rollback itself when ...
    (comp.databases.oracle.server)
  • Re: isolation level serializable
    ... end loop; ... on to disable all constraints on the destination tables, ... set transaction isolation level serializable; ...
    (comp.databases.oracle.server)
  • Re: Possible to check whether a COMMIT will fail?
    ... The only way I can think of is to go through all the constraints you ... think might be involved in the transaction (use CONSTRAINT_TYPE, ... generic script or a specific one, but I can tell you that a generic one ... Prev by Date: ...
    (comp.databases.oracle.server)
  • Re: DB API 2.0 and transactions
    ... CURRENT_TIMESTAMP within a transaction should be the same. ... manadate that CURRENT_TIMESTAMP in only evaluated once in each SQL ... transaction-initiating SQL statement takes place. ... src = self.__cnx.source ...
    (comp.lang.python)