DEV Community: ARUL SELVI ML

Modifying Tables in SQL using ALTER

ARUL SELVI ML — Sat, 28 Mar 2026 17:34:56 +0000

1. Making a Column NOT NULL

Suppose we already created a customers table, but now we want to ensure that email is always provided.

ALTER TABLE customers ALTER COLUMN email SET NOT NULL;

2. Adding UNIQUE Constraint

To make sure no two users have the same username:

ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);

3. Adding CHECK Constraint

We want to ensure product price is always greater than 0:

ALTER TABLE products
ADD CONSTRAINT check_price CHECK (price > 0);

4. Setting DEFAULT Value

To make status = 'pending' by default:

ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';

5. Adding a Column with Constraints

Now we add a salary column to employees:

ALTER TABLE employees
ADD COLUMN salary DECIMAL(10,2) NOT NULL CHECK (salary > 10000);

6. Modifying Foreign Key with CASCADE

To automatically delete employees when a department is deleted:

ALTER TABLE employees
DROP CONSTRAINT employees_department_id_fkey;

ALTER TABLE employees
ADD CONSTRAINT employees_department_id_fkey
FOREIGN KEY (department_id)
REFERENCES departments(id)
ON DELETE CASCADE;

7. Dropping a CHECK Constraint

To remove the balance restriction:

ALTER TABLE accounts
DROP CONSTRAINT accounts_balance_check;

\d accounts;

8. Adding Composite UNIQUE Constraint

To ensure a combination is unique:

ALTER TABLE payments
ADD CONSTRAINT unique_user_transaction
UNIQUE (user_id, transaction_id);

create tables

ARUL SELVI ML — Sat, 28 Mar 2026 16:40:45 +0000

1. Primary Key – Unique Identity

Every table needs a way to identify each row uniquely. This is done using a PRIMARY KEY.

Example:

CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(100),
age INT
);

So every student is uniquely identified.

2. NOT NULL – Mandatory Fields

Sometimes, certain fields must always have a value.

Example:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone_number VARCHAR(15)
);

Here:

name and email are required
phone_number is optional

3. UNIQUE – No Duplicates Allowed

If we don’t want duplicate values in a column, we use UNIQUE.

Example:

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

This ensures:

4. CHECK – Valid Data Only

We can restrict values using conditions.

Example:

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2) CHECK (price > 0),
    stock INT CHECK (stock >= 0)
);

5. DEFAULT – Automatic Values

Sometimes we want a column to have a default value if none is provided.

Example:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

6. Combining Constraints

We can use multiple constraints together.

Example:

CREATE TABLE accounts (
    account_number INT UNIQUE NOT NULL,
    balance DECIMAL(10,2) CHECK (balance >= 0)
);

7. Composite Constraints

Sometimes a combination of columns should be unique.

Example:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    UNIQUE (student_id, course_id)
);

8. Foreign Key – Connecting Tables

Foreign keys help link tables together.

Example:

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

9. CASCADE – Automatic Updates & Deletes

We can control what happens when related data changes.

Example:


![ ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4adwzxri0qqs8hufztdj.png)

Idempotency Situation

ARUL SELVI ML — Wed, 25 Mar 2026 13:25:18 +0000

Today I tried to understand what happens when the same transaction is executed multiple times in a database. This can happen in real applications like PhonePe or GPay when there are network issues and the same request is sent again.

I used the same accounts table where Alice has 1000 and Bob has 500. First, I performed a normal transfer of 200 from Alice to Bob.

BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 200
WHERE name = 'Bob';

COMMIT;

After this, Alice had 800 and Bob had 700. Everything was correct.

Then I repeated the same transaction again, as if the same request was sent twice.

BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 200
WHERE name = 'Bob';

COMMIT;

Now the balances became Alice 600 and Bob 900. This means the same transaction was applied again. The database did not stop it.

From this, I understood that PostgreSQL does not automatically prevent duplicate transactions. It will execute whatever queries we send, even if they are repeated.

This can be dangerous in real systems because users may lose money or get incorrect balances if the same request is processed multiple times.

To handle this, real world systems use additional logic. One common approach is using a unique transaction id. Every transaction is given a unique id, and the system checks if that id is already processed. If it is already present, the system ignores the duplicate request.

Another approach is to maintain a transaction history table and check before processing any new request. Some systems also use locking or idempotent operations to avoid repeating the same effect.

I understood that while the database ensures safety using ACID properties, it does not automatically handle duplicate requests. That responsibility is handled by the application design.

Overall, this helped me understand the importance of handling repeated transactions carefully in real world financial systems.

Durability

ARUL SELVI ML — Wed, 25 Mar 2026 13:20:46 +0000

Today I learned about durability in databases by doing a small experiment using a wallet system example.

I used the same accounts table where Alice has 1000 and Bob has 500. First, I checked the initial balances to make sure the data is correct.

Then I performed a money transfer from Alice to Bob.

BEGIN;

UPDATE accounts
SET balance = balance - 300
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 300
WHERE name = 'Bob';

COMMIT;

After committing the transaction, I checked the balances again. Alice’s balance became 700 and Bob’s balance became 800. This showed that the transaction was successful.

Next, I wanted to test what happens if the system restarts. So I disconnected and reconnected to the database, similar to a system restart. After reconnecting, I queried the accounts table again.

I observed that the balances were still 700 and 800. The changes were not lost. This shows that once a transaction is committed, the data is stored permanently.

From this, I understood the concept of durability. It means that once a transaction is completed and committed, the data will remain safe even if there is a crash or restart.

I also thought about what happens if a failure occurs before commit. In that case, the transaction will not be saved, and the database will roll back to the previous state. So no partial changes will be stored.

If a failure happens just after commit, the database uses log files to make sure the data is not lost. PostgreSQL writes changes to a log before saving them to the actual data files. This helps in recovering the data after a crash.

Isolation

ARUL SELVI ML — Wed, 25 Mar 2026 13:18:06 +0000

Today I tried to understand how isolation works in a database by simulating multiple users using the same account at the same time. This is very important for applications like PhonePe or GPay because many users can perform transactions at once.

I used the same accounts table where Alice has 1000 balance. Then I opened two database sessions to act like two different users.

In the first session, I started a transaction and deducted money from Alice’s account but did not commit it.

BEGIN;

UPDATE accounts
SET balance = balance - 700
WHERE name = 'Alice';

At this point, the balance is reduced inside this transaction, but it is not yet saved permanently.

Now in the second session, I also started a transaction and tried to read Alice’s balance.

BEGIN;

SELECT * FROM accounts WHERE name = 'Alice';

I observed that the second session still showed the old balance as 1000. It did not see the uncommitted change from the first session. This means dirty read is prevented.

Then I tried to update again in the second session.

UPDATE accounts
SET balance = balance - 500
WHERE name = 'Alice';

This query did not execute immediately. It waited until the first transaction was either committed or rolled back. This showed that PostgreSQL is preventing conflicts between transactions.

Next, I committed the first transaction.

COMMIT;

After that, the second transaction continued and used the updated balance. This ensured that the final result was correct and no money was lost.

I repeated this experiment with different isolation levels. In the default level, which is read committed, I saw that uncommitted data is not visible. This prevents dirty reads.

When I thought about lower isolation like read uncommitted, such problems could happen, but PostgreSQL does not actually allow dirty reads even in that mode.

I understood that isolation ensures that multiple transactions do not interfere with each other. It prevents problems like reading incomplete data, updating wrong values, or losing updates.

Consistency

ARUL SELVI ML — Wed, 25 Mar 2026 13:13:40 +0000

Today I explored how databases maintain correct data using consistency rules, especially in a wallet system like PhonePe or GPay.

I used the same accounts table where Alice has 1000 and Bob has 500. One important thing in this table is the condition that balance should never be negative. This rule is already defined in the table using a check condition.

First, I tried a normal update where I deduct a valid amount.

UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';

This worked fine because Alice still had a positive balance.

Then I tried something wrong. I attempted to deduct more money than Alice has.

UPDATE accounts
SET balance = balance - 2000
WHERE name = 'Alice';

This time, PostgreSQL did not allow the update. It gave an error because the balance would become negative. This showed that the database itself is enforcing the rule.

Next, I tried directly setting a negative balance.

UPDATE accounts
SET balance = -100
WHERE name = 'Bob';

Again, the database rejected the query. This is because of the check constraint defined in the table. So even if I try to manually break the rule, the database does not allow it.

After this, I understood that consistency is maintained in two ways.

One way is through database constraints. In this case, the check condition ensures that balance is always zero or positive. This is handled directly by PostgreSQL.

Another way is through application logic or transactions. For example, before transferring money, the application should check if the sender has enough balance. This avoids unnecessary errors and improves user experience.

So, constraints act as a safety layer, while application logic acts as a control layer.

I clearly understood that PostgreSQL does not allow invalid data to enter the system. Even if a developer makes a mistake, the database prevents wrong updates.

Overall, this helped me understand how consistency is maintained and why it is very important in financial systems where even a small mistake can cause serious problems.

Atomicity - Design a Reliable Wallet Transfer System with ACID Guarantees

ARUL SELVI ML — Wed, 25 Mar 2026 13:09:50 +0000

Today I worked on a small database problem where I tried to understand how money transfer works in applications like PhonePe or GPay. The main idea was to see how databases handle transactions safely using ACID properties.

First, I created a simple accounts table with id, name, and balance. I inserted two users, Alice with balance 1000 and Bob with balance 500. This helped me simulate a real scenario of transferring money between users.

Before starting the transaction, I checked the data. Alice had 1000 and Bob had 500.

Then I wrote a transaction to transfer 200 from Alice to Bob.

BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 200
WHERE name = 'Bob';

COMMIT;

After running this, I checked the table again. Alice’s balance became 800 and Bob’s balance became 700. This showed a successful transaction.

Next, I wanted to see what happens when something goes wrong. So I introduced an error after deducting money from Alice.

BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';

SELECT * FROM wrong_table;

UPDATE accounts
SET balance = balance + 200
WHERE name = 'Bob';

COMMIT;

Here, the second step caused an error because the table does not exist. Because of this, the transaction failed.

After that, I checked the balances again. I expected Alice’s balance to be reduced, but surprisingly it was still 1000 and Bob was still 500. This means the database did not save the partial change.

This clearly showed the concept of atomicity. Even though one update was executed, since the full transaction did not complete, everything was rolled back.

If the system allowed partial updates, it could lead to money loss or incorrect balances.

Overview of PostgreSQL architecture and ACID

ARUL SELVI ML — Wed, 25 Mar 2026 13:00:44 +0000

Today I learned about PostgreSQL architecture in a more detailed way by understanding each component.

The process starts from the client. A user or application sends a query to the PostgreSQL server through an API or connection layer. Once the request reaches the server, the query processing begins. The system first checks the query, then plans how to execute it, and finally runs it to get the result.
Inside the server, one important part is the shared buffer. This is a memory area where frequently used data is stored. Instead of going to the disk every time, PostgreSQL first checks the shared buffer. This makes the system faster.
Another important concept is write ahead logging. Before updating the actual data in storage, PostgreSQL writes the changes into a WAL log. This ensures that even if the system crashes, the data can be recovered.
There are different background processes related to WAL. The WAL writer is responsible for writing log records from memory to the WAL files. The WAL sender sends these log records to another server in case of replication. On the other side, the WAL receiver receives the logs and applies them to keep the standby server updated.
The storage layer contains the actual data like tables and indexes. Data is read from and written to the disk when needed.
Another important background process is autovacuum. In PostgreSQL, when data is updated or deleted, the old data is not immediately removed. It remains as unused space. The autovacuum process automatically cleans this unused data and frees up space. It also helps maintain database performance by preventing the database from becoming slow over time.
Along with this, other background processes keep running to manage tasks like saving data to disk and maintaining stability.
Finally, after processing everything, the result is sent back to the client.
Overall, learning these components like shared buffer, WAL writer, WAL sender, WAL receiver, and autovacuum helped me understand how PostgreSQL manages performance, data safety, and maintenance in a real system.
ACID
ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties are very important because they make sure that database transactions are reliable and the data remains correct.
Atomicity means all operations in a transaction should happen completely or not happen at all. There is no partial execution. For example, in a bank transfer, if money is deducted from one account but not added to another account, the transaction will be cancelled. So either both actions happen or none happens.
Consistency means the database should always follow rules and constraints. Before and after a transaction, the data must remain valid. For example, if a rule says account balance cannot be negative, the system will not allow a transaction that breaks this rule.
Isolation means multiple transactions happening at the same time should not affect each other. Each transaction should work as if it is running alone. For example, if two users are accessing the same account, one user should not see incomplete changes made by another user.
Durability means once a transaction is successfully completed, the data is permanently stored. Even if there is a system crash or power failure, the data will not be lost because it is saved properly in the database.
A simple example to understand all these properties is a bank transfer system. When a person transfers money, the system ensures that the amount is deducted and added correctly, rules are followed, other users do not see intermediate changes, and once the transaction is completed, it is saved permanently.

Merge Two Sorted Linked Lists

ARUL SELVI ML — Tue, 24 Mar 2026 16:04:40 +0000

Merge Two Sorted Linked Lists

Problem Statement

Given the heads of two sorted linked lists, merge them into one sorted linked list and return the head of the merged list.

Example

Input
List1
1 -> 2 -> 4

List2
1 -> 3 -> 4

Output
1 -> 1 -> 2 -> 3 -> 4 -> 4

Approach Iterative Method

Compare nodes from both lists and build a new sorted list.

Steps

1 Create a dummy node to store result
2 Compare values of both lists
3 Attach smaller node to result
4 Move pointer of that list
5 Repeat until one list becomes empty
6 Attach remaining nodes

Code

```python id="mll1"
class ListNode:
def init(self, val=0, next=None):
self.val = val
self.next = next

def mergeTwoLists(l1, l2):
dummy = ListNode(0)
tail = dummy

while l1 and l2:
    if l1.val < l2.val:
        tail.next = l1
        l1 = l1.next
    else:
        tail.next = l2
        l2 = l2.next
    tail = tail.next

if l1:
    tail.next = l1
if l2:
    tail.next = l2

return dummy.next




---

## Approach Recursive Method

---

### Code



```python id="mll2"
def mergeTwoLists(l1, l2):
    if not l1:
        return l2
    if not l2:
        return l1

    if l1.val < l2.val:
        l1.next = mergeTwoLists(l1.next, l2)
        return l1
    else:
        l2.next = mergeTwoLists(l1, l2.next)
        return l2

Explanation

The iterative method builds the merged list step by step using a dummy node. The recursive method compares nodes and builds the list during function calls.

Expected Output

Input
1 -> 2 -> 4
1 -> 3 -> 4

Output
1 -> 1 -> 2 -> 3 -> 4 -> 4

Conclusion

Merging two sorted linked lists is a basic and important problem. It helps in understanding linked list traversal and pointer manipulation.

Practice both iterative and recursive methods to improve your coding skills.

Sort a Linked List using Merge Sort

ARUL SELVI ML — Tue, 24 Mar 2026 16:02:52 +0000

Sort a Linked List using Merge Sort

Problem Statement

Given the head of a linked list, sort the list in ascending order using merge sort and return the sorted list.

Example

Input
4 -> 2 -> 1 -> 3

Output
1 -> 2 -> 3 -> 4

Input
-1 -> 5 -> 3 -> 4 -> 0

Output
-1 -> 0 -> 3 -> 4 -> 5

Approach Merge Sort on Linked List

Merge sort works by dividing the list into halves, sorting each half, and then merging them.

Steps

1 Find the middle of the linked list
2 Split the list into two halves
3 Recursively sort both halves
4 Merge the sorted halves

Code

```python id="msll1"
class ListNode:
def init(self, val=0, next=None):
self.val = val
self.next = next

def getMiddle(head):
slow = head
fast = head.next

while fast and fast.next:
    slow = slow.next
    fast = fast.next.next

return slow

def merge(l1, l2):
dummy = ListNode(0)
tail = dummy

while l1 and l2:
    if l1.val < l2.val:
        tail.next = l1
        l1 = l1.next
    else:
        tail.next = l2
        l2 = l2.next
    tail = tail.next

if l1:
    tail.next = l1
if l2:
    tail.next = l2

return dummy.next

def sortList(head):
if not head or not head.next:
return head

mid = getMiddle(head)
right = mid.next
mid.next = None

left_sorted = sortList(head)
right_sorted = sortList(right)

return merge(left_sorted, right_sorted)




---

## Explanation

The linked list is divided into two halves using the slow and fast pointer method. Each half is sorted recursively. Finally, both halves are merged into a sorted list.

---

## Expected Output

Input
4 -> 2 -> 1 -> 3

Output
1 -> 2 -> 3 -> 4

---

## Conclusion

Merge sort is very efficient for linked lists because it does not require random access. This problem helps in understanding recursion and linked list manipulation.

Practice this problem to strengthen your understanding of sorting and linked lists.

Remove Duplicates from Sorted Linked List

ARUL SELVI ML — Tue, 24 Mar 2026 16:01:18 +0000

Remove Duplicates from Sorted Linked List

Problem Statement

Given the head of a sorted linked list, delete all duplicates such that each element appears only once. Return the linked list sorted as well.

Example

Input
1 -> 1 -> 2 -> 3 -> 3

Output
1 -> 2 -> 3

Approach Iterative Method

Since the list is already sorted, duplicates will be adjacent. We can compare current node with the next node and remove duplicates.

Steps

1 Start from the head node
2 Compare current node with next node
3 If both values are equal, skip the next node
4 Else move to the next node
5 Repeat until end of list

Code

```python id="rdll1"
class ListNode:
def init(self, val=0, next=None):
self.val = val
self.next = next

def deleteDuplicates(head):
current = head

while current and current.next:
    if current.val == current.next.val:
        current.next = current.next.next
    else:
        current = current.next

return head




---

## Explanation

Since the list is sorted, all duplicate values are next to each other. We simply skip the duplicate nodes by changing the pointer of the current node.

---

## Expected Output

Input
1 -> 1 -> 2 -> 3 -> 3

Output
1 -> 2 -> 3

---

## Conclusion

This problem helps in understanding linked list traversal and pointer manipulation. It is simple yet important for mastering linked list operations.

Practice this problem to improve your confidence in working with linked lists.

Reverse a Linked List

ARUL SELVI ML — Tue, 24 Mar 2026 15:59:40 +0000

Reverse a Linked List

Problem Statement

Given the head of a singly linked list, reverse the list and return the new head.

Example

Input
1 -> 2 -> 3 -> 4 -> 5

Output
5 -> 4 -> 3 -> 2 -> 1

Approach 1 Iterative Method

Reverse the links of the list one by one.

Steps

1 Initialize three pointers previous as None, current as head, next as None
2 Traverse the list
3 Store next node
4 Reverse current node link
5 Move previous and current one step forward
6 Repeat until current becomes None

Code

```python id="ll1"
class ListNode:
def init(self, val=0, next=None):
self.val = val
self.next = next

def reverseList(head):
prev = None
curr = head

while curr:
    next_node = curr.next
    curr.next = prev
    prev = curr
    curr = next_node

return prev




---

## Approach 2 Recursive Method

Reverse the list using recursion.

---

### Code



```python id="ll2"
def reverseList(head):
    if not head or not head.next:
        return head

    new_head = reverseList(head.next)

    head.next.next = head
    head.next = None

    return new_head

Explanation

In the iterative method, we reverse the direction of pointers step by step.
In the recursive method, we reverse the rest of the list and then fix the current node.

Expected Output

Input
1 -> 2 -> 3 -> 4 -> 5

Output
5 -> 4 -> 3 -> 2 -> 1

Conclusion

Reversing a linked list is a fundamental problem that helps in understanding pointers and data structures. It is frequently asked in coding interviews.

Practice both iterative and recursive methods to strengthen your understanding.