Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: TEXT datatype with CHECK constraint

    Hi,

    I require a column to be created with CHECK constraint NOT NULL which has TEXT datatype. But SQL Server will not allow CHECK constraints for the columns which has TEXT datatype. How can I solve this problem ? Is there any other alternative for this ?

    With Warm Regards,
    Sam.

  2. #2
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51
    perhaps you could implement your constraint code in an after insert and/or after update trigger?

  3. #3
    Join Date
    Mar 2004
    Posts
    205
    Thank you for your reply. Is it possible to give in before insert/update ?

  4. #4
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51
    Originally posted by SamCute
    Thank you for your reply. Is it possible to give in before insert/update ?
    You can raise an error in your AFTER trigger that will rollback the entire transaction.

    I'm not really sure what the FOR INSERT / FOR UPDATE phrases do, but maybe they are a better option in your case than INSTEAD OF

  5. #5
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    avoid rolling back triggers because of the overhead involved. Instead of letting the trigger find a problem and rolling back a transaction, instead catch the error before it can get to the trigger (if possible based on your code). Catching an error early (before the trigger fires) consumes much fewer server resources than letting the trigger roll back.

    Catching errors before a transaction can fire a trigger can be done through code in the initiating transaction, and it sometimes can be accomplished by adding a constraint to the table to catch common mistakes. If a constraint catches an error, the trigger will not fire.

  6. #6
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51
    Originally posted by harshal_in
    avoid rolling back triggers because of the overhead involved. Instead of letting the trigger find a problem and rolling back a transaction, instead catch the error before it can get to the trigger (if possible based on your code). Catching an error early (before the trigger fires) consumes much fewer server resources than letting the trigger roll back.

    Catching errors before a transaction can fire a trigger can be done through code in the initiating transaction, and it sometimes can be accomplished by adding a constraint to the table to catch common mistakes. If a constraint catches an error, the trigger will not fire.
    I think the point is that Sam wants to replicate the functionality of a check constraint on a text column (constraints apparently cant be applied to text columns), not have to write external code to police his 'constraint' in each location that his table is updated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •