Toad World® Forums

Foreign key to table with Computed Persisted Primary Key has no type!


#1

I have a table with a primary key that is a persisted computed column. I have a relationship from this table to another table. When I generate the DDL, the other table has the column name but has a blank type:

e.g. It generates something like:

CREATE TABLE [MyTable]
(
[MyTableId] AS datepart(YEAR,ReferenceDate) * 10000 + datepart(MONTH, ReferenceDate) * 100 + datepart(DAY, ReferenceDate) PERSISTED NOT NULL,
[ReferenceDate] Date NOT NULL,
[DayOfWeek] AS upper(datename(DW,ReferenceDate))
)

CREATE TABLE [OtherTable]
(
[OtherTableId] Int NOT NULL,
[MyTableId] AS , – HERE IS THE PROBLEM (AND YOU CAN’T OVERRIDE THE TYPE IN TOAD MODELER!)
[Value] Float NOT NULL
)


#2

Hi,

currently there is no workaround. TDM requires both parent and child columns to be of the same datatype (there are exceptions, but computed column is not there).

I created new change request: 160534 and we will look into the matter. The problem is that computed column has no fixed data type. In one table it can be numeric in another char or something similar. TDM by design requires consistency and doesn’t allow you to map integer to char etc. In any case, we will try to find some solution.

Thanks,

Vaclav