none
(Sumber: milis SQL Server) Constraint untuk DELETE RRS feed

  • Pertanyaan

  • Dear rekan-rekan,

    Saya sedang mendesign suatu table, di mana dibutuhkan constraint untuk mencegah data dihapus secara tidak berurutan.

    Berikut adalah detail table-nya:

    - unitId (int, not null, PK)

    - sequence (tinyint, not null, PK)

    - otherField1

    - otherField2

    - otherField3

    unitId adalah foreign key dari parent table, jadi value-nya mengikuti PK parent table. Sequence sendiri, hanyalah angka urut 1,2,3,4,5,... dan unique per unitId.

    Jadi contoh data yang valid adalah sebagai berikut: unitId, sequence 101, 1 101, 2 101, 3 101, 4 112, 1 112, 2 112, 3 231, 1 231, 2 231, 3 231, 4 231, 5

    Yang harus dicegah adalah apabila user mencoba untuk delete baris (101,3) di mana baris (101, 4) masih exist; atau delete baris (112, 2) di mana baris (112, 3) masih exist.

    Sehingga data di bawah ini adalah data yang invalid: unitId, sequence 101, 1 101, 2 <DELETED> 101, 4 112, 1 <DELETED> 112, 3 231, 1 231, 2 231, 3 231, 4 231, 5

    Apakah hal ini bisa dicapai? Mohon pencerahan rekan-rekan sekalian.


    Best Regards,
    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Senin, 01 April 2013 13.41
    Moderator

Jawaban

  • Setelah menelusuri CHECK CONSTRAINT lebih details, sepertinya untuk kasus saya ini tidak bisa menggunakan constraint.

    Berikut adalah kutipan dari MSDN:

    “CHECK constraints are not validated during DELETE statements. Therefore, executing DELETE statements on tables with certain types of check constraints may produce unexpected results.”

    http://msdn.microsoft.com/en-us/library/ms188258(v=sql.100).aspx

    Berarti tidak ada pilihan lain, selain melakukan validasi data dengan trigger.


    Berikut adalah code final-nya:

    CREATE TRIGGER [unitSales].[unitMutation_deleteTrigger]
    ON [unitSales].[unitMutation]
    AFTER DELETE AS
     
    BEGIN
          DECLARE @rowsAffected int
          
          SET @rowsAffected = @@ROWCOUNT
          IF @rowsAffected = 0 RETURN
          
          SET NOCOUNT ON
          SET ROWCOUNT 0 
          
          IF EXISTS (SELECT t.unitId FROM
                                  (SELECT um.unitId, um.sequence, ROW_NUMBER() OVER (PARTITION BY um.unitId ORDER BY um.sequence) AS validSequence
                                  FROM [unitSales].unitMutation  AS um
                                  WHERE EXISTS (SELECT * FROM deleted WHERE deleted.unitId = um.unitId)) t
                            WHERE t.sequence <> t.validSequence)
                BEGIN
                      RAISERROR ('Violation of sequence in object ''unitSales.unitMutation''.', 16, 1)
                      IF @@TRANCOUNT > 0
                            ROLLBACK TRANSACTION
                END
    END

    Case closed



    Best Regards,
    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.



    Senin, 01 April 2013 13.49
    Moderator

Semua Balasan

  • Bs saja dgn membuat constrain ,yg boleh didelete adalah max(seq) dr unit id.jika bukan ya diabaikan.

    Bs jg dgn cara ,boleh mendelete seq apa sj dari sebuah unitid,dan setelah itu,seq yg tersisa di generate ulang shg menjadi berurutan kembali

    Dijawab oleh: Satrian


    Best Regards,
    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Senin, 01 April 2013 13.44
    Moderator
  • Untuk solusi yang ke-2 (generate ulang sequence), tidak bisa kami lakukan sebab akan menyalahi business process.

    Untuk solusi yang pertama (dengan membuat constrain), memang ini yang kami mau lakukan. <o></o>

    Apakah bisa dijelaskan lebih details? 

    Bagaimana caranya untuk membuat CHECK constraint yang bisa mengecek apakah suatu field berurutan atau tidak.

    Mengingat CHECK constraint yang sifatnya berupa declaration TSQL, atau memang bisa berupa script seperti stored procedure?


    Best Regards,
    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Senin, 01 April 2013 13.45
    Moderator
  • Coba ini

    Delete a

    From tableA

    Cross apply(select seqno=max(seqno)from tableA x where x.unitid=a.unitid)

    where unidid=@unitid and b.seqno=@seqno

    Akan menghapus record kalo seqno yg dmskkan ada max seqno dr suatu unitid,jika tdk akan diabaikan

    Dijawab oleh: Satrian


    Best Regards,
    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Senin, 01 April 2013 13.46
    Moderator
  • Setelah menelusuri CHECK CONSTRAINT lebih details, sepertinya untuk kasus saya ini tidak bisa menggunakan constraint.

    Berikut adalah kutipan dari MSDN:

    “CHECK constraints are not validated during DELETE statements. Therefore, executing DELETE statements on tables with certain types of check constraints may produce unexpected results.”

    http://msdn.microsoft.com/en-us/library/ms188258(v=sql.100).aspx

    Berarti tidak ada pilihan lain, selain melakukan validasi data dengan trigger.


    Berikut adalah code final-nya:

    CREATE TRIGGER [unitSales].[unitMutation_deleteTrigger]
    ON [unitSales].[unitMutation]
    AFTER DELETE AS
     
    BEGIN
          DECLARE @rowsAffected int
          
          SET @rowsAffected = @@ROWCOUNT
          IF @rowsAffected = 0 RETURN
          
          SET NOCOUNT ON
          SET ROWCOUNT 0 
          
          IF EXISTS (SELECT t.unitId FROM
                                  (SELECT um.unitId, um.sequence, ROW_NUMBER() OVER (PARTITION BY um.unitId ORDER BY um.sequence) AS validSequence
                                  FROM [unitSales].unitMutation  AS um
                                  WHERE EXISTS (SELECT * FROM deleted WHERE deleted.unitId = um.unitId)) t
                            WHERE t.sequence <> t.validSequence)
                BEGIN
                      RAISERROR ('Violation of sequence in object ''unitSales.unitMutation''.', 16, 1)
                      IF @@TRANCOUNT > 0
                            ROLLBACK TRANSACTION
                END
    END

    Case closed



    Best Regards,
    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.



    Senin, 01 April 2013 13.49
    Moderator
  • ada baiknya di test dulu dengan tool kaya SQL Query stress untuk ngetes deadlock issues ato concurrent issues

    e.g. ada 10 connections pada saat bersamaan mau update table yg sama, unitid yang sama, tapi beda sequence (cuma contoh saja). 

    Dijawab oleh: Fendy


    Best Regards,
    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Senin, 01 April 2013 13.49
    Moderator