DAL Could not create constraint or index

Andrea Fae'

unread,

Aug 17, 2020, 6:51:56 AM8/17/20

to web2py-users

When I try to migrate sqlite to mssql and execute the command

python web2py.py -S ITAsset -M -P

(ITAsset is the name of application)

I see this error

ProgrammingError: ('42000', u"[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing FOREIGN KEY constraint 'auth_user_created_by__constraint' on table 'auth_user' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. (1785) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint or index. See previous errors. (1750)")

auth_user is the standard web2py table. So What can I do to permit to this command to create all the tables?

Thank you

Andrea Fae'

unread,

Aug 17, 2020, 7:03:40 AM8/17/20

to web2py-users

This is my sql.log

timestamp: 2020-08-17T13:02:34.812000

CREATE TABLE auth_user(

id INT IDENTITY PRIMARY KEY,

first_name VARCHAR(128) NULL,

last_name VARCHAR(128) NULL,

email VARCHAR(512) NULL,

username VARCHAR(128) NULL,

password VARCHAR(512) NULL,

registration_key VARCHAR(512) NULL,

reset_password_key VARCHAR(512) NULL,

registration_id VARCHAR(512) NULL,

is_active BIT NULL,

created_on DATETIME NULL,

created_by INT NULL , CONSTRAINT auth_user_created_by__constraint FOREIGN KEY (created_by) REFERENCES auth_user (id) ON DELETE CASCADE,

modified_on DATETIME NULL,

modified_by INT NULL , CONSTRAINT auth_user_modified_by__constraint FOREIGN KEY (modified_by) REFERENCES auth_user (id) ON DELETE CASCADE

);

villas

unread,

Aug 17, 2020, 8:24:25 AM8/17/20

to web2py-users

Hi Andrea

I was pleased to see you are making progress with the DB connection etc.

I can see why mssql does not like the cascade from the same auth_user table. This may be an issue which needs fixing...

To get things moving, and this might not be ideal, but I propose you consider either of these options:

Create manually your own tables without those contraints. You then set this: auth.define_tables(... migrate=False ) so that pyDal skips creation.
Do not include the signature fields with your auth. auth.define_tables(... signature=False )

Incidentally, I use option 2 and I therefore do not benefit from the created and modified info, but I could easily remedy this by adding the fields if I wished.

Hope this helps.

Andrea Fae'

unread,

Aug 18, 2020, 3:51:57 PM8/18/20

to web2py-users

Thank you Villas. I'm using option 2 and now I have the tables without "created_by" and "modified_by".

Now I will try to reset signature=true and see what will happen. If it causes the same error I could add but they will not managed automatically by web2py, or I'm wrong?

What do you suggest?

Thank you for your precious information.

villas

unread,

Aug 18, 2020, 4:38:50 PM8/18/20

to web2py-users

I presume therefore that you need those fields. This is the kind of thing I do to get things working. Please forgive me if I've missed something.

I would create those required fields manually in your database (without the constraint that causes the problem).
Set auth.define_tables(migrate = True, fake_migrate=True)
Run the app. This will create the .table definition file in the databases dir. You may have had to delete the old .table file. If necessary delete that.
Set auth.define_tables(migrate =False) again.

Sorry if this seems strange, but it is a work around solution.

I am hoping that someone will eventually fix this contraint problem in pyDal.

Andrea Fae'

unread,

Aug 19, 2020, 4:43:42 AM8/19/20

to web2py-users

Hello Villas, unfortunately other problems to this game...

I used archiving, and so this type of table definitions

db.define_table('asset_archive',

Field('current_record', db.asset),

db.asset,migrate='asset_archive.table')

but I have the same problem regarding FOREIGN KEYS...no way to recreate the tables.

Do I have to give up the record archiving? Waht do you think?

If you can, please can you explain the meaning of fake_migrate? I never used and in the book is not very well explained.

Thank you

villas

unread,

Aug 19, 2020, 6:32:20 AM8/19/20

to web2py-users

An easier solution for you could be this!

Simply specify your created_by/modified_by as extra fields (which should side-step self-referencing probem).
I didn't test it, but it seems like a good idea to me. You can then use normal migrations etc.

auth.settings.extra_fields['auth_user']= [

                     Field('is_active', 'boolean', default=True),
                     Field('created_on', 'datetime', default=request.now),
                     Field('created_by', 'integer', default=auth.user_id),
                     Field('modified_on', 'datetime', update=request.now),
                     Field('modified_by', 'integer', update=auth.user_id)

]

auth.define_tables(... signature=False )

Otherwise, in answer to your questions...

migrate='asset_archive.table'

Personally, I do not specify the table definition name as the one given automatically by web2py is very good. I just use migrate=True/False.

If you use migrate='asset_archive.table', you are also specifying migrate=True

fake_migrate=True

This is used to recreate the .table file without touching your DB.

If you manually create and edit the fields in your DB, you may wish to recreate the .table definition file (hence fake_migrate).

I am not sure how you are using the archiving etc, but I do know this:

you can manually create your own DB on-disk structure with whatever triggers, indexes etc you require.
in web2py you can use define_table to create a web2py meta definition (which must be compatible with what is on disk!)
you can use fake_migrate if you wish to create a .table definition file (which will reflect your web2py define_table)
you can use migrate=False to prevent web2py from attempting to migrate the physical DB on-disk structure

Using the above, the book explanation should make a little more sense.

Andrea Fae'

unread,

Aug 20, 2020, 10:45:35 AM8/20/20

to web2py-users

Hello Villas, first al all...thank you thank you thank you! But I didn't arrived to the end of the game yet. but I think I'm near with your suggestions.

I followed the "easier solution" and, with a lot of effort, now I have my MSSQL database filled of my data.

But when I start tha application web2py I have this error:

Error ticket for "ITAsset"

Ticket ID

127.0.0.1.2020-08-20.16-37-26.91c5b362-833d-4bdc-afef-3b727fde37a8

<class 'pyodbc.ProgrammingError'> ('42S01', u"[42S01] [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'sede' in the database. (2714) (SQLExecDirectW)")

Versione

web2py™	Version 2.14.6-stable+timestamp.2016.05.10.00.21.47
Python	Python 2.7.15: C:\Python27\python.exe (prefix: C:\Python27)

Traceback

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

Traceback (most recent call last):
  File "C:\web2py\gluon\restricted.py", line 227, in restricted
    exec ccode in environment
  File "c:/web2py/applications/ITAsset/models/db_asset.py", line 12, in <module>
    format='%(nome)s')
  File "C:\web2py\gluon\packages\dal\pydal\base.py", line 834, in define_table
    table = self.lazy_define_table(tablename,*fields,**args)
  File "C:\web2py\gluon\packages\dal\pydal\base.py", line 873, in lazy_define_table
    polymodel=polymodel)
  File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 482, in create_table
    self.create_sequence_and_triggers(query, table)
  File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 1369, in create_sequence_and_triggers
    self.execute(query)
  File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 1388, in execute
    return self.log_execute(*a, **b)
  File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 1382, in log_execute
    ret = self.get_cursor().execute(command, *a[1:], **b)
ProgrammingError: ('42S01', u"[42S01] [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'sede' in the database. (2714) (SQLExecDirectW)")

I will attach you db.py and dbasset.py, my models and the sql.log generated...

I tested with all combination of "migrate" and "fake_migrate" but web2py try every time to create the table "sede and so the error.

Can you have another suggestion? when I will finish I inveted you yo a dinner in the restaurant! :-)

thank you

db.py

db_asset.py

sql.log

Andrea Fae'

unread,

Aug 20, 2020, 10:57:26 AM8/20/20

to web2py-users

UPDATES....

in appconfig.ini I changed from migrate= true to migrate=false.

tha system doesn't create me the sql.log and .table file definitions in the folder databases, but the application seems start to work! How is posisble?

Andrea Fae'

unread,

Aug 21, 2020, 3:43:14 AM8/21/20

to web2py-users

Hello, some updates.

In the databases I can see only this (see attachment).

I'm trying to manage record versionin (table archiving). I create for example asset.archive in mssql but web2py doesn't "see" the table. In fact the table is not listend in the appadmin page.

The question is that tables definitions are not created in the databases folder, even if I put migrate=false in appconfig.ini and in db.py I typed migrate_fake=true... ???

databases.docx

Andrea Fae'

unread,

Aug 21, 2020, 4:04:15 AM8/21/20

to web2py-users

I think the problem with archiving is when I defined archive table in mssql I defined a field named 'current_record" type int. Maybe type int is not correct, isn't it?

Thank you

Andrea Fae'

unread,

Aug 21, 2020, 4:06:07 AM8/21/20

to web2py-users

...but this error is not clear with me

Error ticket for "ITAsset"

Ticket ID

127.0.0.1.2020-08-21.09-54-23.77579ea3-20b8-49ca-aed5-76c10960c92f

<class 'pyodbc.IntegrityError'> ('23000', u"[23000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'id', table 'itassetdb.dbo.asset_archive'; column does not allow nulls. INSERT fails. (515) (SQLExecDirectW); [23000] [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (3621)")

insert a NULL value into culumn'id' on table asset_archive? Why?

Reply all

Reply to author

Forward