Skip to Content
avatar image
Former Member

Not Able to drop a table..Please help me out

While trying to delete from a table got below error:

The 'delete' statement will not be executed because the cross-database referential constraint on table 'ims24_fits..e_fit_trade' cannot be enforced. A table or constraint definition is missing in database 'global'. Contact your System Administrator.

Msg: 436, Level: 16, State: 1

Below is the details of sp_helpconstraint:

name definition created

--------------------------- -------------------------------------------------------------------------------- -------------------

e_fit_trad_buy_se_459864705 DEFAULT 'N' Jun 2 2001 2:33AM * global.. FOREIGN KEY (*) REFERENCES e_fit_trade(seqno) NULL

e_fit_trad_seqno_1890925908 e_fit_trade_discrepancy_reason FOREIGN KEY (seqno) REFERENCES e_fit_trade(seqno) Mar 17 2015 11:23AM

XPK_e_fit_trade PRIMARY KEY INDEX ( seqno) : CLUSTERED, FOREIGN REFERENCE Mar 16 2015 3:47PM

The Second constraint is NULL.

It's not even letting me to delete the table.

Please advise....

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Apr 15, 2016 at 05:44 PM


    Are you trying to drop a table, drop a RI constraint, or get the delete to work?

    Did you by any chance dump-n-load your database from a different server, or perhaps recently dropped the 'global' database from your dataserver?



    RI (foreign key => primary key) constraints are stored in the sysreferences table.

    When you have cross database RI constraints the sysreferences table contains the dbname, dbid and table_id of the referencing and/or referenced tables.

    When dumping-n-loading a database between dataservers, the DBA has to take care to address potential issues when any of the contents of sysreferences become corrupt (eg, dbname, dbid or table_id don't exist on target dataserver). [Unfortunately, addressing these types of issues is a bit convoluted as it depends on the type of issue(s) you run into.]


    Looking through the source code for sp_helpconstraint we can see ...

    - the contraint name gets displayed as '*' when object_name(sysreferences.constrid,<other_db_id>) returns NULL; this can occur in the following scenarios:

    - constraint does not exist in the <other_db_id> database

    - constraint exists in the <other_db_id> database but has a different object id (constrid)

    - <other_db_id> database does not exist on this dataserver

    - the referencing object name, when said object resides in the <other_db_name> database, is displayed as <other_db_name>..<object_name>; in your case your display is showing 'global..<NULL>'; this can occur in the following scenarios:

    - sysreferences.frgndbname='global' but there is not 'global' database in this dataserver

    - the 'global' database does exist but sysreferences.constrid value does not exist in the global..sysobjects table (either contraint does not exist, or constraint exists but with different id)


    Fixing this issue is going to depend on which of the following applies ...

    - the 'global' database does not exist in your dataserver

    - the 'global' database does exist in your dataserver but there's no entry in the global..sysobjects table for id = <contrid>/<tableid>/<reftabid>

    ... and how you wish to fix the issue (eg, drop this entry from the local sysreferences table; load the global database; patch the local (and possibly global..) sysreferences tables).

    You could start by trying to find the problematic records from the local sysreferences table. See the attached SQL script. [NOTE: I whipped this up without a problematic db to test against so we may need to come back and tweak the queries if it finds no issues in your db.]

    Message was edited by: Mark A Parsons; updated attached SQL script

    Add comment
    10|10000 characters needed characters exceeded