DEV Community

Anjana R.K.
Anjana R.K.

Posted on

Consistency

Hi!
To explore how the database enforces rules that maintain valid data states, particularly ensuring that account balances never become negative.
first create a table account,

CREATE TABLE accounts (id SERIAL PRIMARY KEY,name TEXT NOT NULL,balance INT NOT NULL CHECK (balance >= 0),last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Enter fullscreen mode Exit fullscreen mode

the table contains atributes id as primary key,name,balance hee check for condition as if balance >=0,last_updated here set default value as current timestamp.
Next,insert values to the table.

INSERT INTO accounts (name, balance) VALUES ('Alice', 1000),('Bob', 500);
Enter fullscreen mode Exit fullscreen mode

Attempt to perform operations that violate deducting more money than is available in an account or directly updating a balance to a negative value.

UPDATE accounts SET balance = balance - 1200 WHERE name = 'Alice';
Enter fullscreen mode Exit fullscreen mode

IT WILL THROW ERROER as attempting to deduct 1200 from Alice who only has 1000.
In PostgreSQL,the failure occurs due to Constraints because if the deduction occour with balance less than the debit amount while checking the constraints the balance >=0 fails.

Top comments (0)