Constraint On Column

Hmm,

I'm creating a table that contains information from two other tables. I'd like to have a constraint on some columns so that thay can only contain information that exists in the other two tables. This is what I got:

create table dbo.KUSE_Bills
(
SERVICE_ID int IDENTITY(1,1) NOT NULL primary key,
BILLDATE datetime NOT NULL,
CNAME varchar(100) NOT NULL,
SNAME varchar(100) NOT NULL,
PRICE money NOT NULL,
UNIT varchar(50) NOT NULL,
NUMBER integer NOT NULL,
BILLSUM money NOT NULL
)
GO

I tried something like this:

create table dbo.KUSE_Bills
(
SERVICE_ID int IDENTITY(1,1) NOT NULL primary key,
BILLDATE datetime NOT NULL,
CNAME varchar(100) NOT NULL,
SNAME varchar(100) NOT NULL,
PRICE money NOT NULL,
UNIT varchar(50) NOT NULL,
NUMBER integer NOT NULL,
BILLSUM money NOT NULL

CONSTRAINT chk_cname CHECK (CNAME NOT IN (
SELECT CNAME FROM KUSE_Customer))
)
GO

But subqueries are not allowed...

So how can I do it?