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

    Questions:

    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?

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

    Background:

    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