By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[tCat](
[CatID] [int] IDENTITY(1,1) NOT NULL,
[Cat] [nchar](100) NOT NULL,
[CatBreedID] [int] NULL,
[Fluffiness] [float] NOT NULL,
CONSTRAINT [PK_tCat] PRIMARY KEY CLUSTERED
(
[CatID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-- batch abort
SET XACT_ABORT ON;
BEGIN TRAN
INSERT INTO tCat(Cat, Fluffiness) VALUES('Sir Pounce A Lot', 8.0)
INSERT INTO tCat(Cat) VALUES('Violet')
COMMIT
Msg 515 Level 16 State 2 Line 5
Cannot insert the value NULL into column 'Fluffiness', table 'fiddle_920b1041f5944f16bc8a2ce954835490.dbo.tCat'; column does not allow nulls. INSERT fails.
SELECT @@TRANCOUNT
| (No column name) |
|---|
| 0 |
SELECT * FROM dbo.tCat
| CatID | Cat | CatBreedID | Fluffiness |
|---|
-- statement abort
SET XACT_ABORT OFF;
BEGIN TRAN
INSERT INTO tCat(Cat, Fluffiness) VALUES('Sir Pounce A Lot', 8.0)
INSERT INTO tCat(Cat) VALUES('Violet')
COMMIT
Msg 515 Level 16 State 2 Line 5
Cannot insert the value NULL into column 'Fluffiness', table 'fiddle_920b1041f5944f16bc8a2ce954835490.dbo.tCat'; column does not allow nulls. INSERT fails.
Msg 3621 Level 0 State 0 Line 5
The statement has been terminated.
SELECT @@TRANCOUNT
| (No column name) |
|---|
| 0 |
SELECT * FROM dbo.tCat
| CatID | Cat | CatBreedID | Fluffiness |
|---|---|---|---|
| 3 | Sir Pounce A Lot | null | 8 |