table_1:
id Serial
name Varchar
date_range DATERANGE

table_2:
id Serial
name Varchar
date_range DATERANGE
table_1_id - Foreign Key, this references the id of a record in table 1.

USE tempdb;
GO

SELECT * 
INTO MY_MESSAGES
FROM sys.messages;

CREATE CLUSTERED INDEX IX_MY_MESSAGES ON dbo.MY_MESSAGES(message_id);

SET STATISTICS IO ON;
ALTER TABLE dbo.MY_MESSAGES
	ADD CONSTRAINT CK_mymessages CHECK (message_id < 50000);
SET STATISTICS IO OFF;

DROP TABLE IF EXISTS dbo.MY_MESSAGES;

Table 'MY_MESSAGES'. Scan count 1, logical reads 10955

select title, tree_path from my_table where tree_path <@ '3';

title    | tree_path
-----------------------
item A   | 3
item B   | 3.1.1
item C   | 3.1.2
item D   | 3.1.3

create table factory (
  id serial primary key,
  detail text not null
);

create table process (
  id serial primary key,
  detail text not null
);

create table item (
  id serial primary key,
  detail text not null
);

create table factory_item_process (
  factory_id integer not null references factory(id),
  process_id integer not null references process(id),
  item_id integer not null references item(id),
  constraint pk_factory_item_process primary key (
    factory_id, process_id, item_id
  ),
  constraint uq_factory_item_process_process unique (process_id)
);

create function in_other_factories(
  factory_id integer, 
  item_id integer
) returns boolean
language sql returns null on null input
  return true in (
    select 
      true 
    from factory_item_process
    where factory_id  $1
      and item_id = $2
  );
alter table factory_item_process 
  add constraint chk_factory_item check(not in_other_factories(factory_id, item_id));

create or replace function insert_step_status_value() returns trigger as $$
	begin
		if (new.step_status != 'COMPLETE') or (new.step_status != 'INCOMPLETE')
		from phase_steps where step_type = 'RESEARCH'
		then
			raise exception 'Status value not in range for this phase step';
		end if;
		return new;
	end;
$$ language plpgsql;

create trigger check_step_status_value before update on phase_steps
for each row execute procedure insert_step_status_value();

DispatchedOn is a DateTime column

ALTER TABLE [table] WITH CHECK ADD CONSTRAINT [CK_ReportDate6MonthRollGreater] CHECK  ((CONVERT([date],[DispatchedOn])>='2022-12-07'))
GO

ALTER TABLE [table] CHECK CONSTRAINT [CK_ReportDate6MonthRollGreater]
GO

-- laravel.spies definition

CREATE TABLE spies (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  surname varchar(255) NOT NULL,
  agency varchar(255) NOT NULL DEFAULT 'NO-AGENCY',
  country_of_operation varchar(255) DEFAULT NULL,
  birth_date date NOT NULL,
  death_date date DEFAULT NULL,
  created_at timestamp NULL DEFAULT NULL,
  updated_at timestamp NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY unique_spy (name,surname,agency,birth_date),
) ENGINE=InnoDB AUTO_INCREMENT=2
       DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE spies add constraint
         death_date_valid check ( spies.death_date = NULL
           || spies.death_date > spies.birth_date );

INSERT INTO spies (name,surname,birth_date,death_date)
      VALUES ('Namae','Myoji','1980-12-01','1970-12-03');

CREATE TABLE personnel(
	personnel INT IDENTITY(1, 1) NOT NULL,
	firstName VARCHAR(20), 
	lastName VARCHAR(20),
	login VARCHAR(20) DEFAULT NULL,
	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)
CONSTRAINT FkAuthAuthType FOREIGN KEY(authtype) REFERENCES authtype(authtype)
);
CREATE FUNCTION checkIfAuthorized (@personnel INTEGER, @authType INTEGER)
RETURNS Bit
AS
BEGIN
    Return Case 
        When Exists (Select 1 FROM auth
                     Where personnel = @personnel AND authtype = @authtype)
            Then 1 Else 0
        End
END
GO
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),
	CONSTRAINT [Must be signed by an authorized signatory.] CHECK(metrologyTesting.dbo.checkIfAuthorized(signedBy, 8))
);