Circular dependency

[nfaugout-lucca]

Hi there,

I get this error when resetting the checkpoint : "There is a circular dependency between the DB tables and we can't safely build the list of tables to delete."

I have 2 tables that refer to each other but with different columns :

table users
table companies

A user has a FK with a company, and a company has a creator column, which is a FK with a user.

Both FK are nullable, so we can create a user without company, then create a company with the user as creator, then update the user with the company key.

Is it something Respawn does not support ?

I have digged into the SQL request generated by Respawn to get all the FK from the database. And I don't see any filter for nullable FK

Thanks for any help.

[Poltuu]

+1

[jbogard]

You can filter tables and schemas, but that's about it. You can't filter FKs, though I'm not sure how this would work anyway? Wouldn't it still fail trying to delete those tables because there are some rows WITH foreign keys?

[nfaugout-lucca]

Thank you for your answer.

I am new to Respawn and I might not understand well how it works. But I thought it would watch SQL requests going through and then replay a sort of "rollback" by deleting rows that where added.

I don't understand why it would delete "tables".

Our unit tests only add some rows, and we just need to delete thoses rows, in a certain order, respectfuly of the FKs (delete the children rows in the children table, then the parent row).

Thanks again

[jbogard]

It deletes all tables, in the right order. That replay sounds interesting,
though.

On Thursday, September 24, 2015, nfaugout notifications@github.com wrote:

Thank you for your answer.

I am new to Respawn and I might not understand well how it works. But I
thought it would watch SQL requests going through and then replay a sort of
"rollback" by deleting rows that where added.

I don't understand why it would delete "tables".

Our unit tests only add some rows, and we just need to delete thoses rows,
in a certain order, respectfuly of the FKs (delete the children rows in the
children table, then the parent row).

Thanks again

—
Reply to this email directly or view it on GitHub
#12 (comment).

[nfaugout-lucca]

OK thanks for clarifying. I understand now why Respawn is not compatible with our tables refering to each others.

[jjm340]

So I'm getting this same error; however, it only happens in our AWS environment, not when I run locally - any chance existing data would cause this error where as a clean DB would not?

[jbogard]

Dunno, are the constraints different?

[jjm340]

I ran
select so_pk.name, so_fk.name from sysforeignkeys sfk inner join sys.objects so_pk on sfk.rkeyid = so_pk.object_id inner join sys.schemas pk_schema on so_pk.schema_id = pk_schema.schema_id inner join sys.objects so_fk on sfk.fkeyid = so_fk.object_id inner join sys.schemas fk_schema on so_fk.schema_id = fk_schema.schema_id
On both DBs to compare and they were identical. Is there a better way to tell?

[jjm340]

You can close this issue, I opened one that highlights the actual problem that I was encountering