9 Replies Latest reply on Jun 24, 2016 2:42 PM by rareddy

foreign keys in odata

mirco73 May 11, 2016 1:43 PM

Hi,

in a virtual table we've created a couple of foreign keys referring to the same field (the Primary Key) of another virtual table:

FK1: Table1.CurrentManager = Table2.EmployeeId

FK2: Table1.PMResponsible = Table2.EmployeeId

when we call http://prdteiid01:8080/odata/VDB.1/Entities.Table2(1234)

we correctly get the values of the table and the references to the other tables but we cannot distinguish the two foreign keys for it seems they have the same url and name

"Table1" : {

"__deferred" : {

"uri" : "http://prdteiid01:8080/odata/VDB.1/Entities.Table2(1234)/Table1"

}

}, "Table1" : {

"__deferred" : {

"uri" : "http://prdteiid01:8080/odata/VDB.1/Entities.Table2(1234)/Table1"

}

Is it possible to define them in order to have different urls ?

thanks,

Mirco

1. Re: foreign keys in odata

rareddy May 11, 2016 2:56 PM (in response to mirco73)

Mirco,

This is side effect of using table name for relationship instead of using the FK name (this is a design mistake). This only affects our OData V2 version. Did you try Teiid's OData V4? I would recommend using that, as V2 is being deprecated/removed in Teiid 9.x releases. V4 also has much more extensive feature set.

The corresponding V4 URL would be
http://prdteiid01:8080/odata4/VDB.1/Entities/Table2(1234)

Ramesh..
Actions
2. Re: foreign keys in odata

mirco73 May 30, 2016 4:50 AM (in response to rareddy)

Ramesh,
we're experimenting Odata V4 version, and as you said we solved the problem about the foreign keys.
I've one more question about this subject:
from the Odata specification I read that while defining an Entity, it's possible to add the "Partner" attribute in the "NavigationProperty" property in order to link two entities.
Is it possible with Teiid and if yes, how ?

thanks,
Mirco
Actions
3. Re: foreign keys in odata

rareddy May 30, 2016 9:54 PM (in response to mirco73)

Mirco,

The relationship between two tables will be modeled as NavigatoinProperty in the Entity in V4. So, if you have defined two related virtual tables, then define FK betweek them. Will that satisfy what you are looking for, or were you thinking something else?

Ramesh..
Actions
4. Re: foreign keys in odata

mirco73 Jun 24, 2016 6:44 AM (in response to rareddy)

Hi Ramesh,
that's my scenario:

EmployeeMasterEntity (PK: EmployeeID) is related to EmployeeActionEntity (PK: EMployeeActionKey) as 1 to n

We have created a Foreing Key in the EmployeeActionEntity view which refers to the PK of EmployeeMasterEntity

The odata2 behaviour is correct

For EmployeeID = 3 there are 3 EmployeeActionEntities:

http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/EmployeeActionEntity?$format=json

{
"d" : {
"results" : [
{
"__metadata" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeActionEntity('10009- - - -20140430-20120101-0')", "type" : "Entities.EmployeeActionEntity"
}, "EmployeeActionKey" : "10009- - - -20140430-20120101-0", "EmployeeID" : 3, "ActionType" : "Hiring", "EmploymentStatus" : "Active", "ValidFrom" : "\/Date(1325376000000)\/", "ValidUntil" : "\/Date(1398816000000)\/", "SequenceNumber" : 0, "ModifiedBy" : "DEMARIAM", "ModificationDate" : "\/Date(1322179200000)\/", "EmployeeMasterEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeActionEntity('10009- - - -20140430-20120101-0')/EmployeeMasterEntity"
}
}
}, {
"__metadata" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeActionEntity('10009- - - -20140531-20140501-0')", "type" : "Entities.EmployeeActionEntity"
}, "EmployeeActionKey" : "10009- - - -20140531-20140501-0", "EmployeeID" : 3, "ActionType" : "Organizational reassignment", "EmploymentStatus" : "Active", "ValidFrom" : "\/Date(1398902400000)\/", "ValidUntil" : "\/Date(1401494400000)\/", "SequenceNumber" : 0, "ModifiedBy" : "ZUCCHIATTIS", "ModificationDate" : "\/Date(1398729600000)\/", "EmployeeMasterEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeActionEntity('10009- - - -20140531-20140501-0')/EmployeeMasterEntity"
}
}
}, {
"__metadata" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeActionEntity('10009- - - -99991231-20140601-0')", "type" : "Entities.EmployeeActionEntity"
}, "EmployeeActionKey" : "10009- - - -99991231-20140601-0", "EmployeeID" : 3, "ActionType" : "Organizational reassignment", "EmploymentStatus" : "Active", "ValidFrom" : "\/Date(1401580800000)\/", "ValidUntil" : "\/Date(253402214400000)\/", "SequenceNumber" : 0, "ModifiedBy" : "DEMARIAM", "ModificationDate" : "\/Date(1400544000000)\/", "EmployeeMasterEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeActionEntity('10009- - - -99991231-20140601-0')/EmployeeMasterEntity"
}
}
}
]
}
}

For EmployeeActionKey = '10009-%20-%20-%20-20140430-20120101-0' I can see the data of the corresponding EmployeeMasterEntity (id = 3)

http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeActionEntity('10009-%20-%20-%20-20140430-20120101-0')/EmployeeMasterEntity?$format=json

{
"d" : {
"__metadata" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)", "type" : "Entities.EmployeeMasterEntity"
}, "EmployeeID" : 3, "ValidityDate" : "\/Date(1451606400000)\/", "CID" : 10009, "BadgeNumber" : 35, "BadgeCode" : null, "WindowsUser" : "GUARINO_G", "SAPUser" : "GUARINOG", "Salutation" : "ING.", "FirstName" : "Giuseppe", "LastName" : "Guarino", "Gender" : "M", "Initials" : "GG", "InternalPhone" : "0916419259", "Supervisor" : 5, "DeputySupervisor" : 0, "Delegate" : 5, "Company" : "1000", "Department" : "10000001", "Position" : "50000035", "CostCenter" : "1-BIC", "JobTitle" : "Chief Executive Officer", "AdditionalJobDescription" : "Deputy Chairman of the Board", "HiringDate" : "\/Date(457488000000)\/", "LeaveDate" : null, "EmployeeType" : "20", "SalaryCategory" : "STDIN", "EmailAddress" : "g.guarino@casale.ch", "Active" : true, "EnableTimeSheet" : false, "SendHoursToSAP" : true, "SendHoursToSAPSince" : null, "BudgetType" : "Internal", "RequiresApproval" : false, "HeadOfOrganizationalUnit" : true, "AutocompleteWorkloads" : false, "TimeSheetDelegate" : 0, "TimeSheetLockDate" : "\/Date(1062412722920)\/", "OfficeLocation" : "", "HoursPerMonth" : "173.33", "HoursPerWeek" : "40.00", "HoursPerDay" : "8.00", "DaysPerWeek" : "5.00", "HoursPerYear" : "2080.00", "BirthDate" : "\/Date(-697075200000)\/", "Nationality" : "CH", "VisitCardQuantity" : "200", "PhoneOnVisitCard" : false, "MobileOnVisitCard" : false, "PostalAddress" : "Via Ing. Luigi Casale, 7", "PostalCode" : "6932", "City" : "Breganzona", "State" : "TI", "Country" : "CH", "PrivatePhone" : " ", "FamilyStatus" : "Married", "FamilyStatusSince" : "\/Date(-2208988800000)\/", "NumberOfChildren" : "2", "Notes" : "", "thumbnailPhoto" : "..........=\r\n", "SAPData" : true, "AbsenceEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/AbsenceEntity"
}
}, "CountryEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/CountryEntity"
}
}, "EmployeeActionEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/EmployeeActionEntity"
}
}, "EmployeeFamilyEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/EmployeeFamilyEntity"
}
}, "CompanyEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/CompanyEntity"
}
}, "CostCenterEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/CostCenterEntity"
}
}, "CountryEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/CountryEntity"
}
}, "EmployeeMasterEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/EmployeeMasterEntity"
}
}, "OrganizationalUnitEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/OrganizationalUnitEntity"
}
}, "EmployeeMasterEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/EmployeeMasterEntity"
}
}, "EmployeeTypeEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/EmployeeTypeEntity"
}
}, "CountryEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/CountryEntity"
}
}, "PositionEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/PositionEntity"
}
}, "EmployeeMasterEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/EmployeeMasterEntity"
}
}, "EmployeeMasterEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/EmployeeMasterEntity"
}
}, "GeneralCostEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/GeneralCostEntity"
}
}, "OrganizationalUnitEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/OrganizationalUnitEntity"
}
}, "OrganizationalUnitEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/OrganizationalUnitEntity"
}
}, "ProjectCostEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/ProjectCostEntity"
}
}, "ProjectMasterEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/ProjectMasterEntity"
}
}, "ProjectMasterEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/ProjectMasterEntity"
}
}, "ProjectMasterEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/ProjectMasterEntity"
}
}, "ProjectMasterEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/ProjectMasterEntity"
}
}, "ProjectMasterEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/ProjectMasterEntity"
}
}, "ProjectMasterEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/ProjectMasterEntity"
}
}, "ProjectMasterEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/ProjectMasterEntity"
}
}, "ProjectMasterEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/ProjectMasterEntity"
}
}, "ProjectRevenueEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/ProjectRevenueEntity"
}
}, "ProjectStructureEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/ProjectStructureEntity"
}
}, "TimeEventEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/TimeEventEntity"
}
}, "WorkloadsAssignmentEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/WorkloadsAssignmentEntity"
}
}, "WorkloadsAssignmentEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/WorkloadsAssignmentEntity"
}
}, "WorkloadsAssignmentEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/WorkloadsAssignmentEntity"
}
}
}
}

With odata4 we get some problems. First of all we have to specify the name of the foreign key instead of the name of table as inodata2 (but this is not a real problem)
We correctly see the three records:

http://casteiid01.casalelug.ch:8080/odata4/CASALE.1/Entities/EmployeeMasterEntity(3)/EmployeeActionEntity_FK_Employee?$format=json

{"@odata.context":"$metadata#EmployeeActionEntity","value":[{"EmployeeActionKey":"10009- - - -20140430-20120101-0","EmployeeID":3,"ActionType":"Hiring","EmploymentStatus":"Active","ValidFrom":"2012-01-01","ValidUntil":"2014-04-30","SequenceNumber":0,"ModifiedBy":"DEMARIAM","ModificationDate":"2011-11-25"},{"EmployeeActionKey":"10009- - - -20140531-20140501-0","EmployeeID":3,"ActionType":"Organizational reassignment","EmploymentStatus":"Active","ValidFrom":"2014-05-01","ValidUntil":"2014-05-31","SequenceNumber":0,"ModifiedBy":"ZUCCHIATTIS","ModificationDate":"2014-04-29"},{"EmployeeActionKey":"10009- - - -99991231-20140601-0","EmployeeID":3,"ActionType":"Organizational reassignment","EmploymentStatus":"Active","ValidFrom":"2014-06-01","ValidUntil":"9999-12-31","SequenceNumber":0,"ModifiedBy":"DEMARIAM","ModificationDate":"2014-05-20"}]}

What we cannot resolve is how to get the data corresponding to the EmployeeMasterEntity having the EmployeeActionEntityKey = '10009-%20-%20-%20-20140430-20120101-0'

http://casteiid01.casalelug.ch:8080/odata4/CASALE.1/Entities/EmployeeActionEntity('10009-%20-%20-%20-20140430-20120101-0')/EmployeeMasterEntity?$format=json

We cannot create a foreign key in EmployeeMasterEntity because there we have not a field related to EmployeeActionEntity

thanks,
Mirco
Actions
5. Re: foreign keys in odata

rareddy Jun 24, 2016 10:35 AM (in response to mirco73)

Micro,

Please read about $expand option in the OData V4, with that option you can issue

http://casteiid01.casalelug.ch:8080/odata4/CASALE.1/Entities/EmployeeMasterEntity(3)?$expand=EmployeeActionEntity_FK_Employee&$format=json

Ramesh..
Actions
6. Re: foreign keys in odata

mirco73 Jun 24, 2016 11:14 AM (in response to rareddy)

Ramesh,
in that way I can see all the values of EmployeeActionEntity knowing the key of EmployeeMasterEntity
what I' trying to to is to see is all the values of EmployeeMasterEntity starting from the key of EmployeeActionEntity

eg: like in odata2, knowing the PK of EmployeeActionEntity, what is the data of the corresponding EmployeeMasterEntity ?

odata2
http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeActionEntity('10009-%20-%20-%20-20140430-20120101-0')/EmployeeMasterEntity?$format=json

odata4
http://casteiid01.casalelug.ch:8080/odata4/CASALE.1/Entities/EmployeeActionEntity('10009-%20-%20-%20-20140430-20120101-0')/XXXXXXXXXX?$format=json

Mirco
Actions
7. Re: foreign keys in odata

rareddy Jun 24, 2016 11:38 AM (in response to mirco73)

In OData V2 it was based on relationships, it was most of time forced to use both directions with entities. With V4 is about navigability, I choose to implement one direction. I have not tried this, but how about creating a FK on EmployeeMaterEntity to the EmployeeActionEntity. I know the relationship is implied, for implementation I would need a navigation name, which would be this FK's name.

Ramesh..
Actions
8. Re: foreign keys in odata

mirco73 Jun 24, 2016 11:57 AM (in response to rareddy)

Ramesh,
I cannot create a FK in that direction because I have no fields in EmployeeMasterEntity which identifies the key in EmployeeActionEntity
to make it simpler, just have a look on this public odata4 examples:

Product is a table like my EmployeeActionEntity

<EntityType Name="Product">
    <Key>
        <PropertyRef Name="ProductID"/>
    </Key>
    <Property Name="ProductID" Type="Edm.Int32" Nullable="false" p5:StoreGeneratedPattern="Identity"/>
    <Property Name="ProductName" Type="Edm.String" Nullable="false" MaxLength="40"/>
    <Property Name="SupplierID" Type="Edm.Int32"/>
    <Property Name="CategoryID" Type="Edm.Int32"/>
    <Property Name="QuantityPerUnit" Type="Edm.String" MaxLength="20"/>
    <Property Name="UnitPrice" Type="Edm.Decimal" Precision="19" Scale="4"/>
    <Property Name="UnitsInStock" Type="Edm.Int16"/>
    <Property Name="UnitsOnOrder" Type="Edm.Int16"/><Property Name="ReorderLevel" Type="Edm.Int16"/>
    <Property Name="Discontinued" Type="Edm.Boolean" Nullable="false"/>
    <NavigationProperty Name="Category" Type="NorthwindModel.Category" Partner="Products">
        <ReferentialConstraint Property="CategoryID" ReferencedProperty="CategoryID"/>
    </NavigationProperty>
    <NavigationProperty Name="Order_Details" Type="Collection(NorthwindModel.Order_Detail)" Partner="Product"/>
    <NavigationProperty Name="Supplier" Type="NorthwindModel.Supplier" Partner="Products">
        <ReferentialConstraint Property="SupplierID" ReferencedProperty="SupplierID"/>
    </NavigationProperty>
</EntityType>

Category is like my EmployeeMasterEntity

<EntityType Name="Category">
    <Key>
        <PropertyRef Name="CategoryID"/>
    </Key>
    <Property Name="CategoryID" Type="Edm.Int32" Nullable="false" p5:StoreGeneratedPattern="Identity"/>
    <Property Name="CategoryName" Type="Edm.String" Nullable="false" MaxLength="15"/>
    <Property Name="Description" Type="Edm.String" MaxLength="max"/>
    <Property Name="Picture" Type="Edm.Binary" MaxLength="max"/>
    <NavigationProperty Name="Products" Type="Collection(NorthwindModel.Product)" Partner="Category"/>
</EntityType>

It seems that the two tables are related through NavigationProperty and Partner

In fact I can see all the Categories of a certain Product

http://services.odata.org/V4/Northwind/Northwind.svc/Products(10)/Category

or viceversa the products of a certain Category

http://services.odata.org/V4/Northwind/Northwind.svc/Categories(8)/Products
Actions
9. Re: foreign keys in odata

rareddy Jun 24, 2016 2:42 PM (in response to mirco73)

You can log enhancement JIRA for this, I will see if I can get into 9.1
Actions

Go to original post