Questions[foreign-key](server)

Main question is in the subject.

In more detail: Let's say we have a table T with a FK constraint to table U (for simplicity, to its PK, and both table use clustered indices on their PKs). Now, we INSERT or UPDATE a row in T, thereby writing the FK value. My understanding is that

• a transaction-long X lock is issued for the inserted/changed row on T (and IX locks above);

• a transaction-long S lock is issued on U for the FK value - so that no other transaction can acquire an X lock on that key and change it.

Is this correct? If not, how does FK constraint enforcement work, and which locks does it set?

It would explain deadlocks on a parallel ETL system of ours.

(I did not find any explanation about this, also not in publicly accessible places like https://www.microsoftpressstore.com/articles/article.aspx?p=2233327&seqNum=3; also, common understanding seems to be "shared locks are released as soon as SQL Server has read and processed the locked data.", which sort of contradicts my transaction-long S locks ...).

EDIT: Here is an extract from the deadlock graph:

Delete from ProduktGtin ....
...
INSERT INTO dbo.Produktzusatz ....
...
   <pagelock fileid="1" pageid="33949422" ...
     <owner id="processc45d4e8c8" mode="IX"/>
     <waiter id="process780a4c8c8" mode="U" requestType="wait"/>
   <pagelock fileid="1" pageid="6935000" ...
     <owner id="process780a4c8c8" mode="IX"/>
     <waiter id="processc45d4e8c8" mode="S" requestType="wait"/>

I'm inheriting a database that has 400 tables and only 150 foreign key constraints registered. Knowing what I do about the application and looking at the table columns, it's easy to say that there ought to be a lot more.

I'm afraid that the current application software will break if I started adding the missing FKs because the developers have probably come to rely on this "freedom", but step one in fixing the problem is to come up with the list of missing FKs so we can evaluate them as a team.

To make matters worse, the referencing columns don't share a naming convention.

The relationships ARE coded informally into the hundreds of ad-hoc queries and stored procedures, so my hope is to parse these files programmatically looking for JOINS between actual tables (but not table variables, etc).

Challenges I foresee in this approach are: newlines, optional aliases and table hints, alias resolution.

• Any better ideas? (Besides quitting)
• Are there any pre-built tools that can solve this?
• I don't think regex can handle this. Do you disagree?
• SQL Parsers? I tried using Microsoft.SqlServer.Management.SqlParser.Parser but all that is exposed is the lexer - can't get an AST out of it - all that stuff is internal.

I'm about to drop an index from a mySQL INNODB table. There are references defined already. When I try to alter it, it says:

#1025 - Error on rename of 'X/#sql-2a5f_219828' to 'X' (errno: 150)

Which at first seems logical, since there are relations, it can't be renamed. But even if I disable foreign key checking:

SET foreign_key_checks = 0;

ALTER TABLE flyers DROP INDEX Index_5, ROW_FORMAT = DYNAMIC;

I get the same! I don't want to drop ALL foreign key definitions, drop that index, and define them again! Another solution?

I have a table called abs with primary key absId, this is used as a foreign key in couple of tables which I don't know. Is there a way that I can find out how find the names of those tables in which the primary key absId is referenced? I'm using Oracle 10g as the database. Thanks.

For sending data back and forth between tables having identical schema I have used the import/export task in management studio.

The problem is that SQLServer seems to do the import/export ops in whatever order it sees fit. This causes issues with FK constrained tables that need to be imported in the specific cascading order.

I know that I can write SSIS packages to do these ops in whatever order I like, but sometimes the import/export task is just easier for ad-hoc data changes.

Is there a way I can tell SQL Server to perform the task in the proper order for the constraints?

EDIT: I know that I can ALTER TABLE to disable and enable FK's... I guess I'm just wondering if there's a setting to make the import/export task do this automatically for constrained tables.