MySQL Schema is different about the same class definitions. (depends on create table vs alter table)

[Jordan Bae]

Hi, My name is Jordan. When I delete the column, I found some picky points in Django.

MySQL Schema is different about the same class definitions.
MySQL Schemas are different about the nullable column default value.

For example,

1) when it was made by 'create table'

class PhoneBook(models.Model):
    name = models.CharField(max_length=32, null=True, blank=True, default='jordan')
    phone_number = models.CharField(max_length=32, null=True, blank=True)

The above code creates the migrations file as shown below.

operations = [
        migrations.CreateModel(
            name='PhoneBook',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('name', models.CharField(blank=True, default='jordan', max_length=32, null=True)),
                ('phone_number', models.CharField(blank=True, max_length=32, null=True)),
            ],
        ),
]

When Migrate, SQL is executed as shown below.

CREATE TABLE `main_phonebook` (
	`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
	`name` varchar(32) NULL,
	`phone_number` varchar(32) NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2) when it was made by 'alter table'
For the situation where the ‘alter table’ is applied, let's define the model class first as below and then add the name column.

class PhoneBook2(models.Model):
    phone_number = models.CharField(max_length=32, null=True, blank=True)

and I added 'name' column.

class PhoneBook2(models.Model):
    name = models.CharField(max_length=32, null=True, blank=True, default='jordan')
    phone_number = models.CharField(max_length=32, null=True, blank=True)

operations = [
        migrations.AddField(
            model_name='phonebook2',
            name='name',
            field=models.CharField(blank=True, default='jordan', max_length=32, null=True),
        ),
    ]

this operations make this SQL.

ALTER TABLE `main_phonebook2` ADD COLUMN `name` varchar(32) DEFAULT %s NULL ['jordan']
ALTER TABLE `main_phonebook2` ALTER COLUMN `name` DROP DEFAULT []

and table schema is like below.

CREATE TABLE `main_phonebook2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `phone_number` varchar(32) DEFAULT NULL,
  `name` varchar(32),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

We can see that the same code creates different schemas.

+ when i try to remove the column

class PhoneBook(models.Model):
    # name = models.CharField(max_length=32, null=True, blank=True, default='jordan')
    phone_number = models.CharField(max_length=32, null=True, blank=True)


class PhoneBook2(models.Model):
    # name = models.CharField(max_length=32, null=True, blank=True, default='jordan')
    phone_number = models.CharField(max_length=32, null=True, blank=True)

happened like this.

In [2]: p=PhoneBook(phone_number='010-1234-1234')                         

In [3]: p.save()                                                          
INSERT INTO `main_phonebook` (`phone_number`) VALUES (%s) ['010-1234-1234']

In [4]: p2=PhoneBook2(phone_number='010-1234-1234')                       

In [5]: p2.save()                                                         
INSERT INTO `main_phonebook2` (`phone_number`) VALUES (%s) ['010-1234-1234']

!!!!!!!!error!!!!!!!!!!!!!!!
IntegrityError: (1364, "Field 'name' doesn't have a default value")