Belongs_To Relation To Non Primary Key

Hi all:

I’m working on a multi-tenant application that uses MS SQL Server schemas to separate tenant data. Both my tenant and user tables use surrogate primary keys [int identity(1,1)] but I want to relate the user to the tenant through a different column (User->TenantDbUser => Tenant->DbUser) that contains the tenant’s db user name and not the primary key. The reason is that when an app request starts I can switch the db connection to that belonging to the user’s schema. The tenant and user tables are the only shared tables in the database (dbo); each tenant’s schema will be encapsulated so there’s no need to store tenant references in the schema’s tables.

User model:

public function relations()


    return array(

	'tenant' => array(self::BELONGS_TO, 'Tenant', 'TenantDbUser'),



Everything works fine at the database level, but it seems to me that in the relations() method the BELONGS_TO relationship automatically looks for the parent’s primary key, which won’t work in this case.

Is there any way to work around this with stock Yii code or do I have to extend the CActiveRecord class to allow this functionality? Any ideas are welcome.

*** Update ***

Changing the relationship to:

'tenant' => array(self::BELONGS_TO, 'Tenant', 'SomeOtherIntegerColumn','on'=>'1=1 OR [tenant].[DbUser] = [t].[TenantDbUser]'),

solves the SQL Server error (casting an nvarchar to an integer) but doesn’t load the parent data. I reckon it’s because the foreign key in the table is still nvarchar. I’d really like NOT to add an integer column to use as foreign key.