Remove Column record does not work with Change Statement

Question

Hi, For Intro to Join exercise, when I typed in the following codes, the dept_no still shows . could you help to figure out what is going on there? Thank you. ALTER TABLE departments_dup CHANGE COLUMN dept_no dept_no CHAR(4) NULL; Thanks, Jo Zhou

Answer 1

Hi Jo!
Thanks for reaching out.
This is a very interesting question!
Basically, this modification doesn't turn the dept_no into a Unique Key. Instead, it adds NULL as a default value. Moreover, it doesn't affect the records that have been inserted so far in the table.
That's why if you executed again the following query, for instance:

INSERT INTO departments_dup
(
dept_no,
dept_name
)
SELECT 
*
FROM 
departments;

you'll manage to insert all for records with all department numbers available. However, if you only try to insert a record containing a value for the department name, then the value in the dept_no field will be null.

INSERT INTO departments_dup (dept_name) VALUES ('Finance');

Hope this helps.
Best,
Martin

Remove Column record does not work with Change Statement

Submit an answer