none
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

    Question

  • Hi guys I had to import a table into sql from Access and it needs to be Joined with another table on sql server. but I am getting a Error. How do I deal with this??
    'People_tbl' table saved successfully
    'Contact_sc' table
    - Unable to create relationship 'FK_Contact_sc_People_tbl'.
    The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Contact_sc_People_tbl". The conflict occurred in database "SCAIR", table "dbo.People_tbl", column 'Parent ID'.
    [Parent ID] would be the foreign key in the Contact_sc . sorry about that what I was wantig to know is how would I alter the table so that there can be a relationship between the two?? I just imported the table and tried to create a relationship with the foreign key [parent id] to the People_tbls primary key [Parent Id] and I got that error


    contact_sc

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Contact_sc](
    [ID] [int] NOT NULL,
    [Contact Date] [datetime] NULL,
    [Parent ID] [nvarchar](50) NULL,
    [Type of Contact] [nvarchar](50) NULL,
    [Purpose of Contact] [nvarchar](max) NULL,
    [Referral Date] [datetime] NULL,
    [Earned hours] [float] NULL,
    [Catagory for hours] [nvarchar](255) NULL,
    [Services Covered] [nvarchar](255) NULL,
    [State Catagory] [nvarchar](255) NULL,
    [State Services Covered] [nvarchar](255) NULL,
    CONSTRAINT [PK_Contact_sc] PRIMARY KEY CLUSTERED
    (
    [ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    people_tbl

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[People_tbl](
    [Parent ID] [nvarchar](50) NOT NULL,
    [Family ID] [nvarchar](50) NULL,
    [StudtID] [nvarchar](50) NULL,
    [Date of Referral] [nvarchar](50) NULL,
    [Parent First Name] [nvarchar](50) NULL,
    [Parent Last Name] [nvarchar](50) NULL,
    [Parent SS#] [nvarchar](50) NULL,
    [DOB] [datetime] NULL,
    [____] [nvarchar](50) NULL,
    [Telephone #] [nvarchar](50) NULL,
    [Message #] [nvarchar](50) NULL,
    [Address] [nvarchar](50) NULL,
    [City] [nvarchar](50) NULL,
    [State] [nvarchar](50) NULL,
    [Zip] [nvarchar](50) NULL,
    [E Mail Address] [nvarchar](50) NULL,
    [Tribal Affiliation] [nvarchar](50) NULL,
    [Event ID] [nvarchar](50) NULL,
    [TANF staff making Referral] [nvarchar](50) NULL,
    [ReferralLocation] [nvarchar](50) NULL,
    [RegistrationDate] [datetime] NULL,
    [Type Participant] [nvarchar](50) NULL,
    [Required hours] [int] NULL,
    [Special Instrution] [nvarchar](max) NULL,
    [GED] [bit] NULL,
    [High School Diploma] [bit] NULL,
    [Drivers License] [bit] NULL,
    [Assessement Date] [bit] NULL,
    [Assessement] [datetime] NULL,
    [Career Assessment Date] [bit] NULL,
    [Career Assessment] [datetime] NULL,
    [Other] [bit] NULL,
    [Explain Other] [nvarchar](50) NULL,
    [GED/High School Diploma-VC Adult School] [nvarchar](50) NULL,
    [Higher Education] [nvarchar](50) NULL,
    [Culture] [nvarchar](50) NULL,
    [Community Service] [nvarchar](50) NULL,
    [Vocational] [nvarchar](50) NULL,
    [DMV] [nvarchar](50) NULL,
    [SchoolAddress] [nvarchar](50) NULL,
    [SchoolName] [nvarchar](50) NULL,
    [SchoolPhone] [nvarchar](50) NULL,
    [SchoolCity] [nvarchar](50) NULL,
    [SchoolState] [nvarchar](50) NULL,
    [SchoolZip] [nvarchar](50) NULL,
    [TimeSchoolStart] [datetime] NULL,
    [TimeSchoolEnds] [datetime] NULL,
    [StudentGPA] [nvarchar](50) NULL,
    [Grade] [nvarchar](50) NULL,
    [Age] [nvarchar](50) NULL,
    [StudentschoolID] [nvarchar](50) NULL,
    [SchoolID] [nvarchar](50) NULL,
    [StudentRelease] [nvarchar](50) NULL,
    [ParentRelease] [nvarchar](50) NULL,
    [DateOfRelease] [datetime] NULL,
    [TanfReferral] [nvarchar](50) NULL,
    CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED
    (
    [Parent ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    Tuesday, August 04, 2009 3:52 AM

Answers

  • Hi Muzzettemm

    Thank you for the reply.

    in order to resolve the issue, I would like to explain the following
    1 after receiving the code you posted here, I create 2 empty tables(tand_tbl and perple_tbl) and then I add the FOREIGN KEY between the 2 empty tables succsssfully. Therefore the issue is related to the data in the 2 tables.

    2 Based on my expereince, when the table(tand_tbl) in the foreign key relation has some some data that does not have a match in the primary key colum of the table (perple_tbl), the issue will happens. we could verify that by comparing the data(parent_id) of the 2 tables.

    3 if the above doesn't resolve the problem, please reproduce the issue and capture the SQL Profiler Trace to further diagnose the problem.

    I look forward to your update.

    Regards
    Mark Han
    Monday, August 10, 2009 4:18 AM

All replies

  • When a FOREIGN KEY constraint is added to an existing column or columns in the table, by default, the Database Engine examines the existing data in the columns to make sure that all values, except NULL, exist in the columns of the referenced PRIMARY KEY or UNIQUE constraint. However, by specifying WITH NOCHECK, the Database Engine can be prevented from checking the data in the column against the new constraint and made to add the new constraint regardless of the data in the column. The WITH NOCHECK option is useful when the existing data already meets the new FOREIGN KEY constraint, or when a business rule requires the constraint to be enforced only from this point forward.

    For example:

    ALTER TABLE [Table Name]  WITH NOCHECK ADD  CONSTRAINT [Constraint Name] FOREIGN KEY([Column Name])
    REFERENCES [Table Reference]([Column Name])

    Tuesday, August 04, 2009 4:20 AM
  • ok so I need to alter the table, I still new to this so how would I do that


    Alter Table [People_tbl] with no check add constraint [not sure about this part] foreign key ([Parent id]??
    Tuesday, August 04, 2009 4:49 AM
  • Here is your code should be

    Alter Table [Contact_sc] with no check add constraint [FK_Contact_sc_People_tbl] foreign key ([Parent id])
    REFERENCES [People_tbl]([Parent ID])


    Tuesday, August 04, 2009 5:26 AM
  • gave me an errror message

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'no'.

    Tuesday, August 04, 2009 5:29 AM
  • nm got it nocheck
    Tuesday, August 04, 2009 5:29 AM
  • Yeah NoCheck. sorry for that.
    Tuesday, August 04, 2009 5:33 AM
  • thank you so much Andrian
    Tuesday, August 04, 2009 8:04 PM
  • Can you help me again I added another table and tried to create a replationship with the People_tbl but its giving me an error

    'People_tbl' table saved successfully
    'Tanf_tbl' table
    - Unable to create relationship 'FK_Tanf_tbl_People_tbl'. 
    The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Tanf_tbl_People_tbl". The conflict occurred in database "SCAIR", table "dbo.People_tbl", column 'Parent ID'.

    the thing is I ran the script you gave me and its not working for some reason

    Alter

     

    Table [Tanf_tbl] with NOCHECK add constraint [FK_Tanf_tbl_People_tbl] foreign key ([Parent ID])

    REFERENCES

     

    [People_tbl]([Parent ID])

    Wednesday, August 05, 2009 10:14 PM
  • can u paste Tanf_tbl structure?
    Thursday, August 06, 2009 1:10 AM
  • Hi Muzzettemm

    This is Mark, Microsoft SQL Support Engineer. I'm glad to assist you with the issue.

    Based on the current information, in order to resolve the problem, we need to know the stucture of the table Tanf_tbl. So if possible, please post the detail code which you use to create the table. Thanks

    Regards
    Mark Han
    Thursday, August 06, 2009 8:12 AM
  • Hi Mark sure here it is

     

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Tanf_tbl](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Contact Date] [datetime] NULL,
    [Parent ID] [nvarchar](50) NULL,
    [Type of Contact] [nvarchar](50) NULL,
    [Purpose of Contact] [nvarchar](max) NULL,
    [Referral Date] [datetime] NULL,
    [Earned hours] [float] NULL,
    [Catagory for hours] [nvarchar](255) NULL,
    [Services Covered] [nvarchar](255) NULL,
    [State Catagory] [nvarchar](255) NULL,
    [State Services Covered] [nvarchar](255) NULL,
    [SubCatagory] [nvarchar](50) NULL,
    [DateOfCall] [datetime] NULL,
    [DateofPostCard] [datetime] NULL,
    [DateReferredBack] [datetime] NULL,
    [PersonMetWith] [nvarchar](50) NULL,
    [Message] [text] NULL,
    [TypeOfContact] [nvarchar](50) NULL,
    CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED 
    (
    [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

     

     

     

    Friday, August 07, 2009 4:35 PM
  • Hi Muzzettemm

    Thank you for the reply.

    in order to resolve the issue, I would like to explain the following
    1 after receiving the code you posted here, I create 2 empty tables(tand_tbl and perple_tbl) and then I add the FOREIGN KEY between the 2 empty tables succsssfully. Therefore the issue is related to the data in the 2 tables.

    2 Based on my expereince, when the table(tand_tbl) in the foreign key relation has some some data that does not have a match in the primary key colum of the table (perple_tbl), the issue will happens. we could verify that by comparing the data(parent_id) of the 2 tables.

    3 if the above doesn't resolve the problem, please reproduce the issue and capture the SQL Profiler Trace to further diagnose the problem.

    I look forward to your update.

    Regards
    Mark Han
    Monday, August 10, 2009 4:18 AM