check constaints problem, integrity checking

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

SQL Server 2000 Forums

Transact-SQL (2000)

check constaints problem, integrity checking

Author

Topic

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-05-01 : 02:36:34

hello guyz, i need your advice with this one....


     tblRegistry  (Registry_Key int, Parent_key int, Data varchar)
     tblSubscribers(Account_No int, Account_Name, Registry_Key)

     sample data:
     
     tblregistry
       Registry_Key   Parent_Key  Data
       1              1           Main Registry
       2              1           Collectors
       3              2           Will Smith
       4              2           John Smith
     tblsubscribers
       Account_No     Account_Name    Registry_Key
       1              Sample Name     3
       2              New Name        4
      
     my problem is i want to check the integrity of the database 
     so that it registry_key from tblsubscribers will relate to 
     registry_key from tblregistry (where tblregistry having       
     parent_key=2)  
 
     i tried this one with check constraints 
          registry_key in (select registry_Key from tblregistry where
                             parent_key = 2)

           BUT IT DOESNT WORK.

     is there a way to solve this problem with sql.

TCC

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-02 : 02:27:30

parent_key is constrained by registry_key so your criteria might as well be

registry_key in (select registry_Key from tblregistry where
registry_key = 2)

or you can use exists

if exists(select * from tblregistry where registry_key=2)

or a join...

select s.* from
tblsubscribers s
join tblregistry t
on s.registry_key=t.registry_key
where s.registry_key=2

--just to get you started