Only foreign key constraints are allowed on this table AX 2012

foreign-key-relationship axapta dynamics-ax-2012 dynamics-ax-2012-r3
Raas picture Raas · Mar 2, 2015 · Viewed 8k times · Source

I have created three fields in a table: Country, State and City. I have given EDT as LogisticsAddressCountryRegionId for Country, LogisticsAddressStateId for State. The relations are MyTable.Country = LogisticsAddressCountry.CountryRegionId.

I am getting a best practise error as Only foreign key constraints are allowed on this table.

I tried few options like Setting New roles in relations. Doesnt work.

Answer

boucekv picture boucekv · Mar 3, 2015

The error message is probably misleading. It will be linked to the extended data type with table relation.

Try following

1) Check if the EDT you are using is in the 2012 style or 2009 style (2009 has relations). If it is in the old style try to use new style datatype with table reference instead of relation.

2) Add relation to the table level.

3) "Table1 -> Field1,Field12

Index1 -> Field1 having AlternateKey set to "Yes".

Table1 -> properties->PrimaryIndex set to "Index1".

Table2 -> create a foreign key relation (Foreign key -> Primary key based)

then automatically a reation Table2.Table1 ==Table1.Field1 is created.

In this way you can easily create a relation on any field other than RecId. and also the BP error Only foreign key constraints are allowed on this table will remove."

http://dynamicsuser.net/forums/p/54753/288954.aspx

4) Try to export table into .XPO. Then change EnforceFKRelation property in file and import .XPO back to axapta.

https://erpcoder.wordpress.com/2014/08/04/get-rid-of-bp839-only-foreign-key-constraints-are-allowed-on-this-table/

5) You can find more about this error here http://microsoft-dynamics-ax-erp.blogspot.cz/2012/12/debug-bp-errors-in-dynamics-ax-2012.html

"For those of you who were wondering the reason for this BP error, it is because we should create a new Foreign key based relation instead of a normal relation.

Simply put, when you drag and drop the ItemID EDT on the table, it will ask for your confirmation to add the relation on the EDT.

Press Yes and a Foreign key based relation will be created. If you press No and want to create the relation manually, make sure you create a Foreign key based relation and not a normal relation.

Both the normal and foreign key relation looks the same and it is visually difficult to differnetiate between them. So, if you ever encounter the above mentioned BP and have a relation defined, delete and recreate a new foreign key relation."

Related questions

When to use "ON UPDATE CASCADE"

I use "ON DELETE CASCADE" regularly but I never use "ON UPDATE CASCADE" as I am not so sure in what situation it will be useful. For the sake of discussion let see some code. CREATE TABLE parent ( id INT …

Read More
SQL Add foreign key to existing column

If I am using the following SQL command in SQL Server 2008 to update a table with a foreign key constraint: ALTER TABLE Employees ADD FOREIGN KEY (UserID) REFERENCES ActiveDirectories(id) UserID being my FK column in the Employees table. I'm …

Read More
What is `related_name` used for in Django?

What is the related_name argument useful for on ManyToManyField and ForeignKey fields? For example, given the following code, what is the effect of related_name='maps'? class Map(db.Model): members = models.ManyToManyField(User, related_name='maps', verbose_name=_(…

Read More