SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
	Oct  8 2022 05:58:25 
	Copyright (C) 2022 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE personnel(
	personnel INT IDENTITY(1, 1) NOT NULL,
	lastName VARCHAR(20),
	login VARCHAR(20) DEFAULT NULL,
	firstName VARCHAR(20), 
	title varchar(20) DEFAULT NULL,
	initials varchar(4) NOT NULL,
	startDate DATE DEFAULT GETDATE(),
	CONSTRAINT PkPersonnel PRIMARY KEY(personnel),
	CONSTRAINT UqPersonnelFirstNameLastName UNIQUE(firstName, lastName),
	CONSTRAINT UqPersonnelInitials UNIQUE(initials)
);

CREATE TABLE authType(
authType INT NOT NULL IDENTITY(1, 1),
authName varchar(50)
CONSTRAINT PkAuthTypeAuthType PRIMARY KEY(authType)
);

CREATE TABLE auth(
auth INT NOT NULL IDENTITY(1, 1),
personnel INT NOT NULL,
authtype INT NOT NULL,
date date DEFAULT GETDATE(),
CONSTRAINT PkAuthAuth PRIMARY KEY(auth),
  
-- UNIQUE constraint added
CONSTRAINT person_authtype 
    UNIQUE (personnel, authtype),
  
CONSTRAINT FkAuthAuthType FOREIGN KEY(authtype) REFERENCES authtype(authtype)
);


CREATE TABLE report(
	report INTEGER NOT NULL IDENTITY(1, 1),
	iteminJob INTEGER NOT NULL,
	reportDate DATE NOT NULL,
	notes VARCHAR(200),
	signedBy INTEGER NOT NULL,
	reviewedBy INTEGER,
	conformance VARCHAR(30)
	CONSTRAINT PkReport PRIMARY KEY(report),
	-- CONSTRAINT [Reports must be associated with an item in a job.] 
    --    FOREIGN KEY(iteminJob) REFERENCES iteminjob(iteminJob),
  
    -- column added
    authorized_to_sign_authtype AS CAST(8 AS INT) PERSISTED,
    -- FOREIGN KEY added
    CONSTRAINT must_be_signed_by_an_authorized_signatory 
        FOREIGN KEY (signedBy, authorized_to_sign_authtype)
            REFERENCES auth (personnel, authtype)
);