0

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.

Tuan Le PN
  • 364
  • 1
  • 12

1 Answers1

0

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.

  • Should I just go to SQL server and add a constraint for the foreign key there? I will surely help to prevent any insertion to child table without any relevant record in parent table? – Tuan Le PN Aug 29 '17 at 04:13
  • @TuanLePN If you have a follow-up question, either create a new question or [edit] your question to add additional information. – FH-Inway Aug 29 '17 at 16:36
  • The answer does not directly answer my question. But there is a paragraph in the link that says "A value of Yes for the Validate property does not prevent direct X++ SQL data operations from deleting parent records or inserting child records that violate the integrity of foreign key data." So, it implies that there is no way to force AX to create foreign keys in the relevant back-end table (?), and I accept Pradeep's answer. I also tried to add the foreign key manually in the SQL table, and it seems that my code works fine. Thanks. – Tuan Le PN Aug 29 '17 at 16:56
  • @TuanLePN This is why I asked you to create a new question, because adding the foreign key manually in the SQL table is wrong. You should never modify the structure or data of the Dynamics AX databases directly. – FH-Inway Aug 30 '17 at 10:23