check constraints

Hi all DBA:

     Would Oracle allow one table's check constraints to refer to another table's column name, also would subquery be allowed in check constraint in any version of oracle?

     Foreign key would be the best way for the example below, but I am interested to know if check constraint is possible in this case!

   For example :

         create table dept(
                   deptno   number,
                   name     varchar2(100)


         );


         create table emp (
                   empno    number,
                   name     varchar2(100),
                   deptno   number constraint emp_pk_deptno
check(exists(select * from emp where emp.deptno = dept.deptno))


         );

   Thanks
   Sam Hou

Sent via Deja.com
http://www.deja.com/ Received on Thu Jan 25 2001 - 22:13:17 CST