CREATE TABLE [LST_FILES] (
  [file_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  [file_name] VARCHAR NOT NULL, 
  [file_path] VARCHAR NOT NULL, 
  [file_er] BOOLEAN NOT NULL DEFAULT 0);

CREATE UNIQUE INDEX [idx_file_name] ON [LST_FILES] ([file_name]);


CREATE TABLE [PARTS] (
  [part_id] INTEGER PRIMARY KEY AUTOINCREMENT, 
  [part_name] varchar NOT NULL);

CREATE UNIQUE INDEX [idx_part_name] ON [PARTS] ([part_name] ASC);


CREATE TABLE [parts_and_file_assignment] (
  [part_id] INTEGER CONSTRAINT [idx_foreign_part] REFERENCES [PARTS]([part_id]) ON DELETE CASCADE, 
  [file_id] INTEGER CONSTRAINT [idx_foreign_file] REFERENCES [LST_FILES]([file_id]) ON DELETE CASCADE);

CREATE UNIQUE INDEX [idx_part_file_accord] ON [parts_and_file_assignment] ([part_id] ASC, [file_id] ASC);