Updating Data and Best Practice for Deleting Data

TLDR

Insert data into related tables using INSERT INTO, with references to foreign keys (house_id).

Update data in tables using UPDATE, making sure to maintain consistency across related records.

Safely delete data using DELETE, while considering foreign key constraints and cascading deletes.

Use best practices like transactions and cascading updates/deletes to maintain data integrity.

1. Updating Data (`UPDATE`)

The UPDATE statement allows you to modify existing data in the table. Here’s how to update records in the persons, houses, and street tables.

a. Updating a person's last name

Let’s say John Doe’s last name changes to Smith. We can update his record using the following query:


UPDATE persons
SET lastname = 'Smith'
WHERE firstname = 'John' AND house_id = 1;

This updates the last name of the person whose firstname is John and lives in the house with house_id = 1.

b. Updating the address in the `houses` table

If the house address changes, we can update it like this:


UPDATE houses
SET address = '456 Oak Street'
WHERE id = 1;

This changes the address of the house with id = 1 to 456 Oak Street.

c. Updating the street name

To change the name of the street from Elm Street to Oak Street:


UPDATE street
SET name = 'Oak Street'
WHERE house_id = 1;

This updates the street name where house_id = 1, ensuring consistency across related tables.

2. Deleting Data (`DELETE FROM`)

The DELETE statement allows you to remove rows from a table. Here’s how to safely delete data in your schema.

a. Deleting a person

To delete a person from the persons table:


DELETE FROM persons
WHERE firstname = 'John' AND house_id = 1;

This deletes the record for John (now John Smith) who lives in the house with house_id = 1.

b. Deleting a house and its dependencies

If you want to delete a house and its associated persons and street records, you need to consider foreign key constraints.

If you have cascading deletes: This means deleting a house will automatically delete all related persons and street records.

If no cascading deletes: You will need to delete the dependent records first.

Deleting related records manually:

Delete from persons:


DELETE FROM persons
WHERE house_id = 1;

Delete from street:


DELETE FROM street
WHERE house_id = 1;

Delete the house:


DELETE FROM houses
WHERE id = 1;

This sequence ensures that all related records are safely removed without violating foreign key constraints.

3. Best Practices for Data Integrity

a. Foreign Key Integrity

Foreign keys help ensure that data in related tables remains consistent. For example, you can’t insert a house_id in persons or street unless that house_id exists in the houses table. Similarly, when deleting data, be mindful of foreign key constraints.

b. Using Transactions

When making multiple changes to the database, it's often a good idea to wrap them in a transaction. This ensures that all changes are applied atomically (either all succeed, or none are applied):



BEGIN;

-- Insert data into houses, persons, and street
INSERT INTO houses (address, city) VALUES ('789 Maple Street', 'New York');
-- More queries

COMMIT;

If something goes wrong, you can roll back:


ROLLBACK;

c. Cascading Deletes and Updates

If you want the database to automatically handle deleting or updating related records, you can define cascading rules when creating foreign key constraints:


ALTER TABLE persons
ADD CONSTRAINT fk_persons_house
FOREIGN KEY (house_id) REFERENCES houses(id)
ON DELETE CASCADE;

This ensures that when a house is deleted, all persons linked to that house are also deleted.