CODEWITHSUNDEEP
mysql
SkyVar
SkyVar

Reputation: 351

Changing defaults

Ok let me rephrase. This is what my table is described as.

    mysql> describe department;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| dnumber | int(1)      | NO   | PRI | NULL    |       |
| dname   | varchar(15) | YES  |     | NULL    |       |
| mgrssn  | varchar(9)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

This is what I need my table to be described as:

+--------------+-------------+------+-----+---------+
| Field        | Type        | Null | Key | Default |
+--------------+-------------+------+-----+---------+
| dnumber      | int(1)      | NO   | PRI | NOT NULL|
| dname        | varchar(15) | YES  |     | NULL    |
| mgrssn       | varchar(9) | YES  |     | NULL    |
+--------------+-------------+------+-----+---------+

How do I change the default of dnumber to make it not null from null?

Upvotes: 1

Views: 38

Answers (1)

Daniel Miladinov
Daniel Miladinov

Reputation: 1592

Since dnumber is the primary key of the department table, it cannot have a null value.

So, what will happen if you actually try to insert the following?

INSERT INTO department(dname, mgrssn) VALUES ("test", "123456789");

You'd expect the insert to fail with an error. But no, it succeeds, (albeit, with a warning):

mysql> INSERT INTO department(dname, mgrssn) VALUES ("test", "123456789");
Query OK, 1 row affected, 1 warning (0.04 sec)

What just happened? Well, turns out to be one of MySQL's many gotchas, where "NULL is NOT NULL". Today's MySQL manual says pretty much the same thing:

If a column definition includes no explicit DEFAULT value, MySQL determines the default value as described in Section 11.5, “Data Type Default Values”.

So, what actually got inserted? Let's take a look:

mysql> select * from department;
+---------+-------+-----------+
| dnumber | dname | mgrssn    |
+---------+-------+-----------+
|       0 | test  | 123456789 |
+---------+-------+-----------+
1 row in set (0.00 sec)

What happened here was that MySQL took that insert statement that was implicitly trying to insert NULL into dnumber and quietly converted it to the default value for INT, or 0.

There's also a really good video that describes this and other strange things MySQL does (as compared to PostgreSQL) in more detail.

Finally, let me add one more piece of advice - since it is the primary key, it's a really good practice to make dnumber an auto-incrementing column, like this:

mysql> ALTER TABLE department MODIFY dnumber int(1) AUTO_INCREMENT;
Query OK, 1 row affected (0.27 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc department;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| dnumber | int(1)      | NO   | PRI | NULL    | auto_increment |
| dname   | varchar(15) | YES  |     | NULL    |                |
| mgrssn  | varchar(9)  | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

That way, future inserts into department won't always try to insert another 0 for dnumber causing a duplicate key error, but will instead use the next lowest available integer value, e.g. 1, then 2, then 3, etc.

Hope this helps!

Upvotes: 1

Related Questions