28 lines
1.9 KiB
MySQL
28 lines
1.9 KiB
MySQL
|
CREATE TABLE "accounts" (
|
||
|
|
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "accounts_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
|
||
|
|
"owner_id" integer NOT NULL,
|
||
|
|
"name" text NOT NULL,
|
||
|
|
"currency" text NOT NULL,
|
||
|
|
"starting_date" timestamp with time zone NOT NULL,
|
||
|
|
"starting_balance" numeric DEFAULT '0' NOT NULL
|
||
|
|
);
|
||
|
|
--> statement-breakpoint
|
||
|
|
CREATE TABLE "receipt_items" (
|
||
|
|
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "receipt_items_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
|
||
|
|
"receipt_id" integer NOT NULL,
|
||
|
|
"amount" numeric DEFAULT '0' NOT NULL,
|
||
|
|
"category" text NOT NULL
|
||
|
|
);
|
||
|
|
--> statement-breakpoint
|
||
|
|
CREATE TABLE "receipts" (
|
||
|
|
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "receipts_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
|
||
|
|
"date" timestamp with time zone NOT NULL,
|
||
|
|
"account_from_id" integer NOT NULL,
|
||
|
|
"account_to_id" integer,
|
||
|
|
CONSTRAINT "no_account_loops" CHECK ("receipts"."account_from_id" <> "receipts"."account_to_id")
|
||
|
|
);
|
||
|
|
--> statement-breakpoint
|
||
|
|
ALTER TABLE "accounts" ADD CONSTRAINT "accounts_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "receipt_items" ADD CONSTRAINT "receipt_items_receipt_id_receipts_id_fk" FOREIGN KEY ("receipt_id") REFERENCES "public"."receipts"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "receipts" ADD CONSTRAINT "receipts_account_from_id_accounts_id_fk" FOREIGN KEY ("account_from_id") REFERENCES "public"."accounts"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "receipts" ADD CONSTRAINT "receipts_account_to_id_accounts_id_fk" FOREIGN KEY ("account_to_id") REFERENCES "public"."accounts"("id") ON DELETE no action ON UPDATE no action;
|