Is Inconsistent state allowed in a transaction?

Question

I have a very simple question about transactions. (In sql server 2000, but I guess it applies to general db. transactions).

tblPrimaryKey

PkId        
-----
1
2
3

tblForeignKey

Id   ForeignKey  
---- ----- 
1    1
2    2
3    3
4    1

I have 2 tables, one referencing the other (tblForeignKey.ForeignKey references tblPrimaryKey.PkID). Now I have some logic that alters the table of the primary key, by deleting and reinserting a key.

After deleting, the database would be of course in an inconsistent state. I looked at on old script of mine where I first dropped the relationship and recreated it afterwards. But my question is this: I learned that a transaction is atomic, so inside of a transaction inconsistent state is allowed.

So I guess something like this should work:

BEGIN TRAN eg

    DELETE tblPrimaryKey WHERE PkId = 3     
    INSERT INTO tblPrimaryKey  SELECT 3

COMMIT TRAN eg

But this doesn't work. Can someone provide me with an example of a working transaction that applies this logic?

UPDATES :

Consistency This characteristic means that the database should be consistent before and after the transaction.

In no case can a partial transaction be committed to the database since that would leave the database in an inconsistent state.

Doesn't this imply that in the transaction inconsistency is possible?

UPDATE :

Some have asked me why I didn't use an update in this case. Kind of complicated but I give it a go: the sql needed was part of a publication script that build tables from views, and then updated those tables. Since the views contained the publicationmodel, alterations of the view were made there, and only there. The rest of the script could not rely on column names to do the update.

Of course I could query for those columnnames, but it seem like a hassle at the time, so I chose not to, and instead drop constraints and rebuild them. Now I must admit I wasn't feeling comfortable myself with that solution, so now I use indeed an update instead. I wrote a sproc to do that, if anyone nows an other solution, please let me know.

CREATE PROC usp_SyncRecords
(
 @tableName1 as nvarchar(255),
 @tableName2 as nvarchar(255), 
 @joinClause as nvarchar(255),
 @whereClause as nvarchar(1000)
)
-- this proc updates all fields in table 1 that have corresponding names 
-- in table2 to the value of the field in table2.
AS 
BEGIN 
    DECLARE @sqlClause nvarchar(4000)
    DECLARE @curFieldName nvarchar(255)
    DECLARE @sqlColumnCursorClause nvarchar(1000)
    SET @sqlClause = 'UPDATE [' + @tableName1 + '] SET '

    -- get FieldNames for second table 
    SET @sqlColumnCursorClause = 
        'DECLARE cur CURSOR FAST_FORWARD FOR SELECT name FROM syscolumns ' + 
        'WHERE id=' + CAST(object_id(@tableName2) as nvarchar(50))

    EXEC sp_executeSql @sqlColumnCursorClause


    OPEN cur
        -- compose sqlClause using fieldnames
        FETCH NEXT FROM CUR INTO @curFieldName
        WHILE @@fetch_status <> -1 
        BEGIN 
            SET @sqlClause = @sqlClause + @curFieldName  + '=' +
                                                      @tableName2 +  '.' + @curFieldName  + ','
            FETCH NEXT FROM CUR INTO @curFieldName
        END

    CLOSE cur 
    DEALLOCATE cur 

    -- drop last comma 
    SET @sqlClause = LEFT(@sqlClause,LEN(@sqlClause) -1)

    -- adding from/join/where clauses 
    SET @sqlClause = @sqlClause + ' FROM [' + @tableName1 + '] INNER JOIN [' + @tableName2 + '] '
               + 'ON ' + @joinClause +  ' WHERE '  +  @whereClause

    EXEC sp_executeSQL @sqlClause

END

Answer 1

But my question is this : I learned that a transaction is atomic, so inside of a transaction inconsistent state is allowed.

That is not what "Atomic" means. Atomic means "indivisible", and for databases this simply means that a transaction is an all or nothing affair. Transactional integrity requires that the transaction be either entirely committed or entirely rolled-back.

None of this has anything to do with Foreign-Keys, which are one of the means of insuring Referential integrity, which is a different thing (though related).

As for what you are trying to do, I know that in SQL Server 2005 you can temporarily DISABLE the FK's, and this might be in 2000 as well. Hwoever, this is not usually considered best practice. Instead, BP is to either

1) NOT Delete the parent-key value, but update the row instead, while preserving the parent-key value, OR,

2) If you intend to delete (or change) the parent-key permanently, then you should delete or reassign the child records first.

Structural inconsistency is never supposed to be visible to the users (if so, then you are structurally corrupted).

Transactional inconsistency is only allowed within a transaction. It should never be visible outside of the transaction (except that isolation levels lower than Serializable allow it to some extent).

Referential inconsistency has nothing to do with these two. However, in most cases referential integrity can be disabled through the use of the NOCHECK option:

    -- Disable the constraint.
ALTER TABLE cnst_example NOCHECK CONSTRAINT FK_salary_caps;

--Do stuff that violates RI here:

-- Reenable the constraint.
ALTER TABLE cnst_example WITH CHECK CHECK CONSTRAINT FK_salary_caps;

However, this is NOT the preferred way. The preferred way is to make the changes in the correct order (this is straight out of BOL).

NOTE1: I do not have access to SQL 2000 so I do not know if the above works there. It works in 2005.

NOTE2: "DEFERRABLE" is an Oracle setting. It is not valid for SQL Server.

Answer 2

The cleanest solution would be to make the foreign key constraint deferred. This will postpone the checking of the constraint until COMMIT time, allowing it to be violated temporarily during the transaction. Unfortunately, this feature is apparently not available in SQL Server. On systems that do support deferred constraints, something like the following would work:

alter table tblForeignKey
  modify constraint YourFKNameHere
    deferrable
    initially deferred;

Some systems do not allow you to change a constraint's deferrability, in which case you would have to re-create the constraint (and possibly the table).

The SET CONSTRAINT[S] statement can be used to toggle a constraint's deferredness, e.g. at the beginning of the transaction:

set constraint YourFKNameHere deferred;

In my experience, the ACID properties, while clearly distinct, tend to work together. For example, in your problem, you are trying to do an update which is temporarily invalid. Other users will not see any of your changes (Isolation, Atomicity) until you commit them (Durability), and no part of your transaction will have any effect (Atomicity) unless your transaction ends with the database in a consistent state (Consistency).