question

arunkumar112 avatar image
arunkumar112 asked

Constraint Custom Message

How can we set custom message for user defined constraint for Table or any object for sql. ALTER TABLE SaleProduct ADD constraint chkshipDate CHECK(shipDate>=OrderDate) I want to show if user violating this rule during entry. like "Ship date must be later then order date' Please suggest
constraints
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
That looks like the sort of thing that should be handled by the UI before the data is sent back to the database.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
I totally agree with Thomas Rushton that the best place would be to handle it at the GUI. This pre-validation is easier and save the round trip to database server and would save the resources. Hence a better performance over all. But if you have encapsulated the logic of INSERT/UPDATE in procedures, then you can have a workaround by comparing the dates in the procedure before INSERT/UPDATE. Then you can raise the customized error by using RAISERROR.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
As both @Tomas and @Usman mentioned, the best place is the UI to handle this kind of checks. Except stored procedures if you really want to fire a custom message, there is another possibility by using TRIGGERS. You can file an exception in the trigger with whatever custom message you want. However once again, the best place is GUI. Even you do not want to handle all the things in GUI, you can catch exception related to violation of the constraints in GUI and show appropriate custom message.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Triggers probably come the closest to giving him what he is asking for. But I agree wtih everyone, this should be handled outside of the database in an area like the UI.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.