1215 Cannot add foreign key constraint

[Steve]

Goodevening,

I am trying to migrate from Sqite to MySQL.

Unfortunatly when restoring the field in the DB tables i get the following error: 

<class 'gluon.contrib.pymysql.err.IntegrityError'> (1215, u'Cannot add foreign key constraint')

location of issue: Field('Insurance',db.insurance,label=T('Insurance')),

When i look this up in google .... this could be caused by  a Field which has the reuirement= Not NULL but has no Default value set.

I tested this but not able to get passed the error.

first table with field in db1.py

db.define_table('insurance',

                Field('Company',requires=IS_NOT_EMPTY(error_message='cannot be empty!'), label=T('Company')),

                Field('Insurance_number','string', requires=IS_NOT_EMPTY(error_message='cannot be empty!'),  label=T('Insurance Number')),

                Field('Street_name', requires=IS_NOT_EMPTY(error_message='cannot be empty!'), label=T('Street Name')),

                Field('House_number','string', requires=IS_NOT_EMPTY(error_message='cannot be empty!'), label=T('House Number')),

                Field('Postal_code','string', requires=IS_NOT_EMPTY(error_message='cannot be empty!'),label=T('Postal Code')),

                Field('City','string', requires=IS_NOT_EMPTY(error_message='cannot be empty!'), label=T('City')),

                Field('Region','string', requires=IS_NOT_EMPTY(error_message='cannot be empty!'), label=T('Region')),

                Field('Country','string', requires=IS_NOT_EMPTY(error_message='cannot be empty!'), label=T('Country')),

                Field('Telephone_number','string', requires=IS_NOT_EMPTY(error_message='cannot be empty!'), label=T('Telephone Number')),

                Field('Mobile','string', requires=IS_NOT_EMPTY(error_message='cannot be empty!'), label=T('Mobile')),

                Field('Email','string', requires=IS_EMAIL(error_message='invalid email! Please fill in a correct e-mail address.'), label=T('Email')),

                auth.signature, format='%(Company)s',fake_migrate=True)

Second table which in a db2.py:

db.define_table('client',

                Field('Debtor_nr',requires=IS_NOT_EMPTY(error_message='cannot be empty!'), label=T('Debtor number')),

                Field('Insurance',db.insurance,label=T('Insurance')), --> this is where the first issue takes place

Where should i add a default value ? to et this issue fixed

Hope you can help me with this

Steve

[Dave S]

This page has different diagnoses than you mention:

<URL:https://www.rathishkumar.in/2016/01/solved-how-to-solve-mysql-error-code.html>

There, the issue with NULL is that the foreign key can't be NULL if it is part of the child table's primary key.

Another way to get the error is to use a different mysql storage engine than innodb.

Yet another way is to not have a unique key in the table, but since you're using a model file to describe the parent table, the DAL should have handled that, and also made sure that the parent and child agree on what the key is.

As a first step, use your mysql console or dbclient to display the schema used for the insurance table.  (I can't tell you how to do that, because I use sqlite and postgres, not mysql, so I know two incantations but they probably don't match what you need).

Good luck!

/dps

 

[Steve]

Hi Dave,

Thanks for your reply.

When starting web2py again the DAL tries to create the tables but none of the tables are being created due to this error.

Using a clean new version of a web2py app the tables are created and are  of type innodb  storage engine( as mentioned in the page you sent).

I am still no step further with solving this issue.

Steve

Op woensdag 11 september 2019 10:35:22 UTC+2 schreef Dave S:

[Steve]

Hi Dave,

After performing a Fake_migration_all=True the Db files where created in de app/databases folder.

So, in de admin i can see all of the tables.....

But, unfortunatly the tables where NOT created in the MySQL database.

I can't find ou why not or how to fix this.

Already went to the document about broken migrations but no luck yet.

The exact error: ProgrammingError: (1146, u"Table 'abc' doesn't exist")

Any idea's how to fix this

regards,

Steve

Op woensdag 11 september 2019 10:35:22 UTC+2 schreef Dave S:

[Dave S]

On Wednesday, September 11, 2019 at 1:55:04 PM UTC-7, Steve wrote:

Hi Dave,

After performing a Fake_migration_all=True the Db files where created in de app/databases folder.

So, in de admin i can see all of the tables.....

But, unfortunatly the tables where NOT created in the MySQL database.

I can't find ou why not or how to fix this.

fake_migration ONLY creates *.table files, and is primarily for already existing tables (e.g., legacy) DBs.  To create an actual DB table requires migration rather than fake_migration.  And as Niphlod has often pointed out, migrate=True only needs to be set for one access, and then you turn it back off.

/dps

 

[Steve]

Hi Dave,

Ok, that fake_migration only creates *.tables file i did not know....

Migrate True only one time .. that i knew but for some reason only 1 table is created from the 25+ so something is wrong.

Don't know what yet

Op donderdag 12 september 2019 10:49:15 UTC+2 schreef Dave S:

[Steve]

Hi Dave,

Finally i got to the root of the problem :-)

Turns out to be a.... builders error ... yes, my own error.

In the very first table there is a reference to a logged in user which does not exist when trying to create the tables ( same for loading the data).

Quick work around is swapping two tables and after that prefill the first table with a couple of records.

After that i could upload my backup data withoout a problem.

Thanks for the help.

Rgards,

Steve

Op donderdag 12 september 2019 10:49:15 UTC+2 schreef Dave S:

[Dave S]

On Friday, September 13, 2019 at 9:41:57 AM UTC-7, Steve wrote:

Hi Dave,

Finally i got to the root of the problem :-)

Turns out to be a.... builders error ... yes, my own error.

In the very first table there is a reference to a logged in user which does not exist when trying to create the tables ( same for loading the data).

Quick work around is swapping two tables and after that prefill the first table with a couple of records.

After that i could upload my backup data withoout a problem.

Thanks for the help.

Rgards,

Steve

It is always good to hear about success!

/dps