MySQL foreign key referencing a big_increments field

[JRubics]

Hi all! I am trying to create a foreign key referencing a big_increments field, but I get (1215, 'Cannot add foreign key constraint') error. I think it is because I can't create a field that is the same type as big_increments (unsigned non nullable auto incrementing big integer with length of 20) because when I tried creating big integer with unsigned() modifier, its length got shortened to 10.

Example of my code:

class CreateUsersTable(Migration):
    def up(self):
        """
        Run the migrations.
        """
        with self.schema.create('users') as table:
            table.big_increments('id')
            ...

class CreateAppsTable(Migration):

    def up(self):
        """
        Run the migrations.
        """
        with self.schema.create('apps') as table:
            table.big_increments('id')
            table.big_increments('user_context_id', length=20).unsigned().nullable()
            ...

            table.index('user_context_id', name=apps_user_context_id_index')
            table.foreign('user_context_id', name='apps_user_context_id_foreign') \
                .references('id').on('users') \
                .on_delete('cascade')

Can you please check if it is possible to create foreign key referencing a big_increments field?

Desktop (please complete the following information):

• OS: Linux
• Version Ubuntu 21.10

What database are you using?

• Type: [MySQL]
• Version [14.14 Distrib 5.7.30, for Linux (x86_64) using EditLine wrapper]
• Masonite ORM [2.4.2]

[josephmancuso]

You can't have 2 increments on the same table I don't think. Increments is mainly for primary keys

This line:

table.big_increments('user_context_id', length=20).unsigned().nullable()

Should be like:

table.big_integer('user_context_id', length=20).unsigned().nullable()

[JRubics]

Thank you :) I tried your solution but it also doesn't work with the same error. Do you have any other idea what I can try?

[lucadelmonte]

Hello, i have the same error reported on the issue, the problem seems to be that using
table.big_integer('user_context_id', length=20).unsigned().nullable()
when calling unsigned(), the type of the fields gets changed to INT, from BIGINT, i think it is a bug here
https://github.com/MasoniteFramework/orm/blob/2.0/src/masoniteorm/schema/Blueprint.py#L596, after calling usnigned the type of the column is changed to unsigned_integer , which here https://github.com/MasoniteFramework/orm/blob/2.0/src/masoniteorm/schema/platforms/MySQLPlatform.py#L40 is defined as "INT UNSIGNED" in mysql.

[josephmancuso]

hm ok I'll fix this.

[JRubics]

Thank you! :)

[josephmancuso]

This is fixed in latest 2.5.0 version of Masonite ORM

[JRubics]

Thank you! 🎉