I’m kind of new to scripting in SQL and I have encountered an error in one of my scripts.
The problematic section is:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE dbo.WorkspaceSettings
(
Id INT NOT NULL IDENTITY PRIMARY KEY ,
ReportColorRGB1 VARCHAR(15) NOT NULL DEFAULT '61,105,138' ,
ReportColorRGB2 VARCHAR(15) NOT NULL DEFAULT '180,210,121'
)
GO
ALTER TABLE Workspace ADD WorkspaceSettingsId int NOT NULL default 1;
GO
ALTER TABLE Workspace
ADD CONSTRAINT FK_WorkspaceSettings_Workspace
FOREIGN KEY (WorkspaceSettingsId)
REFERENCES WorkspaceSettings(Id);
GO
And receive the following error message:
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint «FK_WorkspaceSettings_Workspace». The conflict occurred in database «ClearRisk2_0», table «dbo.WorkspaceSettings», column ‘Id’.
Can someone please tell me where I’m going wrong?
I’m kind of new to scripting in SQL and I have encountered an error in one of my scripts.
The problematic section is:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE dbo.WorkspaceSettings
(
Id INT NOT NULL IDENTITY PRIMARY KEY ,
ReportColorRGB1 VARCHAR(15) NOT NULL DEFAULT '61,105,138' ,
ReportColorRGB2 VARCHAR(15) NOT NULL DEFAULT '180,210,121'
)
GO
ALTER TABLE Workspace ADD WorkspaceSettingsId int NOT NULL default 1;
GO
ALTER TABLE Workspace
ADD CONSTRAINT FK_WorkspaceSettings_Workspace
FOREIGN KEY (WorkspaceSettingsId)
REFERENCES WorkspaceSettings(Id);
GO
And receive the following error message:
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint «FK_WorkspaceSettings_Workspace». The conflict occurred in database «ClearRisk2_0», table «dbo.WorkspaceSettings», column ‘Id’.
Can someone please tell me where I’m going wrong?
0 / 0 / 3 Регистрация: 08.10.2012 Сообщений: 141 |
|
1 |
|
25.09.2014, 22:34. Показов 50842. Ответов 5
пытаюсь сделать связь таблиц Миниатюры
0 |
Я Фоюмчанин ? дааааа) 213 / 204 / 23 Регистрация: 02.06.2012 Сообщений: 1,424 Записей в блоге: 12 |
|
25.09.2014, 22:44 |
2 |
, а в таблице Квартиры столбец КодСотрудника А там и нет такого столбца…
0 |
0 / 0 / 3 Регистрация: 08.10.2012 Сообщений: 141 |
|
25.09.2014, 23:22 [ТС] |
3 |
тьфу, не из «Квартиры», а из Операции запутался. На 2-ом скрине связи видны. КодСотрудника с Кодом сотрудника (табл. Операция-Сотрудник) и id и КодКвартиры (табл Квартиры и Операции) Добавлено через 9 минут
0 |
63 / 63 / 21 Регистрация: 08.02.2013 Сообщений: 262 |
|
26.09.2014, 09:33 |
4 |
Решение вероятно, есть данные в таблице Операция со значением в поле КодСотрудника, которого нет в таблице Сотрудник в соответствующем поле.
6 |
0 / 0 / 3 Регистрация: 08.10.2012 Сообщений: 141 |
|
26.09.2014, 10:22 [ТС] |
5 |
nixon93nixon, вечером проверю, спасибо
0 |
0 / 0 / 3 Регистрация: 08.10.2012 Сообщений: 141 |
|
27.09.2014, 22:37 [ТС] |
6 |
nixon93nixon, да, в этом была ошибка, спасибо.
0 |
- Remove From My Forums
-
Question
-
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint «fk_Allergy». The conflict occurred in database «MSCPROJECT», table «dbo.Severity», column ‘SeverityCode’.
Answers
-
Try to not verifying existing Data when you create the FK, it solve the problem.
-
You did not ask a question. But, I suppose you want to know what this error means. It means that you are trying to alter the table in a way that violates referential integrity. If you really need to change the table in a way that does that, you will need to drop the foreign key relationship before you alter the table.
What the database is doing here is enforcing referential integrity so that you don’t get orphaned records as a result of your alteration. So, be careful and make sure the alteration is something that must occur and make sure to evaluate how the related tables and the data in them will be impacted by your alteration.
- Remove From My Forums
-
Question
-
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint «fk_Allergy». The conflict occurred in database «MSCPROJECT», table «dbo.Severity», column ‘SeverityCode’.
Answers
-
Try to not verifying existing Data when you create the FK, it solve the problem.
-
You did not ask a question. But, I suppose you want to know what this error means. It means that you are trying to alter the table in a way that violates referential integrity. If you really need to change the table in a way that does that, you will need to drop the foreign key relationship before you alter the table.
What the database is doing here is enforcing referential integrity so that you don’t get orphaned records as a result of your alteration. So, be careful and make sure the alteration is something that must occur and make sure to evaluate how the related tables and the data in them will be impacted by your alteration.