Sql Server adding multiple foreign key constraints to a table within a transaction

Question

I am writing a sql server script to create a few tables and then alter the tables to add Foreign Key Constraints that I deem necessary.

I am trying to wrap this script into a transaction to make sure that either nothing gets done, or everything gets done. The problem I am experiencing is that the script seems to give me an error when trying to add to foreign key constraints within the same table.

What I have tried up until now:

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;

OR

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;

Both of which I would hope would work. However, the script and DDL is wrapped into a transaction with a Try and Catch.

    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;  

Also when I remove one of the two foreign Key statements, the error disappears. Important to note is that the tables do not exist in the database. So When running this script there are no existing tables or relationships etc etc in the database.

The error I get is:

It implies that the this error is encountered when adding a PRIMARY KEY constraint on a table where the column being designated as the PRIMARY KEY is defined as NULLABLE. This is not the case because:

the table Fields UoMFrom and UoMTo are both created with the not null property as you will see in the above script.

Is there anyway of creating multiple foreign key constraints within a transaction without getting this error?

Any help or sugestions would be greatly appreciated! Thanks in advance.

Software used:

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

Answer 1

There's no need to use TRY/CATCH here. Just set XACT_ABORT on and run the batch in a transaction.

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;  

You'll see all the error messages returned:

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.

Which you resolve by removing the ON DELETE CASCADE from at least one of your Foreign Keys.