Конфликт инструкции alter table с ограничением foreign key как исправить - Все инструкции и руководства по применению

Why does add a foreign key to the tblDomare table result in this error?

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint «FK__tblDomare__PersN__5F7E2DAC». The conflict occurred in database «almu0004», table «dbo.tblBana», column ‘BanNR’.

Code

CREATE TABLE tblDomare
(PersNR VARCHAR (15) NOT NULL,
fNamn VARCHAR (15) NOT NULL,
eNamn VARCHAR (20) NOT NULL,
Erfarenhet VARCHAR (5),
PRIMARY KEY (PersNR));

INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet)
Values (6811034679,'Bengt','Carlberg',10);

INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet)
Values (7606091347,'Josefin','Backman',4);

INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet)
Values (8508284163,'Johanna','Backman',1);

CREATE TABLE tblBana
(BanNR VARCHAR (15) NOT NULL,
PRIMARY KEY (BanNR));

INSERT INTO tblBana (BanNR)
Values (1);

INSERT INTO tblBana (BanNR)
Values (2);

INSERT INTO tblBana (BanNR)
Values (3);

ALTER TABLE tblDomare
ADD FOREIGN KEY (PersNR)
REFERENCES tblBana(BanNR);

ΩmegaMan

29.5k12 gold badges100 silver badges122 bronze badges

asked Feb 17, 2014 at 21:09

It occurred because you tried to create a foreign key from tblDomare.PersNR to tblBana.BanNR but/and the values in tblDomare.PersNR didn’t match with any of the values in tblBana.BanNR. You cannot create a relation which violates referential integrity.

answered Feb 17, 2014 at 21:16

SmutjeSmutje

17.7k4 gold badges24 silver badges41 bronze badges

This query was very useful for me. It shows all values that don’t have any matches

select FK_column from FK_table
WHERE FK_column NOT IN
(SELECT PK_column from PK_table)

answered Jul 4, 2016 at 8:22

dantey89dantey89

2,17724 silver badges37 bronze badges

Try this solution:

There is a data item in your table whose associated value doesn’t exist in the table you want to use it as a primary key table.
Make your table empty or add the associated value to the second table.

DreamTeK

32.5k27 gold badges112 silver badges171 bronze badges

answered Apr 6, 2018 at 7:34

PatsonLeanerPatsonLeaner

1,23015 silver badges26 bronze badges

It is possible to create the foreign key using ALTER TABLE tablename WITH NOCHECK …, which will allow data that violates the foreign key.

«ALTER TABLE tablename WITH NOCHECK …» option to add the FK — This solution worked for me.

answered Aug 9, 2016 at 6:19

Remove all existing data from your tables and then make a relation between the tables.

ΩmegaMan

29.5k12 gold badges100 silver badges122 bronze badges

answered Aug 20, 2015 at 7:21

maxmax

2292 silver badges3 bronze badges

Before You add Foreign key to the table, do the following

Make sure the table must empty or The column data should match.
Make sure it is not null.
If the table contains do not go to design and change, do it manually.

alter table Table 1 add foreign key (Column Name) references Table 2 (Column Name)

alter table Table 1 alter column Column Name attribute not null

soccer7

3,5573 gold badges29 silver badges50 bronze badges

answered Sep 1, 2015 at 5:08

GirishBabuCGirishBabuC

1,26715 silver badges20 bronze badges

I guess, a column value in a foreign key table should match with the column value of the primary key table. If we are trying to create a foreign key constraint between two tables where the value inside one column(going to be the foreign key) is different from the column value of the primary key table then it will throw the message.

So it is always recommended to insert only those values in the Foreign key column which are present in the Primary key table column.

For ex. If the Primary table column has values 1, 2, 3 and in Foreign key column the values inserted are different, then the query would not be executed as it expects the values to be between 1 & 3.

answered Nov 27, 2014 at 15:46

sam05sam05

1992 silver badges4 bronze badges

In very simple words your table already has data present in it and the table you are trying to create relationship with does have that Primary key set for the values that are already present.

Either delete all the values of the existing table.
Add all the values of foreign key reference in the new table.

answered Oct 27, 2021 at 10:27

Try DELETE the current datas from tblDomare.PersNR . Because the values in tblDomare.PersNR didn’t match with any of the values in tblBana.BanNR.

propoLis

1,2291 gold badge14 silver badges48 bronze badges

answered Jun 8, 2018 at 6:50

When you define a Foreign Key in table B referencing the Primary Key of table A it means that when a value is in B, it must be in A. This is to prevent unconsistent modifications to the tables.

In your example, your tables contain:

tblDomare with PRIMARY KEY (PersNR):

PersNR     |fNamn     |eNamn      |Erfarenhet
-----------|----------|-----------|----------
6811034679 |'Bengt'   |'Carlberg' |10
7606091347 |'Josefin' |'Backman'  |4
8508284163 |'Johanna' |'Backman'  |1
---------------------------------------------

tblBana:

BanNR
-----
1
2
3
-----

This statement:

ALTER TABLE tblDomare
ADD FOREIGN KEY (PersNR)
REFERENCES tblBana(BanNR);

says that any line in tblDomare with key PersNR must have a correspondence in table tblBana on key BanNR. Your error is because you have lines inserted in tblDomare with no correspondence in tblBana.

2 solutions to fix your issue:

either add lines in tblBana with BanNR in (6811034679, 7606091347, 8508284163)
or remove all lines in tblDomare that have no correspondence in tblBana (but your table would be empty)

General advice: you should have the Foreign Key constraint before populating the tables. Foreign keys are here to prevent the user of the table from filling the tables with inconsistencies.

answered Jun 3, 2020 at 12:40

belkabelka

1,4801 gold badge18 silver badges31 bronze badges

i had this error too
as Smutje reffered make sure that you have not a value in foreign key column of your base foreign key table that is not in your reference table i.e(every value in your base foreign key table(value of a column that is foreign key) must also be in your reference table column)
its good to empty your base foreign key table first then set foreign keys

answered Feb 2, 2015 at 6:06

the data you have entered a table(tbldomare) aren’t match a data you have assigned primary key table. write between tbldomare and add this word (with nocheck) then execute your code.

for example you entered a table tbldomar this data

INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet)
Values (6811034679,'Bengt','Carlberg',10);

and you assigned a foreign key table to accept only 1,2,3.

you have two solutions one is delete the data you have entered a table then execute the code. another is write this word (with nocheck) put it between your table name and add
like this

ALTER TABLE  tblDomare with nocheck
ADD FOREIGN KEY (PersNR)
REFERENCES tblBana(BanNR);

answered Nov 2, 2016 at 9:40

Smutje is correct and Chad HedgeCock offered a great layman’s example.
Id like to build on Chad’s example by offering a way to find/delete those records.
We will use Customer as the Parent and Order as the child. CustomerId is the common field.

select * from Order Child 
left join Customer Parent on Child.CustomerId = Parent.CustomerId
where Parent.CustomerId is null

if you are reading this thread… you will get results. These are orphaned children. select * from Order Child
left join Customer Parent on Child.CustomerId = Parent.CustomerId
where Parent.CustomerId is null Note the row count in the bottom right.

Go verify w/ whomever you need to that you are going to delete these rows!

begin tran 
delete Order
from Order Child 
left join Customer Parent on Child.CustomerId = Parent.CustomerId
where Parent.CustomerId is null

Run the first bit.
Check that row count = what you expected

commit the tran

commit tran

Be careful. Someone’s sloppy programming got you into this mess. Make sure you understand the why before you delete the orphans. Maybe the parent needs to be restored.

answered Nov 16, 2017 at 18:11

greggreg

1,6731 gold badge17 silver badges30 bronze badges

From our end, this is the scenario:

We have an existing table in the database with records.
Then I introduces a NOT nullable foreign key
After executing the update i got this error.

How did i solve you ask?

SOLUTION: I just removed all the records of the table, then tried to update the database and it was successful.

answered Jun 1, 2021 at 12:46

VJPPazVJPPaz

9177 silver badges21 bronze badges

This happens to me, since I am designing my database, I notice that I change my seed on my main table, now the relational table has no foreign key on the main table.

So I need to truncate both tables, and it now works!

answered Mar 16, 2018 at 17:46

Willy David JrWilly David Jr

8,6146 gold badges46 silver badges57 bronze badges

You should see if your tables has any data on the rows. If «yes» then you should truncate the table(s) or else you can make them to have the same number of data at tblDomare.PersNR to tblBana.BanNR and vise-verse.

answered Oct 23, 2018 at 21:44

In my scenario, using EF, upon trying to create this new Foreign Key on existing data, I was wrongly trying to populate the data (make the links) AFTER creating the foreign key.

The fix is to populate your data before creating the foreign key since it checks all of them to see if the links are indeed valid. So it couldn’t possibly work if you haven’t populated it yet.

answered Aug 9, 2019 at 11:08

jeromejjeromej

10.5k2 gold badges43 silver badges62 bronze badges

I encounter some issue in my project.

enter image description here

In child table, there isn’t any record Id equals 1 and 11

mage

I inserted DEAL_ITEM_THIRD_PARTY_PO table which Id equals 1 and 11 then I can create FK

answered Oct 21, 2019 at 6:27

Metin AtalayMetin Atalay

1,37518 silver badges28 bronze badges

Please first delete data from that table and then run the migration again. You will get success

answered Apr 19, 2020 at 16:43

I had the same problem.
My issue was having nullable: true in column (migration file):

AddColumn("dbo.table", "column", c => c.Int(nullable: true));

Possible Solutions:

Change nullable ‘false’ to ‘true’. (Not Recommended)
Change property type from int to int? (Recommended)

And if required, change this later after adding column > then missing field data in previous records

If you’ve changed an existing property from nullable to non-nullable:
3) Fill the column data in database records

answered Apr 29, 2021 at 12:15

A foreign key constraint in a child table must have a parent table with a primary key. The primary key must be unique. The foreign key value must match a value in the patent table primary key

answered May 1, 2021 at 15:52

Golden LionGolden Lion

3,8402 gold badges26 silver badges35 bronze badges

When you alter table column from nullable to not nullable column where this column is foreign key, you must :

Firstly, initialize this column with value (because it is foreign
key not nullable).
After that you can alter your table column normally.

answered Sep 17, 2021 at 6:23

Please try below query:

CREATE TABLE tblBana
(BanNR VARCHAR (15) NOT NULL PRIMARY KEY,

);

CREATE TABLE tblDomare
(PersNR VARCHAR (15) NOT NULL PRIMARY KEY,
fNamn VARCHAR (15) NOT NULL,
eNamn VARCHAR (20) NOT NULL,
Erfarenhet VARCHAR (5),
FK_tblBana_Id VARCHAR (15) references  tblBana (BanNR)
);


INSERT INTO tblBana (BanNR)
Values (3);


INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet,FK_tblBana_Id)
Values (8508284173,'Johanna','Backman',1,3);

answered Dec 9, 2021 at 7:01

or you can use this

SELECT  fk_id FROM dbo.tableA
Except
SELECT fk_id From dbo.tableB

desertnaut

57.6k26 gold badges140 silver badges166 bronze badges

answered Apr 16, 2021 at 9:13

and just FYI, in case you do all of your data reference checks and find no bad data…apparently it is not possible to create a foreign key constraint between two tables and fields where those fields are the primary key in both tables! Do not ask me how I know this.

answered Jul 10, 2017 at 15:13

Doug BoudeDoug Boude

271 silver badge5 bronze badges

Источник

Здраствуйте, возникла такая проблема, я хочу поставить связь от fact к dimension2 по ключу KOATUU и от dimension3 к dimension2 по ключу nameLocality, но возникают ошибки такого характера:
[SQL Server]Конфликт инструкции ALTER TABLE с ограничением FOREIGN KEY "FK__fact__KOATUU__505BE5AD". Конфликт произошел в базе данных "OLAP", таблица "dbo.dimension2", column 'KOATUU'.
введите сюда описание изображения

Хочу, чтобы вот так было. Подскажите, пожалуйста, что делаю не так и как стоит сделать. Буду очень благодарен.
Ниже представлено, как создавал ключи и связи, а также таблицы

ALTER TABLE fact ALTER COLUMN id int not null
ALTER TABLE fact ALTER COLUMN KOATUU bigint not null
ALTER TABLE fact ALTER COLUMN OPER_CODE int not null
ALTER TABLE fact ALTER COLUMN DEP_CODE int not null
ALTER TABLE departament ALTER COLUMN DEP_CODE int not null
ALTER TABLE operation ALTER COLUMN OPER_CODE int not null")
ALTER TABLE fact ADD PRIMARY KEY (id,KOATUU,OPER_CODE,DEP_CODE)
ALTER TABLE departament ADD PRIMARY KEY (DEP_CODE)
ALTER TABLE operation ADD PRIMARY KEY (OPER_CODE)
ALTER TABLE fact ADD FOREIGN KEY (DEP_CODE) REFERENCES departament(DEP_CODE)
ALTER TABLE fact ADD FOREIGN KEY (OPER_CODE) REFERENCES operation(OPER_CODE)
ALTER TABLE dimension2 ALTER COLUMN KOATUU bigint not null
ALTER TABLE dimension2 ALTER COLUMN nameLocality varchar(255) not null
ALTER TABLE dimension3 ALTER COLUMN nameLocality varchar(255) not null
ALTER TABLE dimension2 ADD PRIMARY KEY (KOATUU,nameLocality)
ALTER TABLE dimension3 ALTER COLUMN id int not null
ALTER TABLE dimension3 ADD PRIMARY KEY (id,nameLocality)
ALTER TABLE fact ADD FOREIGN KEY (KOATUU) REFERENCES dimension2(KOATUU)
ALTER TABLE dimension3 ADD FOREIGN KEY (nameLocality) REFERENCES dimension2(nameLocality)

Fact:

CREATE TABLE [dbo].[fact](
    [id] [int] NOT NULL,
    [KOATUU] [bigint] NOT NULL,
    [OPER_CODE] [int] NOT NULL,
    [D_REG] [varchar](max) NULL,
    [DEP_CODE] [int] NOT NULL,
    [BRAND] [varchar](max) NULL,
    [MODEL] [varchar](max) NULL,
    [MAKE_YEAR] [bigint] NULL,
    [COLOR] [varchar](max) NULL,
    [KIND] [varchar](max) NULL,
    [BODY] [varchar](max) NULL,
    [PURPOSE] [varchar](max) NULL,
    [FUEL] [varchar](max) NULL,
    [CAPACITY] [float] NULL,
    [OWN_WEIGHT] [varchar](max) NULL,
    [TOTAL_WEIGHT] [varchar](max) NULL,
    [N_REG_NEW] [varchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
    [id] ASC,
    [KOATUU] ASC,
    [OPER_CODE] ASC,
    [DEP_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[fact]  WITH CHECK ADD FOREIGN KEY([DEP_CODE])
REFERENCES [dbo].[departament] ([DEP_CODE])
GO

ALTER TABLE [dbo].[fact]  WITH CHECK ADD FOREIGN KEY([OPER_CODE])
REFERENCES [dbo].[operation] ([OPER_CODE])

Departament:

CREATE TABLE [dbo].[departament](
    [DEP_CODE] [int] NOT NULL,
    [DEP] [varchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
    [DEP_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Operation:

CREATE TABLE [dbo].[operation](
    [OPER_CODE] [int] NOT NULL,
    [OPER_NAME] [varchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
    [OPER_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Dimension2:

CREATE TABLE [dbo].[dimension2](
    [administrativeTeritorialUnit] [varchar](max) NULL,
    [nameAdministrativeTeritorialUnit] [varchar](max) NULL,
    [KOATUU] [bigint] NOT NULL,
    [nameMedicalInstitution] [varchar](max) NULL,
    [nameObject] [varchar](max) NULL,
    [typeInstitution] [varchar](max) NULL,
    [locality] [varchar](max) NULL,
    [nameLocality] [varchar](255) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [KOATUU] ASC,
    [nameLocality] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Dimension3:

CREATE TABLE [dbo].[dimension3](
    [id] [int] NOT NULL,
    [OBL_NAME] [varchar](max) NULL,
    [STREET_NAME] [varchar](max) NULL,
    [type] [varchar](max) NULL,
    [nameLocality] [varchar](255) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [id] ASC,
    [nameLocality] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Источник

If you are a developer dealing with database management, you must have come across the ‘Alter Table Statement Conflicted with Foreign Key Constraint’ error. This error occurs when you try to alter a table which has a foreign key constraint that references other tables. It can be frustrating, especially when you are not familiar with the solution. In this guide, we will discuss how to resolve this error step-by-step.

Step 1: Identify the Foreign Key Constraint

The first step in resolving this error is to identify the foreign key constraint causing the conflict. You can use the following SQL query to list all the foreign key constraints in your database:

SELECT f.name AS foreign_key_name,
   OBJECT_NAME(f.parent_object_id) AS table_name,
   COL_NAME(fc.parent_object_id,
   fc.parent_column_id) AS constraint_column_name,
   OBJECT_NAME (f.referenced_object_id) AS referenced_object,
   COL_NAME(fc.referenced_object_id,
   fc.referenced_column_id) AS referenced_column_name
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

This query will list all the foreign key constraints in your database with their table names, column names, and referenced tables and columns. From the result, you can identify the foreign key constraint causing the conflict.

Step 2: Disable the Foreign Key Constraint

Once you have identified the foreign key constraint causing the conflict, the next step is to disable it temporarily. You can use the following SQL query to disable the foreign key constraint:

ALTER TABLE <Table_Name> NOCHECK CONSTRAINT <Constraint_Name>

Replace <Table_Name> with the name of the table containing the foreign key constraint, and <Constraint_Name> with the name of the foreign key constraint causing the conflict.

Step 3: Execute the ALTER TABLE Statement

After disabling the foreign key constraint, you can now execute the ALTER TABLE statement that was causing the conflict. Once the statement is executed successfully, you can re-enable the foreign key constraint using the following SQL query:

ALTER TABLE <Table_Name> WITH CHECK CHECK CONSTRAINT <Constraint_Name>

Replace <Table_Name> and <Constraint_Name> with the table name and constraint name you disabled in step 2.

FAQ

Q1. Can I disable all foreign key constraints in my database?

A1. No, you cannot disable all foreign key constraints in your database. Disabling a foreign key constraint can cause data inconsistencies and violate referential integrity.

Q2. Can I use the same name for a foreign key constraint in different tables?

A2. Yes, you can use the same name for a foreign key constraint in different tables.

Q3. Can I disable a foreign key constraint permanently?

A3. No, you should not disable a foreign key constraint permanently. It can cause data inconsistencies and violate referential integrity.

Q4. Can I re-enable a foreign key constraint without checking it?

A4. Yes, you can re-enable a foreign key constraint without checking it using the following SQL query:

ALTER TABLE <Table_Name> WITH NOCHECK CHECK CONSTRAINT <Constraint_Name>

Replace <Table_Name> and <Constraint_Name> with the table name and constraint name you disabled in step 2.

Q5. What is referential integrity?

A5. Referential integrity is a database constraint that ensures that data relationships between tables are valid and consistent. It prevents orphaned records and maintains data consistency.

Conclusion

In this guide, we discussed how to resolve the ‘Alter Table Statement Conflicted with Foreign Key Constraint’ error in SQL Server. We provided a step-by-step solution that involves identifying the foreign key constraint causing the conflict, disabling it temporarily, executing the ALTER TABLE statement, and re-enabling the foreign key constraint. We also discussed some frequently asked questions related to foreign key constraints and referential integrity. Hopefully, this guide has helped you resolve the error and improve your database management skills.

How to Create Foreign Key Constraints in SQL Server
Understanding Referential Integrity and Foreign Keys in SQL Server
Disabling Constraints in SQL Server

Источник

Remove From My Forums

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]

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
- Marked as answer by
  
  Thursday, August 13, 2009 9:25 AM

Источник

I have a problem when trying to add a foreign key to my tblDomare table; what am I doing wrong here?

CREATE TABLE tblDomare
(PersNR VARCHAR (15) NOT NULL,
fNamn VARCHAR (15) NOT NULL,
eNamn VARCHAR (20) NOT NULL,
Erfarenhet VARCHAR (5),
PRIMARY KEY (PersNR));

INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet)
Values (6811034679,'Bengt','Carlberg',10);

INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet)
Values (7606091347,'Josefin','Backman',4);

INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet)
Values (8508284163,'Johanna','Backman',1);

CREATE TABLE tblBana
(BanNR VARCHAR (15) NOT NULL,
PRIMARY KEY (BanNR));

INSERT INTO tblBana (BanNR)
Values (1);

INSERT INTO tblBana (BanNR)
Values (2);

INSERT INTO tblBana (BanNR)
Values (3);

ALTER TABLE tblDomare
ADD FOREIGN KEY (PersNR)
REFERENCES tblBana(BanNR);

Error message:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint «FK_tblDomare_PersN__5F7E2DAC». The conflict occurred in database «almu0004», table «dbo.tblBana», column ‘BanNR’.

This question is related to
sql
sql-server
database
foreign-keys

The answer is

This query was very useful for me. It shows all values that don’t have any matches

select FK_column from FK_table
WHERE FK_column NOT IN
(SELECT PK_column from PK_table)

Try this solution:

There is a data item in your table whose associated value doesn’t exist in the table you want to use it as a primary key table.
Make your table empty or add the associated value to the second table.

It is possible to create the foreign key using ALTER TABLE tablename WITH NOCHECK …, which will allow data that violates the foreign key.

«ALTER TABLE tablename WITH NOCHECK …» option to add the FK — This solution worked for me.

So it is always recommended to insert only those values in the Foreign key column which are present in the Primary key table column.

For ex. If the Primary table column has values 1, 2, 3 and in Foreign key column the values inserted are different, then the query would not be executed as it expects the values to be between 1 & 3.

Before You add Foreign key to the table, do the following

Make sure the table must empty or The column data should match.
Make sure it is not null.
If the table contains do not go to design and change, do it manually.

alter table Table 1 add foreign key (Column Name) references Table 2 (Column Name)

alter table Table 1 alter column Column Name attribute not null

Clean your data from your tables and then make relation between them.

Try DELETE the current datas from tblDomare.PersNR . Because the values in tblDomare.PersNR didn’t match with any of the values in tblBana.BanNR.

When you define a Foreign Key in table B referencing the Primary Key of table A it means that when a value is in B, it must be in A. This is to prevent unconsistent modifications to the tables.

In your example, your tables contain:

tblDomare with PRIMARY KEY (PersNR):

PersNR     |fNamn     |eNamn      |Erfarenhet
-----------|----------|-----------|----------
6811034679 |'Bengt'   |'Carlberg' |10
7606091347 |'Josefin' |'Backman'  |4
8508284163 |'Johanna' |'Backman'  |1
---------------------------------------------

tblBana:

BanNR
-----
1
2
3
-----

This statement:

ALTER TABLE tblDomare
ADD FOREIGN KEY (PersNR)
REFERENCES tblBana(BanNR);

2 solutions to fix your issue:

either add lines in tblBana with BanNR in (6811034679, 7606091347, 8508284163)
or remove all lines in tblDomare that have no correspondence in tblBana (but your table would be empty)

General advice: you should have the Foreign Key constraint before populating the tables. Foreign keys are here to prevent the user of the table from filling the tables with inconsistencies.

In my scenario, using EF, upon trying to create this new Foreign Key on existing data, I was wrongly trying to populate the data (make the links) AFTER creating the foreign key.

The fix is to populate your data before creating the foreign key since it checks all of them to see if the links are indeed valid. So it couldn’t possibly work if you haven’t populated it yet.

the data you have entered a table(tbldomare) aren’t match a data you have assigned primary key table. write between tbldomare and add this word (with nocheck) then execute your code.

for example you entered a table tbldomar this data

INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet)
Values (6811034679,'Bengt','Carlberg',10);

and you assigned a foreign key table to accept only 1,2,3.

you have two solutions one is delete the data you have entered a table then execute the code. another is write this word (with nocheck) put it between your table name and add
like this

ALTER TABLE  tblDomare with nocheck
ADD FOREIGN KEY (PersNR)
REFERENCES tblBana(BanNR);

Please first delete data from that table and then run the migration again. You will get success

This happens to me, since I am designing my database, I notice that I change my seed on my main table, now the relational table has no foreign key on the main table.

So I need to truncate both tables, and it now works!

select * from Order Child 
left join Customer Parent on Child.CustomerId = Parent.CustomerId
where Parent.CustomerId is null

Go verify w/ whomever you need to that you are going to delete these rows!

begin tran 
delete Order
from Order Child 
left join Customer Parent on Child.CustomerId = Parent.CustomerId
where Parent.CustomerId is null

Run the first bit.
Check that row count = what you expected

commit the tran

commit tran

Be careful. Someone’s sloppy programming got you into this mess. Make sure you understand the why before you delete the orphans. Maybe the parent needs to be restored.

I encounter some issue in my project.

enter image description here

In child table, there isn’t any record Id equals 1 and 11

mage

I inserted DEAL_ITEM_THIRD_PARTY_PO table which Id equals 1 and 11 then I can create FK

Questions with sql tag:

• Passing multiple values for same variable in stored procedure
• SQL permissions for roles
• Generic XSLT Search and Replace template
• Access And/Or exclusions
• Pyspark: Filter dataframe based on multiple conditions
• Subtracting 1 day from a timestamp date
• PYODBC—Data source name not found and no default driver specified
• select rows in sql with latest date for each ID repeated multiple times
• ALTER TABLE DROP COLUMN failed because one or more objects access this column
• Create Local SQL Server database
• Export result set on Dbeaver to CSV
• How to create temp table using Create statement in SQL Server?
• SQL Query Where Date = Today Minus 7 Days
• How do I pass a list as a parameter in a stored procedure?
• #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’
• MySQL Error: : ‘Access denied for user ‘root’@’localhost’
• SQL Server IF EXISTS THEN 1 ELSE 2
• How to add a boolean datatype column to an existing table in sql?
• Presto SQL — Converting a date string to date format
• What is the meaning of <> in mysql query?
• Change Date Format(DD/MM/YYYY) in SQL SELECT Statement
• Convert timestamp to date in Oracle SQL
• #1292 — Incorrect date value: ‘0000-00-00’
• Postgresql tables exists, but getting «relation does not exist» when querying
• SQL query to check if a name begins and ends with a vowel
• Find the number of employees in each department — SQL Oracle
• Error in MySQL when setting default value for DATE or DATETIME
• Drop view if exists
• Could not find server ‘server name’ in sys.servers. SQL Server 2014
• How to create a Date in SQL Server given the Day, Month and Year as Integers
• TypeError: tuple indices must be integers, not str
• Select Rows with id having even number
• SELECT list is not in GROUP BY clause and contains nonaggregated column
• IN vs ANY operator in PostgreSQL
• How to insert date values into table
• Error related to only_full_group_by when executing a query in MySql
• How to select the first row of each group?
• Connecting to Microsoft SQL server using Python
• eloquent laravel: How to get a row count from a ->get()
• How to execute raw queries with Laravel 5.1?
• In Oracle SQL: How do you insert the current date + time into a table?
• Extract number from string with Oracle function
• Rebuild all indexes in a Database
• SQL: Two select statements in one query
• DB2 SQL error sqlcode=-104 sqlstate=42601
• What difference between the DATE, TIME, DATETIME, and TIMESTAMP Types
• How to run .sql file in Oracle SQL developer tool to import database?
• Concatenate columns in Apache Spark DataFrame
• How Stuff and ‘For Xml Path’ work in SQL Server?
• Fatal error: Call to a member function query() on null

Questions with sql-server tag:

• Passing multiple values for same variable in stored procedure
• SQL permissions for roles
• Count the Number of Tables in a SQL Server Database
• Visual Studio 2017 does not have Business Intelligence Integration Services/Projects
• ALTER TABLE DROP COLUMN failed because one or more objects access this column
• Create Local SQL Server database
• How to create temp table using Create statement in SQL Server?
• SQL Query Where Date = Today Minus 7 Days
• How do I pass a list as a parameter in a stored procedure?
• SQL Server date format yyyymmdd
• SQL Server IF EXISTS THEN 1 ELSE 2
• ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered on the local machine. (System.Data)
• How to add a boolean datatype column to an existing table in sql?
• How to import an Excel file into SQL Server?
• How to use the COLLATE in a JOIN in SQL Server?
• Change Date Format(DD/MM/YYYY) in SQL SELECT Statement
• Stored procedure with default parameters
• Drop view if exists
• Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object
• How to update large table with millions of rows in SQL Server?
• Could not find server ‘server name’ in sys.servers. SQL Server 2014
• How to create a Date in SQL Server given the Day, Month and Year as Integers
• Select Rows with id having even number
• A connection was successfully established with the server, but then an error occurred during the login process. (Error Number: 233)
• SQL Server: Error converting data type nvarchar to numeric
• How to add LocalDB to Visual Studio 2015 Community’s SQL Server Object Explorer?
• Using DISTINCT along with GROUP BY in SQL Server
• Rebuild all indexes in a Database
• How to generate Entity Relationship (ER) Diagram of a database using Microsoft SQL Server Management Studio?
• The target principal name is incorrect. Cannot generate SSPI context
• How Stuff and ‘For Xml Path’ work in SQL Server?
• How to view the roles and permissions granted to any database user in Azure SQL server instance?
• How do I create a local database inside of Microsoft SQL Server 2014?
• Format number as percent in MS SQL Server
• MSSQL Regular expression
• How to select all the columns of a table except one column?
• SQL count rows in a table
• EXEC sp_executesql with multiple parameters
• SQL Server : How to test if a string has only digit characters
• Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query
• Remove decimal values using SQL query
• How to drop all tables from a database with one SQL query?
• How to get last 7 days data from current datetime to last 7 days in sql server
• Get last 30 day records from today date in SQL Server
• Using Excel VBA to run SQL query
• No process is on the other end of the pipe (SQL Server 2012)
• How to subtract 30 days from the current date using SQL Server
• Calculate time difference in minutes in SQL Server
• How to join two tables by multiple columns in SQL?
• The database cannot be opened because it is version 782. This server supports version 706 and earlier. A downgrade path is not supported

Questions with database tag:

• Implement specialization in ER diagram
• phpMyAdmin — Error > Incorrect format parameter?
• Authentication plugin ‘caching_sha2_password’ cannot be loaded
• Room — Schema export directory is not provided to the annotation processor so we cannot export the schema
• SQL Query Where Date = Today Minus 7 Days
• MySQL Error: : ‘Access denied for user ‘root’@’localhost’
• SQL Server date format yyyymmdd
• How to create a foreign key in phpmyadmin
• WooCommerce: Finding the products in database
• TypeError: tuple indices must be integers, not str
• IN vs ANY operator in PostgreSQL
• How to execute raw queries with Laravel 5.1?
• How should I tackle —secure-file-priv in MySQL?
• How to use multiple databases in Laravel
• Why does Oracle not find oci.dll?
• How do I create a local database inside of Microsoft SQL Server 2014?
• How to change the default port of mysql from 3306 to 3360
• Laravel 5 error SQLSTATE[HY000] [1045] Access denied for user ‘homestead’@’localhost’ (using password: YES)
• How to change Oracle default data pump directory to import dumpfile?
• Using COALESCE to handle NULL values in PostgreSQL
• What is Hash and Range Primary Key?
• How to get item count from DynamoDB?
• Find duplicate records in MongoDB
• Django Model() vs Model.objects.create()
• Mongodb: Failed to connect to 127.0.0.1:27017, reason: errno:10061
• How can I rename column in laravel using migration?
• There is already an object named in the database
• How to list all databases in the mongo shell?
• Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user ‘root’@’localhost'(using password:YES)
• Partial Dependency (Databases)
• Warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given… what I do wrong?
• ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM: How to extend?
• Laravel: Error [PDOException]: Could not Find Driver in PostgreSQL
• Warning: mysqli_connect(): (HY000/1045): Access denied for user ‘username’@’localhost’ (using password: YES)
• The backend version is not supported to design database diagrams or tables
• Difference between partition key, composite key and clustering key in Cassandra?
• Backup/Restore a dockerized PostgreSQL database
• Can’t connect to MySQL server on ‘127.0.0.1’ (10061) (2003)
• copy from one database to another using oracle sql developer — connection failed
• Best way to check for «empty or null value»
• ORA-12528: TNS Listener: all appropriate instances are blocking new connections. Instance «CLRExtProc», status UNKNOWN
• getting error HTTP Status 405 — HTTP method GET is not supported by this URL but not used `get` ever?
• How do I to insert data into an SQL table using C# as well as implement an upload function?
• Postgres — Transpose Rows to Columns
• How to update multiple columns in single update statement in DB2
• How to increase MySQL connections(max_connections)?
• What are OLTP and OLAP. What is the difference between them?
• The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
• Reset identity seed after deleting records in SQL Server
• How can I get all sequences in an Oracle database?

Questions with foreign-keys tag:

• Migration: Cannot add foreign key constraint
• The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
• Can a foreign key refer to a primary key in the same table?
• Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths — why?
• MySQL Error 1215: Cannot add foreign key constraint
• MySQL Cannot Add Foreign Key Constraint
• Delete rows with foreign key in PostgreSQL
• How to remove constraints from my MySQL table?
• MySQL — Cannot add or update a child row: a foreign key constraint fails
• How to remove foreign key constraint in sql server?
• Is it fine to have foreign key as primary key?
• Oracle (ORA-02270) : no matching unique or primary key for this column-list error
• entity object cannot be referenced by multiple instances of IEntityChangeTracker. while adding related objects to entity in Entity Framework 4.1
• Add Foreign Key to existing table
• Can a table have two foreign keys?
• Error Code: 1005. Can’t create table ‘…’ (errno: 150)
• Can a foreign key be NULL and/or duplicate?
• Foreign key constraints: When to use ON UPDATE and ON DELETE
• Foreign keys in mongo?
• How to truncate a foreign key constrained table?
• SQlite — Android — Foreign key syntax
• How to insert values in table with foreign key using MySQL?
• Add Foreign Key relationship between two Databases
• How to establish ssh key pair when «Host key verification failed»
• How to select rows with no matching entry in another table?
• Show constraints on tables command
• How can I INSERT data into two tables simultaneously in SQL Server?
• MySQL DROP all tables, ignoring foreign keys
• Meaning of «n:m» and «1:n» in database design
• How to change the foreign key referential action? (behavior)
• The property ‘Id’ is part of the object’s key information and cannot be modified
• INSERT statement conflicted with the FOREIGN KEY constraint — SQL Server
• MySQL foreign key constraints, cascade delete
• MySQL «ERROR 1005 (HY000): Can’t create table ‘foo.#sql-12c_4’ (errno: 150)»
• How to update primary key
• Can table columns with a Foreign Key be NULL?
• Force drop mysql bypassing foreign key constraint
• How do I add a foreign key to an existing SQLite table?
• SQL DROP TABLE foreign key constraint
• When to use «ON UPDATE CASCADE»
• MySQL Creating tables with Foreign Keys giving errno: 150
• How to add composite primary key to table
• Mysql error 1452 — Cannot add or update a child row: a foreign key constraint fails
• How can I find which tables reference a given table in Oracle SQL Developer?
• Postgres and Indexes on Foreign Keys and Primary Keys
• How to find foreign key dependencies in SQL Server?
• MySQL Removing Some Foreign keys
• How to find all tables that have foreign keys that reference particular table.column and have values for those foreign keys?
• Ruby on Rails. How do I use the Active Record .build method in a :belongs to relationship?
• Foreign key referring to primary keys across multiple tables?

Источник

Step 1: Identify the Foreign Key Constraint

Step 2: Disable the Foreign Key Constraint

Step 3: Execute the ALTER TABLE Statement

FAQ

Q1. Can I disable all foreign key constraints in my database?

Q2. Can I use the same name for a foreign key constraint in different tables?

Q3. Can I disable a foreign key constraint permanently?

Q4. Can I re-enable a foreign key constraint without checking it?

Q5. What is referential integrity?

Conclusion

Question

Answers

The answer is

Questions with sql tag:

Questions with sql-server tag:

Questions with database tag:

Questions with foreign-keys tag:

Это тоже интересно: