ADD CHECK CONSTRAINT error
-
I tried to add a new constraint :
ALTER TABLE dbo.Issuers
ADD CONSTRAINT Chk_Issuers_Rating_MustBe_In_StringMapping_Where_TypeEquals_CREDIT_RATING
CHECK
(
Rating IN (SELECT [Name] FROM StringMapping WHERE Type = 'CREDIT_RATING')
AND
Rating != 'D'
)
It returned an eror:
Server: Msg 8142, Level 16, State 1, Line 1
Subqueries are not supported in CHECK constraints, table 'dbo.Issuers'.
How can I include this "Rating IN (SELECT [Name] FROM StringMapping WHERE Type = 'CREDIT_RATING')"
in my new constraint .
I can't use triggers.
-
For This Kind of things you may want to use a FUNCTION!
Like
CREATE FUNCTION dbo.udf_RatingExists(@Rating varchar (20))
returns int
as
begin
if exits(select * from FROM StringMapping WHERE Type = 'CREDIT_RATING' and Name = @Rating)
return 1
else
return 0
end
AND modify the constraint to:
ALTER TABLE dbo.Issuers
ADD CONSTRAINT Chk_Issuers_Rating_MustBe_In_StringMapping_Where_TypeEquals_CREDIT_RATING
CHECK
(
dbo.udf_RatingExists(Rating 
= 1)AND
Rating != 'D'
)
Keep in mind that if your table is large this may slow you down
HTH
* Noel -
Hi noeld,
Thanks for a good idea.
I created a function.
But something is wrong in this part...
...CHECK
(
dbo.udf_RatingExists(Rating = 1)...
It gives me an error
-
Check Parenthesis. I may have a written those incorrectly
(dbo.udf_RatingExists(Rating) = 1
AND ... )
HTH
* Noel -
You were absolutely right - parenthesis.
It all works great now!
Than you so much, NOELD!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply