TRAINING | DAY 2

ALTER, NULL, Constraints & UPDATE / DELETE

Changing a table without losing data, guarding it with constraints, and editing rows with a WHERE filter.

1. The Crux in 60 Seconds

ALTER keeps your data

ALTER TABLE (a DDL command) changes a column's type, adds a column or drops one, without the data loss of DROP + CREATE.

NULL means unknown

It is not a string and not comparable: neither less than nor greater than anything. 'null' in quotes is just text.

Constraints guard the data

NOT NULL, CHECK, UNIQUE, DEFAULT and PRIMARY KEY decide what may enter a column; violations make the statement fail.

Filter before you change

UPDATE and DELETE (both DML) hit every row unless you add a WHERE condition.

2. Foundational Concepts & Comparisons

DROP TABLE + CREATE TABLE

Lets you redefine everything, even column position
Loses all existing data in the table
CREATE on an existing name fails ("already an object named..."), so drop first
DROP always works, with or without data, and takes no WHERE

ALTER TABLE

Changes the definition in place; rows stay
Only compatible changes are allowed and nothing may be truncated
New columns are always appended at the end
Works the same on tables created by CREATE or imported from Excel: "a table is a table"

Recap of Day 1 (instructor's opening)

DDL: CREATE TABLE, DROP TABLE; changes the definition
DML: INSERT, DELETE; manipulates data
DQL: SELECT; only queries data
Also revisited: choosing columns, ORDER BY, schemas
From here most classes focus on the SELECT statement (WHERE, aggregation, advanced topics start from the next class)

Warm-up Q&A

Case: data is stored case-sensitively, but SQL Server compares case-insensitively by default when you query; the setting can be changed (instructor to confirm how). Most other databases are case-sensitive by default, he said.
TOP vs LIMIT: same idea. SQL Server accepts TOP only; Redshift accepts both.
SELECT 1: returns the value 1 once per row (four rows give four 1s); not useful in place of *.
Last N rows: done with ORDER BY, covered later.

3. Architectural Mechanics: ALTER TABLE

Why: order_date was date; the business later wanted the time too (for example to see which hours bring the most orders). Dropping and recreating would wipe the rows, so the type is altered.

ALTER requestNew type / column

→

Database checksExisting data

→

Compatible, nothing lost?Yes: change applied

→

OtherwiseError, statement terminated

Task	Syntax (as shown)	Notes
Change a data type	`ALTER TABLE t ALTER COLUMN order_date datetime;`	Old rows get time 00:00:00; shown as hour, minute, second, millisecond. New rows can carry time: date, space, `hh:mm:ss`
Add a column	`ALTER TABLE t ADD username varchar(20);`	Needs a data type; no word COLUMN (that is a syntax error); always lands last; old rows are NULL; one column per statement
Drop a column	`ALTER TABLE t DROP COLUMN category;`	No data type needed; the column and its values vanish

Compatibility rules stated

Data present: new type must be compatible with it. date to datetime is fine; integer order_id to date fails ("int is incompatible with date")
Shrinking a varchar below existing data length fails: "String or binary data would be truncated... statement has been terminated"
Growing it works: product name to varchar(20) succeeded because no value was longer
The database refuses any change that would lose data

Interesting experiments

datetime to varchar(20) worked; the string kept the full date and time. Changing it back to datetime also worked
Still, do not make everything varchar: you lose type-based functions, use more space and lose performance
Empty table: the instructor said any type can change to any other, then testing integer-to-date failed even with no rows, and reducing a varchar from 100 to 10 worked. Treat compatibility as required unless you recreate the table

After ALTER, update your INSERTs

Once a column is added, an old INSERT ... VALUES with fewer values fails: "column name or number of supplied values does not match table definition" (for example 6 columns in the table, 5 values supplied). Pass NULL for unknown values, or use a column list (see page 4).

What each test run proved

#	Run	Result
1	ALTER COLUMN to datetime, then SELECT	Success; existing rows show 00:00:00 time
2	INSERT with a time value	Time stored for that row
3	ADD username, then SELECT	New last column, NULL for all earlier rows
4	ALTER order_id to date	Error: int incompatible with date
5	Product name to varchar(5), then varchar(20)	Truncation error, then success
6	Date column with another date format, e.g. day first	Accepted, still shown as year-month-day

Date format notes

Default is year-month-day; stick to one format to avoid month/day confusion. The output format is fixed per server (a SQL Server-level setting), not per column. To display another format, convert to string in the SELECT (taught later) or store the value in a varchar column.

3. Mechanics (continued): NULL and Constraints

NULL

Special keyword meaning unknown; written without quotes
NULL inserted into product name displays differently from the text 'null', which is a normal string value
Not comparable: unknown is neither below nor above 20
Filtering NULLs needs a separate concept, covered later

Why NULL passes a CHECK

A discount column with CHECK (discount < 20) accepts NULL, because the database cannot say the unknown value breaks the rule. A student asked this; the instructor's answer was exactly that.

Constraint = a rule on a column. Without one, any value of the right data type goes in (even a person's name in payment method). Constraints can be written in CREATE TABLE or added later with ALTER TABLE; there is no limit on how many a column can have.

INSERT / UPDATERow arrives

→

Constraint checkNOT NULL, CHECK, UNIQUE, PK

→

PassRow stored

→

FailError; row not stored

Constraint	Syntax shown	Violation message / behaviour
NOT NULL	`order_id integer NOT NULL`	"Cannot insert the value NULL into column order_id... column does not allow nulls". NULL in other columns (e.g. payment method) still works
CHECK	`payment_method varchar(20) CHECK (payment_method IN ('UPI','credit card'))` `discount integer CHECK (discount < 20)`	"The INSERT statement conflicted with the CHECK constraint" naming the column. Works on strings and numbers
UNIQUE	`order_id integer UNIQUE`	"Violation of UNIQUE KEY constraint... cannot insert duplicate key", showing the duplicate value. A table may have several unique keys; one NULL is allowed
DEFAULT	`category varchar(20) DEFAULT 'menswear'`	Fills the value when the column is omitted from a column-list INSERT

Inspecting constraints you did not write

Object Explorer: expand the table, then Constraints, to see the name and expression
Right-click the table, Script Table as, CREATE To, New Query Editor: shows the CREATE TABLE plus ALTER TABLE ... WITH CHECK ADD CONSTRAINT lines
Violation messages carry a system-generated name; to choose your own name, add the constraint with ALTER (the instructor's W3Schools example used the CONSTRAINT keyword). Adding constraints needs that keyword; adding a column does not

Constraint tests

Re-running CREATE TABLE on an existing name: "already an object named"; drop first
NOT NULL: insert NULL into order_id gives error; NULL into payment method is fine
CHECK: 'internet banking' rejected; 'UPI' accepted
CHECK discount: value above the limit rejected (instructor said "less than 20", then "less than or equal 20")
UNIQUE: second order_id 1 rejected even though other columns differ

CHECK plus DEFAULT together

A student felt CHECK (UPI or credit card) and DEFAULT 'UPI' conflict. The instructor: still valid. CHECK limits what may be supplied; DEFAULT fills in when nothing is supplied. A value such as 'debit card' would still fail. In reality, he added, you would rarely stack them just to show off.

3. Mechanics (continued): DEFAULT in INSERT, PRIMARY KEY

INSERT with a column list

INSERT INTO a_orders
 (order_id, order_date, product_name,
  total_price, payment_method, discount)
VALUES (4,'2022-10-04','shirts',30,'UPI',5);

Without a list the INSERT needs a value for every column (7 columns and 6 values fails), even if DEFAULT exists
Passing '' stores an empty string; NULL stores NULL; skipping with a bare comma is a syntax error
With a list, omitted columns take the DEFAULT, or NULL if none
The list can be in any order; the values must follow the list's order
The word DEFAULT in the values slot also works (found live; new to the instructor)
For 100 columns: pass all of them, or list a subset; the rest become NULL or DEFAULT

PRIMARY KEY

CREATE TABLE a_orders (
  order_id integer,
  product_name varchar(20),
  ...
  PRIMARY KEY (order_id, product_name)
);

= UNIQUE + NOT NULL together, even with no NOT NULL written
Only one primary key per table, but it may combine several columns (composite)
Write composite keys at the end of CREATE TABLE; a key beside a single column covers only that column
Example: one order can hold two products, so order_id repeats; the pair (order_id, product) is unique

Point	UNIQUE	PRIMARY KEY
Duplicates	Not allowed	Not allowed
NULL values	Allowed, but only one NULL (a second one is a violation)	Not allowed
How many per table	Many (e.g. also on discount, price)	Exactly one (can span columns)

Primary key tests and rules

NULL into a primary-key column: "Cannot insert the value NULL... column does not allow nulls"
Rows (3, shirts) and (3, jeans) both go in; repeating (3, jeans) gives "Violation of PRIMARY KEY constraint... cannot insert duplicate key"
Adding a key to a table that already holds NULLs or duplicates fails; fix the data first
Finding a candidate key in a big table: the column whose count of distinct values equals the row count (technique comes with SELECT)
Foreign key is deliberately postponed until joins

Constraints in practice

Analytics systems generally carry no constraints; transactional (online transaction processing) systems do, so updates must respect them. Alter-table syntax is not worth memorizing: look it up (W3Schools shows syntax for SQL Server, MySQL and Oracle).

4. DELETE and UPDATE with WHERE (both DML)

DELETE

DELETE FROM a_orders
WHERE order_id = 2;   -- "1 row affected"

Without WHERE every row goes. WHERE is checked row by row. It can use any column (e.g. product name 'jeans'). DROP TABLE has no WHERE: the table itself disappears.

UPDATE

UPDATE a_orders
SET discount = 10, payment_method = 'UPI'
WHERE order_id = 1;

Without WHERE all rows change (any value to any value). Several columns: comma-separated. If WHERE matches nothing: "0 rows affected".

1. TableWhich table

→

2. WHEREWhich rows

→

3. SETNew values applied

Primary-key columns can be updated as long as the key stays unique: two rows set to order_id 3 worked; changing product_name so (3, jeans) would repeat gave a primary key violation. Also remember: updates must satisfy CHECK constraints. Row order inside a table cannot be rearranged; use ORDER BY when selecting.

5. Best Practices & Golden Rules

Pitfall: ALTER cannot insert mid-table

New columns always go last. For another position: back up the table, drop and recreate it, put the data back (covered later).

Pitfall: quoted null

'null' is text. Unquoted NULL means unknown.

Rule: pick the right data type

Do not default everything to varchar: type-based features, space and speed all suffer.

Rule: one date format

Use year-month-day to avoid month/day confusion.

Rule: WHERE on UPDATE / DELETE

No WHERE means every row is changed or removed.

Rule: fix data first

A key or constraint cannot be added over rows that already violate it.

Trainer Q&A recap

Several columns in one ALTER ADD? No, one statement per column.
Does ALTER work on imported-from-Excel tables? Yes, a table is a table. To export, select all results, Save Results As CSV, or right-click, Copy with Headers, paste into Excel.
Can order of rows in a table be changed? No; use ORDER BY. DROP TABLE works with or without data.
Can a primary key column be updated? Yes, if the key stays unique.
Update two columns? Yes, comma-separated in SET.
Import issues: a CSV import showing only 11 rows was a data problem (checked offline); SQL Server 2022 lacked the native client option, 2019 has it (2022 offers a DB driver option).

6. Numbers & Metrics to Remember

3ALTER TABLE tasks: change type, add, drop column

1Primary key allowed per table

7Columns in the constraints demo table

20varchar length that fit the data (varchar(5) was truncated)

7. Mini Glossary

ALTER TABLE:: DDL command that changes an existing table's structure.
NULL:: unknown value; not a string, not comparable.
Constraint:: rule limiting which values a column may hold.
CHECK:: constraint with a condition, such as IN or a comparison.

DEFAULT:: value used when a column is omitted from an INSERT.
Composite key:: a primary key made of more than one column.
Truncation:: cutting data to fit a smaller size; ALTER refuses it.
WHERE:: filter selecting which rows a statement touches.

8. Quick Self-Test

Why use ALTER TABLE instead of dropping and recreating, and which command family is it in?
Where does a newly added column appear, and how do old rows look in it?
Scenario: after adding a column, your old INSERT with five values fails. Why, and what are two fixes?
Scenario: product_name holds 10-character values and you alter it to varchar(5). What happens?
How is NULL different from 'null', and why does NULL pass CHECK (discount < 20)?
Compare UNIQUE and PRIMARY KEY, and say why a composite key suits an order with two products.
Scenario: a column has a DEFAULT but your INSERT without a column list fails. Why, and how do you get the default applied?
What does UPDATE ... SET discount = 10 do without WHERE, and in what order does the database process table, WHERE and SET?