I store my sales bills in two tables (simplified): BillHeader (BillId, Date, Salesman) and BillDetail (BillId, LineId, ItemId, Quantity...) I would like to make BillId as a primary key of table BillHeader; (BillId, LineId) as primary key of table BillDetail, and I can make it successfully (I also can see the real primary keys in the SQL database). The problem is that I cannot make BillId of table BillDetail as a foreign key just to make sure I cannot input wrong BillId data (no relevant BillId in BillHeader table): I checked the database, but cannot see the relevant foreign key there.
Here are what I have done: 1) Table BillHeader: - field BillId: [Mandatory]=Yes. - index idx_BillId use field BillId: [AllowDuplicates]=No, [AlternateKey]=Yes. - table BillHeader: [PrimaryIndex]=idx_BillId, [ClusterIndex]=idx_BillId.
The SQL database shows columns [PARTITION], [DATAAREAID], [BILLID] formulating the primary of the table.
2) Table BillDetail: - Add new relation fk_BillId: [Table]=BillHeader (parent table), [Validate]=Yes, [RelatedTableCardinality]=ExactlyOne (each record in table BillDetail has only one relevant record in BillHeader), [Cardinality]=ZeroMore (for each record with the same billId in BillHeader there is/are 0 or more relevant records in BillDetail), [RelationshipType]=Association(foreign key) - After adding a new relation, there is a new field added to table BillDetail, rename it to BillId, and set [Mandatory]=Yes.
After these steps, I tried to insert a couple of new records into table BillDetail using X++ code (simple table's insert() method), and I can do it successfully even I don't have anything in parent table. What do I miss?
Thank you.
You need to override validate write method to do the validation if you want validate insertion using x++. https://msdn.microsoft.com/en-us/library/hh803130.aspx
Making table relation validate property to Yes means that each insert of a record by a form into the child table is rejected unless the related record exists in the referenced parent table.