Is an inconsistent state allowed in a transaction?

Question

I have a very simple transaction question. (in sql server 2000, but I assume this 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 of which refers to the other (tblForeingKey.ForeignKey refers to tblPrimaryKey.PkID). Now I have some logics that change the primary key tab by deleting and reinserting the key.

Once deleted, the database will act in an inconsistent state. I looked at my old script, where I first dropped the relationship and recreated it afterwards. But my question is this: I found out that the transaction is atomic, so an intra-consistent state of the transaction is allowed.

So, I think something like this should work:

BEGIN TRAN eg

    DELETE tblPrimaryKey WHERE PkId = 3     
    INSERT INTO tblPrimaryKey  SELECT 3

COMMIT TRAN eg

. - , ?

:

, .

, .

, ?

:

, . - , : sql script, , . , , . script .

, , , , . , , . sproc, , - , , .

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

, . COMMIT , . , , -, SQL Server. , , :

alter table tblForeignKey
  modify constraint YourFKNameHere
    deferrable
    initially deferred;

, (, , ).

SET CONSTRAINT[S] , . :

set constraint YourFKNameHere deferred;

, ACID, , . , , . (, ) , (), (Atomicity), (Consistency).

Answer 2

: , , .

, "". "", , - . , .

Foreign-Keys, , ( ).

, , , SQL Server 2005 FK, 2000 . , . BP

1) NOT , , , OR,

2) ( ) , .

( , ).

. ( , Serializable ).

. NOCHECK:

    -- 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;

. ( BOL).

1. SQL 2000, , . 2005 .

2: "DEFERRABLE" - Oracle. SQL Server.

Answer 3

ACID , . .

SQL, , ForeignKey NULL.

DECLARE @FKTabIDs (FKTabID int)

BEGIN TRAN eg

    INSERT FKTabIDs (FKTabID) SELECT [Id] FROM tblForeignKey WHERE ForeignKey = 3

    --Assumes NULL but could use any valid value
    UPDATE tblForeignKey SET ForeignKey = NULL WHERE ForeignKey = 3

    DELETE tblPrimaryKey WHERE PkId = 3         
    INSERT tblPrimaryKey SELECT 3

    UPDATE tFK
    SET ForeignKey = 3
    FROM tblForeignKey tFK JOIN @FKTabIDs tv ON tFK.[Id] =  tv.FKTabID
    --... or use exists, in etc if you prefer

COMMIT TRAN eg

Answer 4

, tabel , .

, DELETE/INSERT , ? , tblForeignKey .