Post reply

check constraint before insert

  • December 2, 2011 at 9:50 am

    #260851

    Hi,

    is there a way to check constraints before the INSERT INTO command is executed?

    I would like inserts only valid data before a Bulk Insert fails complete (Rollback). Not valid records should be marked in.

    Thanks

    Nicole 😉

  • December 2, 2011 at 9:56 am

    #1416038

    info 58414 (12/2/2011)

    Hi,

    is there a way to check constraints before the INSERT INTO command is executed?

    I would like inserts only valid data before a Bulk Insert fails complete (Rollback). Not valid records should be marked in.

    Thanks

    Nicole 😉

    It may sound silly (bcoz 2*execution time) but create a replica of your table with all constraints. Try to insert on it (may be with loop / cursor) and mark individual record good / bad.

    OR

    Write Instead of Trigger...

  • December 5, 2011 at 8:11 am

    #1416718

    You could disable the constraints, check the data, delete invalid rows and enable the constraints again.

    -- Gianluca Sartori

  • December 5, 2011 at 8:20 am

    #1416722

    Gianluca Sartori (12/5/2011)

    You could disable the constraints, check the data, delete invalid rows and enable the constraints again.

    By disabling the constraint, you are allowing to enter bad data. Then it requires a Cleanup. If cleanup logic has any loophole OP would be in trouble because data has already been inserted into tables.

    Also, if this cleanup takes significant time the table would be exposed without check for long duration.

  • December 5, 2011 at 8:29 am

    #1416728

    Dev (12/5/2011)

    Gianluca Sartori (12/5/2011)

    You could disable the constraints, check the data, delete invalid rows and enable the constraints again.

    By disabling the constraint, you are allowing to enter bad data. Then it requires a Cleanup. If cleanup logic has any loophole OP would be in trouble because data has already been inserted into tables.

    Also, if this cleanup takes significant time the table would be exposed without check for long duration.

    You have a good point, I agree with you.

    It's just another option, with pros and cons.

    -- Gianluca Sartori

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply