Changes between Version 2 and Version 3 of ddlScript.sql

Timestamp:

12/16/24 01:21:27 (4 weeks ago)

Author:

211101

Comment:

--

ddlScript.sql

@@ -1 @@
-
 {{{#!sql
 -- Delete tables if they exist
@@ -25 +24 @@
 );
 
+-- Create TRANSACTION table
+CREATE TABLE transaction (
+    transaction_id SERIAL PRIMARY KEY,
+    transaction_name VARCHAR(100),
+    amount DECIMAL(10, 2) NOT NULL,
+    net_amount DECIMAL(10, 2),
+    date TIMESTAMPTZ NOT NULL
+);
+
 -- Create TAG table
 CREATE TABLE tag (
@@ -31 +39 @@
 );
 
--- Create TRANSACTION table
-CREATE TABLE transaction (
-    transaction_id SERIAL PRIMARY KEY,
-    transaction_name VARCHAR(100),
-    amount DECIMAL(10, 2) NOT NULL,
-    net_amount DECIMAL(10, 2),
-    date TIMESTAMPTZ,
-    tag_id INT REFERENCES tag(tag_id)
+-- Create TAG_ASSIGNED_TO_TRANSACTION table
+CREATE TABLE tag_assigned_to_transaction (
+    tag_assigned_to_transaction_id SERIAL PRIMARY KEY,
+    transaction_id INT NOT NULL REFERENCES transaction(transaction_id) ON DELETE CASCADE,
+    tag_id INT NOT NULL REFERENCES tag(tag_id) ON DELETE CASCADE
 );
 
@@ -49 +54 @@
     earned_amount DECIMAL(10, 2)
 );
-
--- Create TAG_ASSIGNED_TO_TRANSACTION table
-CREATE TABLE tag_assigned_to_transaction (
-    tag_assigned_to_transaction_id SERIAL PRIMARY KEY,
-    tag_id INT REFERENCES tag(tag_id),
-    transaction_id INT REFERENCES transaction(transaction_id)
-);
 }}}