Блог

Я пишу сценарий sql server для создания нескольких таблиц, а затем изменяю таблицы, чтобы добавить ограничения внешнего ключа, которые я считаю необходимыми.

Я пытаюсь обернуть этот сценарий в транзакцию, чтобы убедиться, что либо ничего не будет сделано, либо все будет сделано. Проблема, с которой я сталкиваюсь, заключается в том, что сценарий, похоже, выдает мне ошибку при попытке добавить ограничения внешнего ключа в ту же таблицу.

То, что я пробовал до сих пор:

 ALTER TABLE UoMConversion
ADD CONSTRAINT FK__UoMConversion__UnitOfMEasure__UoMFrom FOREIGN KEY(UoMFrom)
    REFERENCES UnitOfMeasure(UoMID)
    ON DELETE CASCADE;
ALTER TABLE UoMConversion
ADD CONSTRAINT FK__UoMConversion__UnitOfMEasure__UoMTo FOREIGN KEY(UoMTo)
    REFERENCES UnitOfMeasure(UoMID)
    ON DELETE CASCADE;
 

или

 ALTER TABLE UoMConversion
ADD CONSTRAINT FK__UoMConversion__UnitOfMEasure__UoMFrom FOREIGN KEY(UoMFrom)
    REFERENCES UnitOfMeasure(UoMID)
    ON DELETE CASCADE,
    CONSTRAINT FK__UoMConversion__UnitOfMEasure__UoMTo FOREIGN KEY(UoMTo)
    REFERENCES UnitOfMeasure(UoMID)
    ON DELETE CASCADE;
 

И то, и другое, я надеюсь, сработает. Тем не менее, сценарий и DDL завернуты в транзакцию с попыткой поймать.

     BEGIN TRANSACTION CreateTables 
    BEGIN TRY
    USE ProductDB
    
        CREATE TABLE UnitOfMeasure(
            UoMID int not null identity(1,1) primary key,
            UoMDescription varchar(255) not null,
            UoMAbbreviation varchar(10) not null,
            UoMCategoryID int   -- FK__UnitOfMeasure__UnitOfMeasureCategory
        );
        
        
        CREATE TABLE UnitOfMeasureCategory(
            UoMCategoryID int not null identity(1,1) primary key,
            UoMCategory varchar(100) not null
        );
        
        CREATE TABLE UoMConversion (
            UoMConversionID int not null identity(1,1) primary key,
            UoMFrom int not null,   -- FK__UoMConversion__UnitOfMEasure__UoMFrom
            UoMTo int not null,     -- FK__UoMConversion__UnitOfMeasure__UoMTo
            Factor decimal(5),
            UoMCategoryID int       -- FK__UoMConversion__UnitOfMeasureCategory
        );
        ALTER TABLE UoMConversion
          ADD CONSTRAINT FK__UoMConversion__UnitOfMEasure__UoMFrom FOREIGN KEY(UoMFrom)
        REFERENCES UnitOfMeasure(UoMID)
        ON DELETE CASCADE;
        ALTER TABLE UoMConversion
           ADD CONSTRAINT FK__UoMConversion__UnitOfMEasure__UoMTo FOREIGN KEY(UoMTo)
            REFERENCES UnitOfMeasure(UoMID)
            ON DELETE CASCADE;
END TRY
 
BEGIN CATCH  
    SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
  
 IF @@ERROR > 0  
    ROLLBACK TRANSACTION;  
 END CATCH;  
  
 SELECT @@TRANCOUNT AS OpenTransactions


 COMMIT TRANSACTION;  
 

Также, когда я удаляю один из двух операторов внешнего ключа, ошибка исчезает. Важно отметить, что таблицы не существуют в базе данных. Поэтому при запуске этого скрипта в базе данных нет существующих таблиц, связей и т. Д. И т. Д.

Ошибка, которую я получаю, такова:

Это означает, что эта ошибка возникает при добавлении ограничения ПЕРВИЧНОГО КЛЮЧА в таблицу, в которой столбец, назначенный в качестве ПЕРВИЧНОГО КЛЮЧА, определен как ОБНУЛЯЕМЫЙ. Это не так, потому что:

Поля таблицы UoMFrom и UoMTo созданы со свойством not null, как вы увидите в приведенном выше сценарии.

Есть ли возможность создать несколько ограничений внешнего ключа в транзакции без возникновения этой ошибки?

Любая помощь или предложения будут очень признательны! Заранее спасибо.

Используемое программное обеспечение:

 Microsoft SQL Server Management Studio                      14.0.17289.0
Microsoft Analysis Services Client Tools                    14.0.1016.283
Microsoft Data Access Components (MDAC)                     10.0.19041.1
Microsoft MSXML                                             3.0 6.0 
Microsoft Internet Explorer                                 9.11.19041.0
Microsoft .NET Framework                                    4.0.30319.42000
Operating System                                            6.3.19043
 

Здесь нет необходимости использовать TRY/CATCH. Просто установите XACT_ABORT включенным и запустите пакет в транзакции.

 USE ProductDB

SET XACT_ABORT ON 
BEGIN TRANSACTION 
    
CREATE TABLE UnitOfMeasure(
    UoMID int not null identity(1,1) primary key,
    UoMDescription varchar(255) not null,
    UoMAbbreviation varchar(10) not null,
    UoMCategoryID int   -- FK__UnitOfMeasure__UnitOfMeasureCategory
);
        
        
CREATE TABLE UnitOfMeasureCategory(
    UoMCategoryID int not null identity(1,1) primary key,
    UoMCategory varchar(100) not null
);
        
CREATE TABLE UoMConversion (
    UoMConversionID int not null identity(1,1) primary key,
    UoMFrom int not null,   -- FK__UoMConversion__UnitOfMEasure__UoMFrom
    UoMTo int not null,     -- FK__UoMConversion__UnitOfMeasure__UoMTo
    Factor decimal(5),
    UoMCategoryID int       -- FK__UoMConversion__UnitOfMeasureCategory
);
ALTER TABLE UoMConversion
    ADD CONSTRAINT FK__UoMConversion__UnitOfMEasure__UoMFrom FOREIGN KEY(UoMFrom)
REFERENCES UnitOfMeasure(UoMID) ON DELETE CASCADE;

ALTER TABLE UoMConversion
    ADD CONSTRAINT FK__UoMConversion__UnitOfMEasure__UoMTo FOREIGN KEY(UoMTo)
    REFERENCES UnitOfMeasure(UoMID) ON DELETE CASCADE;

COMMIT TRANSACTION;  
 

Вы увидите все возвращенные сообщения об ошибках:

 Msg 1785, Level 16, State 0, Line 30
Introducing FOREIGN KEY constraint 'FK__UoMConversion__UnitOfMEasure__UoMTo' on table 'UoMConversion' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 1, Line 30
Could not create constraint or index. See previous errors.
 

Которые вы решаете, удаляя ON DELETE CASCADE по крайней мере из одного из ваших внешних ключей.