.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Retrusting a Foreign Key

Posted By:      Posted Date: October 07, 2010    Points: 0   Category :Sql Server

Hi there,


I have a foreign key that I want to update to being trusted again. I run the script:


alter table [tableName] with check check constraint [constraintName]


this completes fine with no messages/errors/etc


However when I go and run :


select * from sys.foreign_keys

where is_not_trusted = 1


The constraint still comes back as untrusted. I have checked in the table and there are no records

View Complete Post

More Related Resource Links

Foreign key constraint not replicating

We have a merge replication setup with one publisher/distributor and several subscribers.  Several of the tables being replicated have foreign key constraints and these replicate without issue.  However, some foreign key constraints that utilize cascading do not replicate.  To make matters worse, some tables with these cascading fk constraints replicate fine while others do not.  Has anyone run across this behavior and is there a fix for it?  The cascades will dramatically reduce our merge conflicts and help maintain the integrity of the data so we need to keep them in place.

How to get all the foreign key references for a primary key

Hi ,  I would like to know all the foriegn key references in the db for a table's primary key..How can i get that? Thanks sunil m datla

trouble with foreign key

I have this statement that breaks due to a foreign key constraint. having a tough time figuring out how to get around it: delete   from Security_LoginActivity where   UserClientMetaDataId in ( select   a.UserClientMetaDataId from Security_LoginActivity a inner   join Security_UserClientMetaData b on a.UserClientMetaDataId = b.UserClientMetaDataId where   LoginTraceId is not null ) delete   from Security_UserClientMetaData where   LoginTraceId is not null and LoginTraceId < (   select min(LoginTraceId) + 50000 from Security_UserClientMetaData   where LoginTraceId is not null )   Error is: Msg 547, Level 16, State 0, Line 9 The DELETE statement conflicted with the REFERENCE constraint "FK_Security_UserClientSignature_Security_UserClientMetaData". The conflict occurred in database "ASPNETDB", table "dbo.Security_UserClientSignature", column 'UserClientMetaDataId'. The statement has been terminated.  qeqw

Inserting Foreign Key Value if it doesn't exist?

I am inserting a record and want to set the value for a foreign key but I am getting an error - I initally thought that it would insert it if it didn't exist but I must be wrong and want to confirm that I need to check all values and make a record for them if they don't exist... here's what I am trying to do - does his make sense?var newAllPart = new Part();                newAllPart.PartNumber = partNumber;                newAllPart.Manufacturer.ManufacturerName = partManufacturer; // error here if the Manufacturer doesn't exists already                newAllPart.PartType.PartTypeName = partType;                newAllPart.Category.CategoryName = partCategory;                newAllPart.DateCreated = DateTime.Now;                newAllPart.UserCreated = userCreated;                newAllPart.Package.PackageName = partPackage;    

iterate till to get n-th level of foreign key inforamtion.

Hi.. how can i fetch n-th level of all foreign key's information? for example: one table is 'A', second table name is 'B', third tables is 'C' and so on table 'A' has a primary key, table 'B' has foreign key against table 'A' and table 'C' has foreign key of table 'B' and so on.. i want to make a such dynamic script which can get all/n-th level of foreign key relation inofrmations against. i made below script which can get foreign key information dynamically but it is used for single level not for n-th level. ALTER PROC [dbo].[sp_sng_fetching_foreign_key_info] @p_table_name varchar(100) , @flag bit AS DECLARE @pk_table_nme VARCHAR(150) DECLARE @fk_table_nme VARCHAR(150) DECLARE @pk_col_nme VARCHAR(150) DECLARE @fk_col_nme VARCHAR(150) DECLARE @constraint_nme VARCHAR(150) DECLARE @flg bit BEGIN set @flg = @flag DECLARE GETTING_ALL_FOREIGN_KEY_DATA CURSOR FOR select ISNULL(c.constraint_name,''), ISNULL(cfk.table_name ,''), ISNULL(kcu.column_name ,''), ISNULL(cpk.table_name ,''), ISNULL(pkt.column_name ,'') from information_schema.REFERENTIAL_CONSTRAINTS c INNER JOIN information_schema.TABLE_CONSTRAINTS cpk on c.unique_constraint_name = cpk.constraint_name INNER JOIN information_schema.TABLE_CONSTRAINTS cfk on c.constraint_name = cfk.constraint_name INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON c.constrai

for foreign key which data type is covinent ?

for foreign key which data type is covinent ?example:- a fruits shop application.i have 4 text boxes in that can enter 1.mango,2.apple,3.banan,4.pineapple.i want to buy and sale, inventry based on date save in database and retrive.can explain any one for that i think foreign key is required.so i am asking is it possible date time for foreign key ?

How to change the value of a foreign key field/navigation property?

How can I change the value of a foreign key field?  E.g. in the Product list view instead of the value of the field Supplier.CompanyName I want to display the Supplier.Region. I searched my whole code to get a clue, googled for "change navigation property" and looked in the docs but unfortunately I did not find any hint. If you know where to find the answer, please let me know too the source of your knowledge!          

How to know foreign key references for a table

Hi, For a parent table, having a parent id, is it possible to know all the child tables? for e.g if TABLE1 has PK1 as primary key, TABLE2 has PK2 which is a foreign key refering to TABLE1 and TABLE3 also has a foreign key referecing TABLE1 and many others.. is it possible to know all the child tables for table TABLE1?? Thanks,

Listing Foreign Keys

If I run the following code (quote many times on the internet), which should show all foreign keys in a database, but it only returns a partial list:   select CCU.table_name src_table, CCU.constraint_name src_constraint, CCU.column_name src_col, KCU.table_name target_table, KCU.column_name target_col, RC.UPDATE_RULE, RC.DELETE_RULE from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC, INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU where CCU.constraint_name = RC.constraint_name and RC.UNIQUE_CONSTRAINT_NAME = KCU.CONSTRAINT_NAME order by CCU.table_name, CCU.constraint_name   If I run the following, removing the KEY_COLUMN_USAGE link, it returns the full list:   select CCU.table_name src_table, CCU.constraint_name src_constraint, CCU.column_name src_col, RC.UNIQUE_CONSTRAINT_NAME, RC.CONSTRAINT_NAME, RC.UPDATE_RULE, RC.DELETE_RULE from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC where CCU.constraint_name = RC.constraint_name order by CCU.table_name, CCU.constraint_name   I can't figure out what the problem is, the ones that are being left out of the first query ad the ones where the unique_constraint is set to the column name, not the constraint's name. For example, the first one appears in both query results, the second one only appears in the secod queries results:   src_table src_constr

Foreign characters causing a duplicate attribute key error - how to avoid?

Greetings, We have a data warehouse into which we import data obtained from our outside vendors.  The data are then used to build data cubes.  Lately we have started to get a duplicate key error on the "County" attribute in our "Location" dimension.  The problem appears to be caused by these two values returned by the query that returns the distinct list of counties: Spree-Neisse Spree-Neiße These are German counties and they are really the same county, I believe.  Our data warehouse stores County as a varchar.  If I cast the stored value as nvarchar, the query that returns the distinct list of counties returns only the 2nd value and so should avoid the duplicate key error.  (I haven't tried it yet, just speculating.) This post suggests the root cause of the problem: http://ssas-musings.blogspot.com/2009/11/duplicate-key-error.html We don't have any control over the data before our vendors give it to us, and it comes from all over the world.  What is the best way to avoid occurrences of the duplicate attribute key error?  Should we be storing all our character data as nvarchar?  Would it be enough to cast the stored data as nvarchar when the cubes are being built?  Is there a better approach?  I'd like to get an idea of what the best practice would be.  Our warehouse

Create a sql foreign key constraint

Hi, I wish to create a constraint that state as below Code.CodeTable ( CodeID smallint, CategoryID smallint,....)  --> Parent Table Admin.Document( DocumentTypeID smallint,.....)  --> Child Table The FK will be Admin.Document.DocumentTypeID map with Code.CodeTable.CodeID I wish to have the contraint that only check Code.CodeTable.CodeID which the Code.CodeTable.CategoryID =15 only. Thanks.Meng Chew

Foreign Key fields are moved to the bottom of my detailsview

Hey All, I'm not sure if you would consider it a foreign key field or not, but I have an agency table with an association to my Project table.  It exists primarily to limit the input values of agency in the Project table to those listed in the Agency table.  Anyway, everything works fine, except that when you insert/add a project, the Agency field is moved to the end of the list.  I'd like to move it back where it belongs (further up the list). How?  Thanks.

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign

Using Visual Studio with MySQL.In my XSD dataset I created a query. It runs perfect. I can preview the data fine.In my BLL I wrote code (see below) to retrieve the query results and I'm getting...Using db As New dsDemoTableAdapters.DemoTableAdapter Dim dt As New DataTable dt = db.GetDemo(DemoId) ' ERROR HAPPENS HEREFailed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.Why would previewing the data work but in code it fails?Any ideas?

disable foreign key link depending on column value in the current row using Attributes


Hi ,

I want to disable  some foreign key links depending on column value in the current row

I am trying to have attribute like this :


 public partial class Order

        internal sealed class OrderMetadata
            public EntityCollection<Order_Detail> Order_Details { get; set; }

            public Nullable<DateTime> OrderDate { get; set; }



I had a look at Mr. Steve code for row highlighting , Conditional UIHint


Identity Insert Error & Conflict with Foreign Key


So I'm trying learn SQL using the CBT Nuggets and am at a point where they must have made a mistake because I've gone through and followed everything to a fine detail and they must have left something out.

So I am trying to insert a row into a table and get following IDENTITY_INSERT error:


So I do some research and find out how to turn on the INDENTITY_INSERT as well as identify the column list. After doing so I get a foreign key error as before attempting to enter the row I created a relationship between the 'country' column and the 'country_code' column from the countries.dbo table. (see below for code).


Foreign Key Error

The error it fully states is:

(0 row(s) affected)
Msg 547, Level 16, State 0, Line 3
The INSERT statement conflicted with the FOREIGN KEY constrai

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend