I have two databases, one is the original schema, and the other one is the same schema with all lowercased table and columns names (actuially: whole file):
-- https://github.com/lerocha/chinook-database/wiki/Chinook-Schema
/*******************************************************************************
Chinook Database - Version 1.4
Script: Chinook_PostgreSql.sql
Description: Creates and populates the Chinook database.
DB Server: PostgreSql
Author: Luis Rocha
License: http://www.codeplex.com/ChinookDatabase/license
********************************************************************************/
/*******************************************************************************
Create Tables
********************************************************************************/
CREATE TABLE "Album"
(
"AlbumId" INT NOT NULL,
"Title" VARCHAR(160) NOT NULL,
"ArtistId" INT NOT NULL,
CONSTRAINT "PK_Album" PRIMARY KEY ("AlbumId")
);
CREATE TABLE "Artist"
(
"ArtistId" INT NOT NULL,
"Name" VARCHAR(120),
CONSTRAINT "PK_Artist" PRIMARY KEY ("ArtistId")
);
CREATE TABLE "Customer"
(
"CustomerId" INT NOT NULL,
"FirstName" VARCHAR(40) NOT NULL,
"LastName" VARCHAR(20) NOT NULL,
"Company" VARCHAR(80),
"Address" VARCHAR(70),
"City" VARCHAR(40),
"State" VARCHAR(40),
"Country" VARCHAR(40),
"PostalCode" VARCHAR(10),
"Phone" VARCHAR(24),
"Fax" VARCHAR(24),
"Email" VARCHAR(60) NOT NULL,
"SupportRepId" INT,
CONSTRAINT "PK_Customer" PRIMARY KEY ("CustomerId")
);
CREATE TABLE "Employee"
(
"EmployeeId" INT NOT NULL,
"LastName" VARCHAR(20) NOT NULL,
"FirstName" VARCHAR(20) NOT NULL,
"Title" VARCHAR(30),
"ReportsTo" INT,
"BirthDate" TIMESTAMP,
"HireDate" TIMESTAMP,
"Address" VARCHAR(70),
"City" VARCHAR(40),
"State" VARCHAR(40),
"Country" VARCHAR(40),
"PostalCode" VARCHAR(10),
"Phone" VARCHAR(24),
"Fax" VARCHAR(24),
"Email" VARCHAR(60),
CONSTRAINT "PK_Employee" PRIMARY KEY ("EmployeeId")
);
CREATE TABLE "Genre"
(
"GenreId" INT NOT NULL,
"Name" VARCHAR(120),
CONSTRAINT "PK_Genre" PRIMARY KEY ("GenreId")
);
CREATE TABLE "Invoice"
(
"InvoiceId" INT NOT NULL,
"CustomerId" INT NOT NULL,
"InvoiceDate" TIMESTAMP NOT NULL,
"BillingAddress" VARCHAR(70),
"BillingCity" VARCHAR(40),
"BillingState" VARCHAR(40),
"BillingCountry" VARCHAR(40),
"BillingPostalCode" VARCHAR(10),
"Total" NUMERIC(10,2) NOT NULL,
CONSTRAINT "PK_Invoice" PRIMARY KEY ("InvoiceId")
);
CREATE TABLE "InvoiceLine"
(
"InvoiceLineId" INT NOT NULL,
"InvoiceId" INT NOT NULL,
"TrackId" INT NOT NULL,
"UnitPrice" NUMERIC(10,2) NOT NULL,
"Quantity" INT NOT NULL,
CONSTRAINT "PK_InvoiceLine" PRIMARY KEY ("InvoiceLineId")
);
CREATE TABLE "MediaType"
(
"MediaTypeId" INT NOT NULL,
"Name" VARCHAR(120),
CONSTRAINT "PK_MediaType" PRIMARY KEY ("MediaTypeId")
);
CREATE TABLE "Playlist"
(
"PlaylistId" INT NOT NULL,
"Name" VARCHAR(120),
CONSTRAINT "PK_Playlist" PRIMARY KEY ("PlaylistId")
);
CREATE TABLE "PlaylistTrack"
(
"PlaylistId" INT NOT NULL,
"TrackId" INT NOT NULL,
CONSTRAINT "PK_PlaylistTrack" PRIMARY KEY ("PlaylistId", "TrackId")
);
CREATE TABLE "Track"
(
"TrackId" INT NOT NULL,
"Name" VARCHAR(200) NOT NULL,
"AlbumId" INT,
"MediaTypeId" INT NOT NULL,
"GenreId" INT,
"Composer" VARCHAR(220),
"Milliseconds" INT NOT NULL,
"Bytes" INT,
"UnitPrice" NUMERIC(10,2) NOT NULL,
CONSTRAINT "PK_Track" PRIMARY KEY ("TrackId")
);
/*******************************************************************************
Create Primary Key Unique Indexes
********************************************************************************/
/*******************************************************************************
Create Foreign Keys
********************************************************************************/
ALTER TABLE "Album" ADD CONSTRAINT "FK_AlbumArtistId"
FOREIGN KEY ("ArtistId") REFERENCES "Artist" ("ArtistId") ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE INDEX "IFK_AlbumArtistId" ON "Album" ("ArtistId");
ALTER TABLE "Customer" ADD CONSTRAINT "FK_CustomerSupportRepId"
FOREIGN KEY ("SupportRepId") REFERENCES "Employee" ("EmployeeId") ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE INDEX "IFK_CustomerSupportRepId" ON "Customer" ("SupportRepId");
ALTER TABLE "Employee" ADD CONSTRAINT "FK_EmployeeReportsTo"
FOREIGN KEY ("ReportsTo") REFERENCES "Employee" ("EmployeeId") ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE INDEX "IFK_EmployeeReportsTo" ON "Employee" ("ReportsTo");
ALTER TABLE "Invoice" ADD CONSTRAINT "FK_InvoiceCustomerId"
FOREIGN KEY ("CustomerId") REFERENCES "Customer" ("CustomerId") ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE INDEX "IFK_InvoiceCustomerId" ON "Invoice" ("CustomerId");
ALTER TABLE "InvoiceLine" ADD CONSTRAINT "FK_InvoiceLineInvoiceId"
FOREIGN KEY ("InvoiceId") REFERENCES "Invoice" ("InvoiceId") ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE INDEX "IFK_InvoiceLineInvoiceId" ON "InvoiceLine" ("InvoiceId");
ALTER TABLE "InvoiceLine" ADD CONSTRAINT "FK_InvoiceLineTrackId"
FOREIGN KEY ("TrackId") REFERENCES "Track" ("TrackId") ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE INDEX "IFK_InvoiceLineTrackId" ON "InvoiceLine" ("TrackId");
ALTER TABLE "PlaylistTrack" ADD CONSTRAINT "FK_PlaylistTrackPlaylistId"
FOREIGN KEY ("PlaylistId") REFERENCES "Playlist" ("PlaylistId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "PlaylistTrack" ADD CONSTRAINT "FK_PlaylistTrackTrackId"
FOREIGN KEY ("TrackId") REFERENCES "Track" ("TrackId") ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE INDEX "IFK_PlaylistTrackTrackId" ON "PlaylistTrack" ("TrackId");
ALTER TABLE "Track" ADD CONSTRAINT "FK_TrackAlbumId"
FOREIGN KEY ("AlbumId") REFERENCES "Album" ("AlbumId") ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE INDEX "IFK_TrackAlbumId" ON "Track" ("AlbumId");
ALTER TABLE "Track" ADD CONSTRAINT "FK_TrackGenreId"
FOREIGN KEY ("GenreId") REFERENCES "Genre" ("GenreId") ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE INDEX "IFK_TrackGenreId" ON "Track" ("GenreId");
ALTER TABLE "Track" ADD CONSTRAINT "FK_TrackMediaTypeId"
FOREIGN KEY ("MediaTypeId") REFERENCES "MediaType" ("MediaTypeId") ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE INDEX "IFK_TrackMediaTypeId" ON "Track" ("MediaTypeId");
-- https://github.com/lerocha/chinook-database/wiki/chinook-schema
/*******************************************************************************
chinook database - version 1.4
script: chinook_postgresql.sql
description: creates and populates the chinook database.
db server: postgresql
author: luis rocha
license: http://www.codeplex.com/chinookdatabase/license
********************************************************************************/
/*******************************************************************************
create tables
********************************************************************************/
create table "album"
(
"albumid" int not null,
"title" varchar(160) not null,
"artistid" int not null,
constraint "pk_album" primary key ("albumid")
);
create table "artist"
(
"artistid" int not null,
"name" varchar(120),
constraint "pk_artist" primary key ("artistid")
);
create table "customer"
(
"customerid" int not null,
"firstname" varchar(40) not null,
"lastname" varchar(20) not null,
"company" varchar(80),
"address" varchar(70),
"city" varchar(40),
"state" varchar(40),
"country" varchar(40),
"postalcode" varchar(10),
"phone" varchar(24),
"fax" varchar(24),
"email" varchar(60) not null,
"supportrepid" int,
constraint "pk_customer" primary key ("customerid")
);
create table "employee"
(
"employeeid" int not null,
"lastname" varchar(20) not null,
"firstname" varchar(20) not null,
"title" varchar(30),
"reportsto" int,
"birthdate" timestamp,
"hiredate" timestamp,
"address" varchar(70),
"city" varchar(40),
"state" varchar(40),
"country" varchar(40),
"postalcode" varchar(10),
"phone" varchar(24),
"fax" varchar(24),
"email" varchar(60),
constraint "pk_employee" primary key ("employeeid")
);
create table "genre"
(
"genreid" int not null,
"name" varchar(120),
constraint "pk_genre" primary key ("genreid")
);
create table "invoice"
(
"invoiceid" int not null,
"customerid" int not null,
"invoicedate" timestamp not null,
"billingaddress" varchar(70),
"billingcity" varchar(40),
"billingstate" varchar(40),
"billingcountry" varchar(40),
"billingpostalcode" varchar(10),
"total" numeric(10,2) not null,
constraint "pk_invoice" primary key ("invoiceid")
);
create table "invoiceline"
(
"invoicelineid" int not null,
"invoiceid" int not null,
"trackid" int not null,
"unitprice" numeric(10,2) not null,
"quantity" int not null,
constraint "pk_invoiceline" primary key ("invoicelineid")
);
create table "mediatype"
(
"mediatypeid" int not null,
"name" varchar(120),
constraint "pk_mediatype" primary key ("mediatypeid")
);
create table "playlist"
(
"playlistid" int not null,
"name" varchar(120),
constraint "pk_playlist" primary key ("playlistid")
);
create table "playlisttrack"
(
"playlistid" int not null,
"trackid" int not null,
constraint "pk_playlisttrack" primary key ("playlistid", "trackid")
);
create table "track"
(
"trackid" int not null,
"name" varchar(200) not null,
"albumid" int,
"mediatypeid" int not null,
"genreid" int,
"composer" varchar(220),
"milliseconds" int not null,
"bytes" int,
"unitprice" numeric(10,2) not null,
constraint "pk_track" primary key ("trackid")
);
/*******************************************************************************
create primary key unique indexes
********************************************************************************/
/*******************************************************************************
create foreign keys
********************************************************************************/
alter table "album" add constraint "fk_albumartistid"
foreign key ("artistid") references "artist" ("artistid") on delete no action on update no action;
create index "ifk_albumartistid" on "album" ("artistid");
alter table "customer" add constraint "fk_customersupportrepid"
foreign key ("supportrepid") references "employee" ("employeeid") on delete no action on update no action;
create index "ifk_customersupportrepid" on "customer" ("supportrepid");
alter table "employee" add constraint "fk_employeereportsto"
foreign key ("reportsto") references "employee" ("employeeid") on delete no action on update no action;
create index "ifk_employeereportsto" on "employee" ("reportsto");
alter table "invoice" add constraint "fk_invoicecustomerid"
foreign key ("customerid") references "customer" ("customerid") on delete no action on update no action;
create index "ifk_invoicecustomerid" on "invoice" ("customerid");
alter table "invoiceline" add constraint "fk_invoicelineinvoiceid"
foreign key ("invoiceid") references "invoice" ("invoiceid") on delete no action on update no action;
create index "ifk_invoicelineinvoiceid" on "invoiceline" ("invoiceid");
alter table "invoiceline" add constraint "fk_invoicelinetrackid"
foreign key ("trackid") references "track" ("trackid") on delete no action on update no action;
create index "ifk_invoicelinetrackid" on "invoiceline" ("trackid");
alter table "playlisttrack" add constraint "fk_playlisttrackplaylistid"
foreign key ("playlistid") references "playlist" ("playlistid") on delete no action on update no action;
alter table "playlisttrack" add constraint "fk_playlisttracktrackid"
foreign key ("trackid") references "track" ("trackid") on delete no action on update no action;
create index "ifk_playlisttracktrackid" on "playlisttrack" ("trackid");
alter table "track" add constraint "fk_trackalbumid"
foreign key ("albumid") references "album" ("albumid") on delete no action on update no action;
create index "ifk_trackalbumid" on "track" ("albumid");
alter table "track" add constraint "fk_trackgenreid"
foreign key ("genreid") references "genre" ("genreid") on delete no action on update no action;
create index "ifk_trackgenreid" on "track" ("genreid");
alter table "track" add constraint "fk_trackmediatypeid"
foreign key ("mediatypeid") references "mediatype" ("mediatypeid") on delete no action on update no action;
create index "ifk_trackmediatypeid" on "track" ("mediatypeid");
For the lowercased database, everything works (except "There have been issue creating few constraints, all the constraints that failed has been saved on to file:...").
λ mock-windows-amd64-v2.2.exe database -a localhost -d chinook_normal -w prisma -f -v
time="2020-02-27 21:48:04" level=debug msg="Checking the version of the database" file="sql.go:51"
time="2020-02-27 21:48:04" level=info msg="Version of the database: PostgreSQL 11.6, compiled by Visual C++ build 1914, 64-bit" file="sql.go:66"
time="2020-02-27 21:48:04" level=debug msg="Checking if this a greenplum or postgres DB" file="sql.go:72"
time="2020-02-27 21:48:04" level=info msg="The flavour of postgres is: postgres" file="sql.go:81"
time="2020-02-27 21:48:04" level=info msg="The database that will be used by mock program is: chinook_normal" file="cmd.go:70"
time="2020-02-27 21:48:04" level=info msg="Starting the program to mock full database" file="database.go:31"
time="2020-02-27 21:48:04" level=info msg="Extracting the tables in the database: chinook_normal" file="database.go:38"
time="2020-02-27 21:48:04" level=debug msg="Extracting the tables info from the postgres database" file="sql.go:86"
time="2020-02-27 21:48:05" level=debug msg="Total number of tables to mock: 11" file="worker.go:24"
time="2020-02-27 21:48:05" level=info msg="Beginning the mocking process for the tables" file="worker.go:36"
time="2020-02-27 21:48:05" level=debug msg="Promoting for yes or no confirmation" file="helpers.go:114"
Are you sure the program mock can continue loading the fake data? FYI, For faking data to the database chinook_normal the constraints on the database will be dropped.
NOTE:
1. These constraints will be backed up & saved onto to directory
2. At the end of the program there will be an attempt to restore it, unless ignore (-i) flag is set when the restore of constraints will be ignored.
Choose (Yy/Nn): y
time="2020-02-27 21:48:06" level=info msg="Extracting the columns and datatype information" file="worker.go:59"
time="2020-02-27 21:48:06" level=debug msg="Extracting the column information from postgres database for table: public.\"Artist\"" file="sql.go:164"
Extracting column information from tables 9% [===> ] (1/11) [0s:3s]time="2020-02-27 21:48:07" level=debug msg="Extracting the column information from postgres database for table: public.\"Album\"" file="sql.go:164"
Extracting column information from tables 18% [========> ] (2/11) [0s:3s]time="2020-02-27 21:48:07" level=debug msg="Extracting the column information from postgres database for table: public.\"Employee\"" file="sql.go:164"
Extracting column information from tables 27% [============> ] (3/11) [1s:2s]time="2020-02-27 21:48:08" level=debug msg="Extracting the column information from postgres database for table: public.\"Customer\"" file="sql.go:164"
Extracting column information from tables 36% [=================> ] (4/11) [1s:2s]time="2020-02-27 21:48:08" level=debug msg="Extracting the column information from postgres database for table: public.\"Invoice\"" file="sql.go:164"
Extracting column information from tables 45% [=====================> ] (5/11) [1s:2s]time="2020-02-27 21:48:08" level=debug msg="Extracting the column information from postgres database for table: public.\"InvoiceLine\"" file="sql.go:164"
Extracting column information from tables 54% [==========================> ] (6/11) [2s:1s]time="2020-02-27 21:48:09" level=debug msg="Extracting the column information from postgres database for table: public.\"Track\"" file="sql.go:164"
Extracting column information from tables 63% [==============================> ] (7/11) [2s:1s]time="2020-02-27 21:48:09" level=debug msg="Extracting the column information from postgres database for table: public.\"Playlist\"" file="sql.go:164"
Extracting column information from tables 72% [===================================> ] (8/11) [2s:1s]time="2020-02-27 21:48:09" level=debug msg="Extracting the column information from postgres database for table: public.\"PlaylistTrack\"" file="sql.go:164"
Extracting column information from tables 81% [=======================================> ] (9/11) [3s:0s]time="2020-02-27 21:48:10" level=debug msg="Extracting the column information from postgres database for table: public.\"Genre\"" file="sql.go:164"
Extracting column information from tables 90% [============================================> ] (10/11) [3s:0s]time="2020-02-27 21:48:10" level=debug msg="Extracting the column information from postgres database for table: public.\"MediaType\"" file="sql.go:164"
Extracting column information from tables 100% [==================================================] (11/11) [4s:0s]
time="2020-02-27 21:48:10" level=info msg="Saving all the backup files to the path: C:\\Users\\Jan/mock/20200227214804" file="constraintsBackup.go:21"
time="2020-02-27 21:48:10" level=debug msg="Backing up all the constraints from the database: chinook_normal" file="constraintsBackup.go:31"
time="2020-02-27 21:48:10" level=debug msg="Extracting the DDL of the p constraints" file="sql.go:244"
time="2020-02-27 21:48:11" level=debug msg="Extracting the DDL of the f constraints" file="sql.go:244"
time="2020-02-27 21:48:11" level=debug msg="Extracting the DDL of the u constraints" file="sql.go:244"
time="2020-02-27 21:48:12" level=debug msg="Extracting the DDL of the c constraints" file="sql.go:244"
time="2020-02-27 21:48:12" level=debug msg="Backing up all the unique indexes from the database: chinook_normal" file="constraintsBackup.go:53"
time="2020-02-27 21:48:12" level=debug msg="Extracting the unique indexes" file="sql.go:277"
time="2020-02-27 21:48:12" level=info msg="Total numbers of tables to mock: 11" file="worker.go:99"
time="2020-02-27 21:48:12" level=debug msg="Removing constraints for table: \"public\".\"Artist\"" file="constraintsBackup.go:96"
time="2020-02-27 21:48:12" level=debug msg="Extracting constraint info for table: \"public\".\"Artist\"" file="sql.go:310"
Mocking Table "public"."Artist" 100% [==================================================] (10/10) [0s:0s]
time="2020-02-27 21:48:14" level=debug msg="Removing constraints for table: \"public\".\"Album\"" file="constraintsBackup.go:96"
time="2020-02-27 21:48:14" level=debug msg="Extracting constraint info for table: \"public\".\"Album\"" file="sql.go:310"
time="2020-02-27 21:48:16" level=debug msg="Table: \"public\".\"Album\"" file="worker.go:168"
time="2020-02-27 21:48:16" level=debug msg="Copy Statement: COPY \"public\".\"Album\"(\"AlbumId\",\"Title\",\"ArtistId\") FROM STDIN WITH CSV DELIMITER '$' QUOTE e'\\x01'" file="worker.go:169"
time="2020-02-27 21:48:16" level=debug msg="Data: 6305390$DDuk71dr5KCg2aEbgVrWOlGHPHQ4PDJHiIXoZHySVucaxWWLvfn45M433k0Y6nVkndTRcWaMTL3BgXG6jS0WUfTEUvCMEHPKDGkx9NYUx607LcwG9jeB8PAi1LJCksYLPCmm2JqVTxuuYnOihay729iIcbBRn17w$1054861" file="worker.go:170"
time="2020-02-27 21:48:16" level=fatal msg="Error during committing data: ERROR #23503 insert or update on table \"Album\" violates foreign key constraint \"FK_AlbumArtistId\"" file="worker.go:171"
![github-actions[bot] avatar](https://avatars.githubusercontent.com/in/15368?v=4 "github-actions[bot]")
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent