SQL CREATE TABLE: Creating Tables and Changing Data

A table definition describes what each row can hold. It does not add any rows. In an online shop, each product needs separate fields for its identifier, name, price and stock count.

Build the definition

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC(8, 2) NOT NULL,
  in_stock INTEGER DEFAULT 0
);

Read from the outside in. CREATE TABLE products names the new table. Parentheses contain its column definitions. Each line gives a column name, a type and any rules for that column. Commas separate definitions; the last definition has no comma. The semicolon ends the statement. After it succeeds, SELECT * FROM products; returns zero rows.

This key declaration generates IDs in SQLite. In PostgreSQL, use id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY to generate them. The decimal declaration also behaves differently across these systems; choosing exact storage is part of choosing a type.

Give each fact its own column

A customer row could contain id, first_name, last_name, email and signup_date. Packing "Ana Ruiz, ana@example.com" into one field makes the name and email difficult to search or check separately. Name the fields for what they contain. Short lowercase names joined with underscores, such as unit_price, are easy to reuse.

Avoid keywords as names: orders works, while unquoted order conflicts with SQL's ORDER keyword. A name containing spaces needs double quotes, as in "order date", each time you use it. PostgreSQL folds unquoted names to lowercase, so unquoted Products and products refer to the same name.

Single quotes have another job: they enclose text values such as 'Desk lamp'. Double quotes identify names. PostgreSQL reads "Desk lamp" as a column name, not as product text.

Check what was created

A trailing comma before ) causes a syntax error. A missing comma often does too, but SQLite can read several words as one type name. CREATE TABLE t (a INTEGER b TEXT); creates one column called a, not two columns. Inspect the definition:

• SQLite shell: .schema products prints the creation statement.
• SQLite SQL: PRAGMA table_info(products); lists columns and types.
• PostgreSQL psql: \d products shows columns, types and constraints.

CREATE TABLE IF NOT EXISTS products (...) avoids an error when the name is already present. It does not compare or update the old definition. A table missing a needed column stays that way.

Create a one-time copy

CREATE TABLE cheap_products AS
SELECT name, price FROM products WHERE price < 10;

This creates columns and copies the query's rows. Later product changes do not refresh the copy. In SQLite and PostgreSQL, the new table does not inherit the source's primary key, NOT NULL or CHECK rules.

CREATE, ALTER and DROP belong to data definition language, or DDL: they change structure. INSERT, UPDATE and DELETE are data manipulation language, or DML: they change rows. SELECT reads data; books differ on whether they include it in DML or give queries their own family.

Choose a type from the job the value must do. A quantity must support arithmetic. A ZIP code must preserve its characters, including leading zeros. Both can contain digits, but they need different storage.

Choose number and text types

In PostgreSQL, NUMERIC(8, 2) has eight total digits, two after the decimal point. Its largest positive value is 999999.99. Inserting 12.345 stores 12.35; 1234567.00 overflows because it needs seven digits before the point. DECIMAL is another name for this type family.

SQLite accepts NUMERIC(8, 2) but does not enforce that precision or scale. It stores 19.99 as floating point and keeps 12.345 unrounded. Store 1999 whole cents for an exact amount of 19.99. Floating point approximates binary fractions: SQLite's SELECT 0.1 + 0.2 = 0.3; returns 0. PostgreSQL treats those bare decimal literals as exact numeric values; cast them to DOUBLE PRECISION to see the approximate result.

Text numbers sort by characters: '100', '25', '9' appear in that order. Numeric quantities need numeric columns. ZIP code '02139' belongs in text; integer storage loses its initial zero.

PostgreSQL's VARCHAR(20) rejects text longer than 20 characters. Its CHAR(n) pads shorter text with spaces to a fixed length. TEXT has no declared character limit. SQLite treats both VARCHAR(n) and CHAR(n) as text, without enforcing the length or adding padding.

Store dates and yes/no values

PostgreSQL has DATE, TIME, TIMESTAMP and BOOLEAN. It rejects an impossible DATE such as '2026-02-30'. SQLite has no dedicated date storage type. Use ISO text such as '2026-03-15', whose character order matches date order. US-style text misorders dates across years: '12/01/2025' sorts after '03/15/2026'.

A timestamp adds a time, such as '2026-03-15 14:30:00'. PostgreSQL uses TRUE and FALSE for Booleans; SQLite stores 1 and 0 and accepts those keywords. MySQL treats BOOLEAN as a small integer; SQL Server uses BIT.

Know what the system enforces

Ordinary SQLite tables use type affinity, a storage preference. An INTEGER column converts '5' to 5 but accepts 'abc' as text. PostgreSQL rejects text that cannot become an integer. SQLite 3.37.0 and later support STRICT tables, whose permitted types are INT, INTEGER, REAL, TEXT, BLOB and ANY.

For orders, choose an integer id, text email, a PostgreSQL DATE or SQLite ISO text date, PostgreSQL NUMERIC(10, 2) or SQLite integer cents for the total, and a Boolean or 0/1 flag for payment. NULL means missing or unknown; it is neither a type nor the same as zero or empty text. A column accepts it unless a rule forbids it.

A constraint makes the database reject data that breaks a declared rule. The check runs when rows are inserted or updated, no matter which application sends the statement. Different rules protect different parts of a product record.

Combine rules in a table

This SQLite definition generates the key and checks the other fields. For PostgreSQL, replace the key line with an identity primary key.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  sku TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  price NUMERIC(8, 2) NOT NULL CHECK (price > 0),
  in_stock INTEGER NOT NULL DEFAULT 0 CHECK (in_stock >= 0)
);

NOT NULL requires a value. UNIQUE prevents a repeated SKU. CHECK (price > 0) rejects zero or negative prices. The stock count has a default of 0 and must stay nonnegative. SQLite's numeric declaration does not give it exact decimal arithmetic, so use integer cents when exactness matters.

Distinguish omission from NULL

Insert a desk lamp with SKU 'LAMP-01', name 'Desk lamp' and price 24.99, leaving stock out of the column list. It succeeds and gets stock 0. A supplied NULL stock does not request the default: it fails NOT NULL.

A coffee mug priced at -3.50 fails the price check. A second product with SKU 'LAMP-01' fails UNIQUE, even if its name and price differ. A pen with no name fails NOT NULL. Read the named rule or column in the error and correct the input.

Empty text is a real value. name TEXT NOT NULL accepts '' in SQLite and PostgreSQL. Add CHECK (name <> '') to reject it. Oracle treats empty text as NULL, so the same input meets a different rule there.

Check combinations and missing values

A column rule follows its type. A table rule is a separate item inside the table definition and can compare columns:

CHECK (end_date >= start_date),
UNIQUE (order_id, product_id)

The first rejects an end date before a start date. The second rejects a repeated pair: a product can occur in different orders, but cannot appear twice in the same order. A table may have several unique constraints.

A check rejects a row when its condition is false. If price is NULL, price > 0 is unknown, so the check passes. That is why the product price also needs NOT NULL. PostgreSQL, SQLite and MySQL normally allow multiple NULLs under UNIQUE; SQL Server allows one in a single-column unique constraint. PostgreSQL 15 and later offer UNIQUE NULLS NOT DISTINCT when missing values must conflict too. MySQL versions before 8.0.16 ignored CHECK rules.

Give a rule a useful name with CONSTRAINT price_positive CHECK (price > 0). The name helps identify it in an error and remove it later. A constraint checks exactly its expression: CHECK (email LIKE '%@%') catches a missing at sign but cannot catch a typo in an otherwise matching address.

A primary key identifies one row reliably. It can be one column or a combination, but a table has at most one primary key. The key must be unique and present, so other statements can name a row without depending on its description.

Choose a stable identifier

A natural key is an existing real-world identifier, such as a book's ISBN. A surrogate key is an artificial identifier, often a generated integer. A customer's email can change; a generated customer ID can stay the same while that happens.

A composite key identifies a combination:

PRIMARY KEY (order_id, product_id)

An order can contain several products, and a product can occur in several orders. The pair identifies one order item. Neither column needs to be unique by itself.

PostgreSQL, MySQL and SQL Server enforce nonmissing primary keys. Ordinary SQLite tables retain a historical exception: a primary key other than INTEGER PRIMARY KEY can contain NULL. Use code TEXT PRIMARY KEY NOT NULL for a text key. SQLite STRICT tables close this gap too.

Let the database generate the number

Leave id out of the insert's column list to request a generated value. A plain PostgreSQL INTEGER PRIMARY KEY does not generate one. Older PostgreSQL code often uses SERIAL.

In SQLite, INTEGER PRIMARY KEY aliases the internal row number. Start with generated IDs 1, 2 and 3, then delete 3. The next normal generated ID is 3 again, one above the current maximum. With INTEGER PRIMARY KEY AUTOINCREMENT, it is 4: a number held by a committed row is not reused. A number from a rolled-back insert can still be reused. This stronger guarantee costs extra work, so choose it when the no-reuse requirement matters.

Read IDs without treating them as counts

Gaps are normal. Deletions leave them, and PostgreSQL does not return sequence numbers after failed or rolled-back inserts. Use COUNT(*) for a row count and a timestamp for event time. The largest ID answers neither question reliably.

PostgreSQL's GENERATED ALWAYS form normally rejects an explicit ID such as 50. GENERATED BY DEFAULT and SERIAL accept explicit values, but supplying 50 does not move the generator past 50. A later generated value can collide with it.

To use a new row's key in a related row, read the key back:

INSERT INTO customers (first_name, last_name, email)
VALUES ('Dev', 'Patel', 'dev@example.com')
RETURNING id;

RETURNING works in PostgreSQL and SQLite 3.35.0 and later. SQLite also offers last_insert_rowid(), MySQL offers LAST_INSERT_ID(), and SQL Server uses OUTPUT. Read the result of the insert instead of guessing the next ID.

An order's customer_id should point to a customer who exists. A foreign key makes the database check that link. The table holding the reference is the child; the table it points to is the parent.

Declare and enforce the link

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id)
);

Here customers must exist first, and its id must be a primary key or unique key. Match the types on both sides. The table form, FOREIGN KEY (customer_id) REFERENCES customers(id), declares the same link separately from the column.

With enforcement on, an order for customer 99 fails when no customer 99 exists. Updating an order to that missing customer also fails. A nullable foreign key can hold NULL, meaning no linked row; NOT NULL makes the link required.

In SQLite, run PRAGMA foreign_keys = ON; on each new connection before starting a transaction. A setting change inside a transaction has no effect. PostgreSQL and SQL Server enforce foreign keys normally, and MySQL's default InnoDB engine does too.

Predict a parent deletion

Use customers 1, Ana, and 2, Ben. Orders 101 and 102 belong to Ana; 103 belongs to Ben. Consider deleting customer 1 under different declarations:

SET NULL needs a nullable column. SET DEFAULT still has to satisfy the foreign key: a nonmissing default must identify an existing parent. A NULL default works when missing links are allowed.

Choose by the meaning of the data. Order items often belong entirely to their order, so cascading can fit. Orders that must be retained call for a blocking action when someone tries to remove their customer. An optional supplier link can be set to NULL. Applying cascades everywhere lets one delete remove more records than its table name suggests.

Work in dependency order

Create parents before children and insert parent rows before rows that refer to them. When removing related data or tables, work from children to parents. An ON UPDATE action offers the same choices when the parent's key changes; ON UPDATE CASCADE copies the new key to the children.

RESTRICT checks immediately. NO ACTION, the default, can wait until the statement ends, or until transaction end for a deferrable constraint. Both block an ordinary delete that would leave unmatched references. Keep generated IDs stable so that changing a customer's details does not require changing every reference to that customer.

INSERT adds rows to an existing table. Its column list is the map between the values you supply and the fields that receive them. Write the map explicitly so that a later table change does not silently change your input's meaning.

Match values by position

INSERT INTO products (sku, name, price, in_stock)
VALUES ('LAMP-01', 'Desk lamp', 24.99, 12);

Read the pairs in order: SKU gets 'LAMP-01', name gets 'Desk lamp', price gets 24.99 and stock gets 12. There must be four values for these four columns. A mismatch such as two values for three columns fails.

Without the list, values follow the table's declared column order. SQLite requires all columns to be supplied; PostgreSQL allows fewer and defaults the trailing columns. An explicit list avoids relying on either behavior. It also lets you put columns in an order that is clear for the input.

A swapped pair can be worse than a syntax error. Supplying 9.99 as the name and 'Desk lamp' as price makes PostgreSQL reject the nonnumeric price. Ordinary SQLite affinity can store name text "9.99" and price text "Desk lamp" without an error. Read the inserted row back with SELECT to check the mapping.

Write literal values correctly

The doubled apostrophe stores one apostrophe. Numbers have no currency sign or thousands separator; a comma separates values, so a comma inside an unquoted amount changes the value count. 'NULL' is the four-letter text "NULL", not a missing value.

Let omitted fields fill themselves

A column left out receives its default, or NULL if it has none. A generated key receives a generated value. That last behavior depends on the declaration: SQLite's INTEGER PRIMARY KEY generates IDs; PostgreSQL requires an identity declaration for this purpose. A plain PostgreSQL integer primary key left out of an insert fails because its value would be NULL.

PostgreSQL, MySQL and SQL Server allow DEFAULT as a value placeholder. SQLite does not accept it inside a VALUES list; leave that column out instead. An explicit NULL never requests the default.

Add several rows together

INSERT INTO customers (first_name, last_name, email) VALUES
  ('Ana', 'Ruiz', 'ana@example.com'),
  ('Ben', 'Okafor', 'ben@example.com'),
  ('Chloe', 'Nguyen', 'chloe@example.com');

In an empty table with its generated key starting at 1, these receive IDs 1, 2 and 3. Under ordinary constraint handling, the multi-row statement succeeds as a whole or fails as a whole. If one row violates a rule, none of this statement's rows are inserted.

A UNIQUE error points to a duplicate key or value; NOT NULL points to missing data; CHECK points to a failed condition; and a foreign key error points to an invalid link. Fix the value or its intended mapping before trying again.

Rows do not have to be typed one at a time. A query can supply the rows for an insert, and a conflict rule can say what should happen when a unique value already exists. Both features depend on matching the intended data precisely.

Copy a query result

INSERT INTO archived_orders (id, customer_id, order_date, total)
SELECT id, customer_id, order_date, total
FROM orders
WHERE order_date < '2026-01-01';

There is no VALUES clause: the query supplies the values. Match its selected columns to the target list by position. The target table must already exist, and its rules still apply.

Preview the SELECT alone. Orders 201, 202 and 204 have dates 2025-11-03, 2025-12-19 and 2025-10-30 and totals 45.00, 120.50 and 60.00. Orders 203 and 205 are dated in 2026. The query returns three rows totaling 225.50, and the insert copies those three. It can also copy zero rows when the filter finds nothing.

A second run tries to copy them again. A target primary key makes that fail instead of silently storing duplicates. INSERT ... SELECT is a copy operation; it does not remove the source rows or keep the two tables synchronized.

File imports often go through a staging table first. SQLite's shell uses .import --csv file.csv staging; psql uses \copy. In a later query, TRIM(email) removes surrounding spaces and NULLIF(TRIM(email), '') turns a blank email into NULL. The destination constraints then check the cleaned rows.

Read the result of a write

INSERT INTO customers (first_name, last_name, email)
VALUES ('Dev', 'Patel', 'dev@example.com')
RETURNING id;

The result gives the generated customer ID to use in a related row. PostgreSQL and SQLite 3.35.0 and later support RETURNING on inserts, updates and deletes. MySQL uses LAST_INSERT_ID() for a generated ID; SQL Server uses OUTPUT.

Decide how duplicates should behave

With stock (sku TEXT PRIMARY KEY, qty INTEGER NOT NULL), suppose 'LAMP-01' has quantity 12. A plain insert proposing the same SKU and quantity 20 fails. In SQLite and PostgreSQL:

INSERT INTO stock (sku, qty) VALUES ('LAMP-01', 20)
ON CONFLICT (sku) DO UPDATE
SET qty = stock.qty + excluded.qty;

stock.qty is the existing 12. excluded.qty is the proposed 20. This form stores 32, which fits an extra delivery. SET qty = excluded.qty stores 20, which fits a replacement count. DO NOTHING keeps 12 and skips the conflicting row. Each variation starts from the same original quantity 12.

An upsert combines inserting a new row with updating on a conflict. The conflict target must have suitable uniqueness protection. MySQL's equivalent family is ON DUPLICATE KEY UPDATE. A plain INSERT does not correct an existing customer: it either creates another row or hits a uniqueness error. Use UPDATE for a known existing row, and an upsert when the row might already exist.

UPDATE changes values in rows that already exist. It names a table, assigns new values with SET, then chooses the target rows with WHERE. The table's constraints check the resulting values.

Set one or several fields

UPDATE products
SET price = 22.50, in_stock = 30
WHERE id = 3;

The product with key 3 receives both values; other rows keep theirs. Assignments use commas. Writing SET price = 22.50 AND in_stock = 30 does not mean two assignments. PostgreSQL rejects it; SQLite treats the right side as a Boolean expression and can store 0 or 1 in price.

A missing WHERE is valid SQL and applies to every row. UPDATE products SET price = 0; sets every product's price to zero if the table's rules allow it. A preview with the intended condition shows whether the target is one row, several rows or none.

Calculate from the stored value

UPDATE products
SET price = price * 1.10;

A price of 20.00 becomes 22.00 after a 10% increase. A price of 19.99 gives an unrounded result of 21.989. PostgreSQL stores 21.99 in a NUMERIC(8, 2) column. SQLite does not enforce the scale, so SET price = ROUND(price * 1.10, 2) requests two decimal places. For exact SQLite money storage, keep whole cents and make the cents rounding decision explicit.

The expression must include the old value when the change is relative. SET in_stock = in_stock - 1 removes one unit; SET in_stock = 1 replaces the count with one.

In SQLite and PostgreSQL, all right-hand expressions read the row before the update. UPDATE t SET a = b, b = a; swaps a row's values from 1 and 2 to 2 and 1. MySQL's single-table update evaluates assignments left to right, so the same form makes both 2. Do not transfer this assumption between systems.

Keep rules and missing values in view

Use SET phone = NULL to clear a phone field. To find missing phone values, use WHERE phone IS NULL. Equality with NULL does not find them.

Consider prices 12.00, 3.00 and 8.50 protected by CHECK (price > 0). Subtracting 5 from every price would make the second -2.00. With ordinary constraint handling, that failure undoes the whole statement, so even the otherwise valid prices stay unchanged.

A primary key update also has to satisfy related foreign keys. A blocked key change fails while children point to the old key; ON UPDATE CASCADE transfers the new value to them. Keeping generated IDs stable avoids this extra work when descriptive details change.

Interpret the affected-row count

psql reports a result such as UPDATE 3; SQLite's changes() reports the count for the last change statement. Both include rows matched and updated even when the assigned value equals the old value. A count of three does not promise three visibly different values. Compare the count with the preview and inspect the relevant fields afterward, or use RETURNING to see affected rows directly.

DELETE removes whole rows. It leaves the table ready to hold rows again. Clearing one field uses UPDATE ... SET column = NULL; DELETE email FROM customers is not valid SQL.

Follow a filtered delete

The shop has these orders:

DELETE FROM orders
WHERE status = 'cancelled'
  AND order_date < '2026-01-01';

Order 201 meets both tests, as does 204. Order 203 is cancelled but too recent; 202 is old enough but shipped. The statement removes two rows and leaves 202, 203 and 205. DELETE FROM orders; without the condition would remove all five.

Use related data to choose rows

DELETE FROM order_items
WHERE order_id IN (
  SELECT id FROM orders WHERE status = 'cancelled'
);

The inner query produces cancelled order IDs. IN checks each item's order_id against that list. Run the inner query alone to inspect the keys, then preview the item rows with the same condition.

Foreign keys also affect deletion. A parent with referring children can be protected by NO ACTION or RESTRICT; a cascade removes children; SET NULL keeps children but clears the link. Check those actions when judging the effect of deleting a parent, because the directly named table may not be the only table changed.

Choose what should remain

PostgreSQL, MySQL, SQL Server and Oracle support TRUNCATE; SQLite does not. Its transaction and dependency rules vary by database. A statement that sounds like a quick emptying operation must still fit those rules.

Keep history with a soft delete

UPDATE customers
SET deleted_at = '2026-03-15'
WHERE id = 7;

A soft delete marks the row without removing it. It can preserve history and allow restoration by clearing the mark. Queries for active customers must include WHERE deleted_at IS NULL; otherwise the marked customers still appear. Marking data does not erase it.

A committed delete cannot be undone by a later ROLLBACK. Restoration needs a saved copy or backup. Within an open transaction, rollback can still restore deleted rows. Check the selected IDs and affected count before making the result permanent.

Choosing rows is often the hardest part of a change. The same filter that is harmless in a query can delete or update too much when reused in a write. Turn the intended rule into a condition, then inspect what it actually matches.

Preview the exact condition

SELECT id, status, order_date FROM orders
WHERE status = 'cancelled'
  AND order_date < '2026-01-01';

This preview returns orders 201 and 204 in the five-order shop example. Reuse exactly that condition in the delete. Rewriting it while changing the command creates a new, unchecked selection.

For one specific row, use its primary key: WHERE id = 42 matches at most one row. A descriptive name such as 'Desk lamp' may be shared by several products.

Group alternatives explicitly

AND binds more tightly than OR. Compare:

WHERE status = 'cancelled'
   OR status = 'expired' AND order_date < '2026-01-01'

with:

WHERE (status = 'cancelled' OR status = 'expired')
  AND order_date < '2026-01-01'

The first accepts every cancelled order, regardless of date, plus expired orders before 2026. It matches 201, 203 and 204. The second applies the date test to both statuses and matches 201 and 204. Order 203 exposes the difference because it is cancelled in 2026.

Include missing values deliberately

WHERE customer_id = NULL matches no rows. Missing values need IS NULL or IS NOT NULL. A negative test also has a gap: status <> 'shipped' does not include a missing status, because its comparison is unknown.

If missing status belongs in the intended group, write:

WHERE status <> 'shipped' OR status IS NULL

When adding another condition, group this whole alternative with parentheses so that both branches meet that condition.

Pattern matching can reach beyond the intended description. LIKE '%lamp%' matches "Desk lamp" and "Clamp". SQLite's default LIKE also ignores case for English letters, so it finds "Lamp shade". A pattern is useful for finding possible matches, but inspect those matches before changing them.

Choose a list comparison for a list

UPDATE orders SET status = 'on hold'
WHERE customer_id IN (
  SELECT id FROM customers WHERE state = 'CA'
);

The subquery can supply several customer IDs; IN checks membership. Replacing IN with = asks for one scalar result. PostgreSQL rejects multiple rows there, while SQLite silently uses the first row returned and misses other customers.

After the change, compare the affected count with the preview. PostgreSQL and SQLite can also append RETURNING * to show the affected rows. For a risky write, start a transaction before changing data, inspect the result, then choose COMMIT or ROLLBACK.

A transaction groups related statements into one unit of work. You decide to keep its changes together with COMMIT or undo them together with ROLLBACK. The all-or-nothing property is called atomicity, the A in ACID.

Keep both sides of a transfer

Maya's account, ID 1, holds 500.00. Leo's account, ID 2, holds 120.00. Moving 75.00 requires two changes:

BEGIN;
UPDATE accounts SET balance = balance - 75.00 WHERE id = 1;
UPDATE accounts SET balance = balance + 75.00 WHERE id = 2;
SELECT id, balance FROM accounts WHERE id IN (1, 2);
COMMIT;

The first update leaves Maya with 425.00. The second leaves Leo with 195.00. The check shows both balances before the commit, and the total stays 620.00. For these exact decimal balances use PostgreSQL NUMERIC; in SQLite an exact-money version stores 50000 and 12000 cents and transfers 7500 cents.

Without an explicit transaction under autocommit, the first update could become permanent before the second runs. A crash between them would keep the withdrawal without the deposit. A transaction gives the two updates one final keep-or-undo decision. If a statement fails, handle the error before deciding that the transfer is complete.

Understand the default boundary

Under database autocommit, each successful standalone statement commits as its own transaction. This is the usual starting behavior for SQLite, PostgreSQL, MySQL and SQL Server. A programming library or client can choose different settings and start a transaction for you.

BEGIN; starts an explicit transaction in SQLite and PostgreSQL. SQLite and SQL Server also use BEGIN TRANSACTION; PostgreSQL and MySQL accept START TRANSACTION. Oracle starts a transaction with a data change and ordinarily waits for COMMIT in tools such as SQL*Plus unless autocommit is enabled.

COMMIT ends the transaction and keeps its changes. ROLLBACK ends it and undoes its uncommitted changes. A later rollback cannot cross an earlier commit.

Inspect and undo

BEGIN;
DELETE FROM orders;
SELECT * FROM orders;
ROLLBACK;

Your connection sees an empty table at the SELECT, then sees the five orders again after rollback. The empty result was real within the transaction, but was not yet permanent. Other connections normally do not see the uncommitted changes.

A transaction can protect a wrong row selection only if you inspect and choose to undo it. SQL can successfully update an unintended row: no database rule knows which account you meant to name. Compare returned IDs, values and counts with the intended result before committing.

Keep transactions short and end them. Open transactions can hold locks that make other connections wait; SQLite permits only one writer at a time. Closing a connection with an unfinished transaction rolls back its pending changes. When a library starts a transaction automatically, the program must call its commit method to keep the work.

A failed statement does not have the same effect in every database. You need to know what is still pending before deciding how to recover. A savepoint can provide a smaller place to return to inside an open transaction.

Trace an error

After the transfer, Maya has 425.00 and Leo has 195.00. The table has CHECK (balance >= 0). Start a transaction and subtract 30.00 from Maya: her pending balance is 395.00. Then try to subtract 300.00 from Leo. That would make his balance -105.00, so the second statement fails.

In SQLite, committing at this point would keep Maya's deduction alone. In PostgreSQL, COMMIT on the failed transaction ends it with a rollback. A clear recovery routine is to issue ROLLBACK, correct the cause, and rerun the related statements. Both balances then return to 425.00 and 195.00 before the corrected attempt.

Undo a later part with a savepoint

BEGIN;
UPDATE accounts SET balance = balance + 10 WHERE id = 1;
SAVEPOINT after_first;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
ROLLBACK TO after_first;
COMMIT;

The savepoint comes after Maya's increase. ROLLBACK TO undoes Leo's later increase but preserves Maya's. The final balances are 435.00 and 195.00. It does not end the outer transaction; COMMIT does that.

RELEASE after_first removes the savepoint without undoing the changes after it. It is not an independent permanent commit inside the outer transaction. PostgreSQL also lets ROLLBACK TO a savepoint established before an error clear the failed state and continue from that point.

Check whether structure changes can roll back

SQLite and PostgreSQL allow CREATE TABLE, ALTER TABLE and DROP TABLE inside transactions. For example, BEGIN; DROP TABLE orders; ROLLBACK; restores the table and its rows.

Ordinary permanent-table DDL in MySQL and Oracle causes implicit commits. A CREATE TABLE, ALTER TABLE, DROP TABLE or TRUNCATE TABLE can commit earlier pending changes as well as perform its own structural operation. A later rollback cannot undo that earlier work. Do not assume that a transaction around a script makes all database dialects behave alike.

For a risky data change, begin the transaction, run the write, inspect its values and count, and then commit or roll back. For example, inspect lighting prices after UPDATE products SET price = ROUND(price * 1.10, 2) WHERE category = 'lighting';. Complete the decision promptly so the transaction does not keep other work waiting.

ALTER TABLE changes an existing table's structure while preserving its rows. UPDATE changes the values those rows hold. Adding a discount field needs ALTER TABLE; raising the discounts already stored needs UPDATE.

Add and rename fields

ALTER TABLE customers ADD COLUMN phone TEXT;
ALTER TABLE customers RENAME COLUMN phone TO mobile;
ALTER TABLE customers RENAME TO clients;

SQLite and PostgreSQL share these forms. A new column gives existing rows its default, or NULL when there is none. ADD COLUMN active INTEGER NOT NULL DEFAULT 1 gives each current row an active value of 1. Adding email TEXT NOT NULL with no default fails on a populated table because the existing rows would lack a required value.

SQLite supports RENAME COLUMN from 3.25.0 and DROP COLUMN from 3.35.0. When renaming, update application code and saved queries that refer to the old name. SQL outside the database cannot discover the new name automatically.

Fill data before requiring it

In PostgreSQL, add a nullable column, fill the existing rows with suitable values, and only then require it:

ALTER TABLE customers ADD COLUMN email TEXT;
UPDATE customers SET email = 'unknown@example.com'
WHERE email IS NULL;
ALTER TABLE customers ALTER COLUMN email SET NOT NULL;

This illustrates filling a required field; it does not declare email unique. A real unique email column needs distinct valid values. SET NOT NULL fails if any missing values remain. PostgreSQL also supports changing a type with ALTER COLUMN price TYPE NUMERIC(10, 2), setting a default and adding a named check. Existing rows must satisfy a newly added rule.

Remove a field or rebuild a table

ALTER TABLE customers DROP COLUMN mobile; removes the field and all its stored values. Dependencies can block the operation. SQLite rejects dropping a primary-key, unique, indexed or foreign-key column, or one used by another schema object. PostgreSQL can remove dependent objects with CASCADE, including a view that uses the column.

SQLite cannot change an existing column's type or add primary-key, unique, foreign-key or check constraints to existing columns with a simple ALTER. Version 3.53.0 adds ALTER COLUMN ... SET NOT NULL and DROP NOT NULL, not general constraint editing. A newly added column can have a check or permitted reference, but cannot have PRIMARY KEY or UNIQUE; with foreign keys on, an added reference needs a NULL default.

For a broader SQLite change:

1. Record the indexes, triggers and views that must be recreated. If foreign keys are on, switch them off before the transaction.
2. Start a transaction and create a new table with the desired rules.
3. Copy rows using explicit columns in INSERT INTO new_products (...) SELECT ... FROM products;.
4. Drop the old table and rename the new one to products.
5. Recreate dependent objects and run PRAGMA foreign_key_check;. Resolve any reported problems before committing.
6. Commit and restore foreign key enforcement afterward if it was on before.

MySQL changes definitions with MODIFY COLUMN; SQL Server uses ALTER COLUMN. Translate a structural change into the target database's supported syntax before running it.

DROP TABLE removes the container as well as its contents. After DROP TABLE archived_orders;, queries cannot read that table because it no longer exists. Its constraints, indexes and triggers are removed with it.

Choose the level of removal

DROP TABLE IF EXISTS archived_orders; avoids an error if the table is absent. It still removes an existing table and its data. A setup script that drops and recreates tables is destructive when run on a database containing records you want to keep.

Respect links between tables

Drop children before parents, reversing the usual creation order. In PostgreSQL, dropping customers while an order foreign key depends on it fails. DROP TABLE customers CASCADE; also removes the dependent foreign key and any dependent views. The order table and its rows remain, but the customer relationship is no longer enforced.

This PostgreSQL CASCADE is different from a foreign key's ON DELETE CASCADE. The first removes dependent database objects during a structure change. The second removes child rows when a parent row is deleted. Knowing which action is in use prevents an incorrect prediction about the remaining data.

With SQLite foreign key enforcement on, dropping a table first performs an implicit deletion of its rows. Referring children can block it. If the relationship declares ON DELETE CASCADE, those child rows can be deleted; SET NULL can clear their links. A drop therefore needs the same attention to references as a delete, as well as attention to the table structure being removed.

Preserve a recovery path

Before removing or rebuilding a table with real data, preserve a usable copy. A quick internal data copy is:

CREATE TABLE products_backup AS
SELECT * FROM products;

In SQLite and PostgreSQL, this preserves the selected data but not the original constraints. It does not serve as a full reconstruction of the table definition. An export or database backup keeps recovery data outside the table being changed.

SQLite and PostgreSQL also allow a drop to be tested inside a transaction:

BEGIN;
DROP TABLE archived_orders;
ROLLBACK;

The rollback restores the table and its rows because the drop was not committed. Once committed, the drop cannot be reversed by issuing ROLLBACK later. In databases with implicit DDL commits, wrapping the drop in transaction syntax does not supply this undo opportunity. Inspect the target database and the tables involved before running a rebuilding script.