SQL schema to create some together-referenced entities

references.sql

create table Type (
  TypeCode CHAR(1) NOT NULL,
  Name CHAR(30) NOT NULL,
  CONSTRAINT Type_PK PRIMARY KEY (TypeCode),
  CONSTRAINT Type_AK UNIQUE (Name)
);

create table Items (
  ItemId int not null,
  TypeCode CHAR(1) not null,
  Name char(30) not null,
  CreatedDateTime datetime not null,
  
  constraint Item_PK primary key (ItemId),
  constraint Item_to_Type_FK foreign key (TypeCode) references Type (TypeCode)
);

create table Story (
  StoryId int not null,
  StoryDateTime datetime not null,
  Content text not null,
  
  constraint Story_PK primary key (StoryId),
  constraint Story_to_Item_FK foreign key (StoryId) references Items (ItemId)
);

create table Files (
  FileId int not null,
  Filename char(60) not null,
  Description text,
  
  constraint File_PK primary key (FileId),
  constraint File_to_Item_FK foreign key (FileId) references Items (ItemId)
);

create table Reference (
  ReferenceId int not null,
  SourceId int not null,
  TargetId int not null,
  
  constraint Ref_PK primary key (ReferenceId),
  constraint Ref_AK unique (SourceId, TargetId),
  constraint Source_to_Item_FK foreign key (SourceId) references Items (ItemId),
  constraint Target_to_Item_FK foreign key (TargetId) references Items (ItemId)
);

insert into Type values ('S', 'Story'), ('F', 'File');

insert into Items values (1, 'S', 'Some story', CURDATE()),
                          (2, 'F', 'Some File', CURDATE()),
                           (3, 'F', 'Other file', CURDATE());
insert into Story values (1, CURDATE(), 'Some story content');
insert into Files values (2, 'file.tmp', 'Some file description');
insert into Files values (3, 'file2.tmp', 'Other temp desc');
                           
insert into Reference values (1, 1, 2), (2, 3, 1);
                          
create view StoryView as select
                            I.ItemId AS Id,
                            I.Name,
                            I.CreatedDateTime,
                            S.StoryDateTime,
                            S.Content
                            from Items I JOIN Story S on I.ItemId = S.StoryId;
                            
create view FileView as select
                            I.ItemId AS Id,
                            I.Name,
                            I.CreatedDateTime,
                            F.Filename,
                            F.Description
                            from Items I JOIN Files F on I.ItemId = F.FileId;
                            
create view ReferenceView as select
                            I.ItemId as Id,
                            IT.Name as Type,
                            I.Name,
                            S.StoryDateTime,
                            F.Filename,
                            F.Description as FileDesc
                            from Items I
                            join Type IT on I.TypeCode = IT.TypeCode
                            left join Story S on S.StoryId = I.ItemId
                            left join Files F on F.FileId = I.ItemId;
                            
create view ReferencedIn as select V.*, R.ReferenceId, R.TargetId from ReferenceView V
                            join Reference R on R.SourceId = V.Id;
                            
create view ReferencesTo as select V.*, R.ReferenceId, R.SourceId from ReferenceView V
                            join Reference R on R.TargetId = V.Id;