Problem with Constraints... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with Constraints...

Author

Topic

johnyan
Starting Member

4 Posts

Posted - 2007-06-26 : 11:34:17

Hi All,

I m trying to add contraints to my table and i am using subqueries in condition. it is giving me error.
can you please let me know how can i do that.

Below is my Query:

ALTER TABLE employee
ADD CONSTRAINT ck_employerID CHECK (10 <= (select count(ee.employer_id) from employertype elt,employer el,employee ee,employeetype et
where elt.employer_type_id=el.employer_type_id and elt.employer_type='Department' and el.employer_id=ee.employer_id
and ee.type_id=et.type_id and et.employee_type='Doctor' group by ee.employer_id))

Thanks in advance.

please help.

-john

pootle_flump

1064 Posts

Posted - 2007-06-26 : 12:19:25

Hi John

You can't reference other tables in your check constraint. You can however reference the return of a function....

johnyan
Starting Member

4 Posts

Posted - 2007-06-27 : 05:37:46

Thanks for your reply.

Can u please let me know if i want to achieve which i am aiming how can i do that.

is there any way?

please help.

Thanks

-john

pootle_flump

1064 Posts

Posted - 2007-06-27 : 07:07:45

Hi Jon

You have already done the hard bit. Take the SQL that produces a count and put it into a scalar function. Return the count so your constraint becomes something like:

ADD CONSTRAINT ck_employerID CHECK (10 <= dbo.MyCountFunction())

Have a look at CHECK CONSTRAINTS in Books OnLine - there is an example of exactly what you want to do.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources