27 lines
1.2 KiB
SQL
27 lines
1.2 KiB
SQL
-- SQL script to create the necessary tables in your PostgreSQL database
|
|
|
|
-- Users table to store login information
|
|
CREATE TABLE users (
|
|
id SERIAL PRIMARY KEY, -- Unique identifier for each user
|
|
username VARCHAR(255) UNIQUE NOT NULL, -- Unique username for login
|
|
password_hash VARCHAR(255) NOT NULL, -- Hashed password
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP -- Timestamp of user creation
|
|
);
|
|
|
|
-- Todos table to store todo items
|
|
CREATE TABLE todos (
|
|
id SERIAL PRIMARY KEY, -- Unique identifier for each todo
|
|
user_id INTEGER NOT NULL, -- Foreign key referencing the users table
|
|
task TEXT NOT NULL, -- The content of the todo item
|
|
is_completed BOOLEAN DEFAULT FALSE, -- Status of the todo item
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, -- Timestamp of todo creation
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE -- Ensure referential integrity
|
|
);
|
|
|
|
-- Optional: Add an index for faster lookups by user_id in the todos table
|
|
CREATE INDEX idx_todos_user_id ON todos(user_id);
|
|
|
|
-- Optional: Add an index for faster lookups by username in the users table
|
|
CREATE INDEX idx_users_username ON users(username);
|
|
|