How to allow null values in a field that was originally NOT NULL in SQL?

Asked

Viewed 93 times

-2

I have a table created this way:

CREATE TABLE usuarios (
    id INT NOT NULL AUTO_INCREMENT,
    nome VARCHAR(64) NOT NULL,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY(id)
);

But I want to change the field usuarios.nome so that, from now on, it allows null values. How the alter table in that case?

mysql sql mariadb
  • 3

    alter table TABELA alter column COLUNA null? https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

2 answers

2


One way to solve it is by using the command alter table ... modify .... For more information see manual 5.7, manual 8.0.

To execute the command below I used the definition of create table that is in your question.

Follow the DDL:

ALTER TABLE usuarios MODIFY COLUMN nome VARCHAR(64) NULL;  
-- Query OK, 0 rows affected (0.01 sec)
-- Records: 0  Duplicates: 0  Warnings: 0

A second way:

ALTER TABLE usuarios MODIFY COLUMN nome VARCHAR(64);

Notice that we have omitted the word NULL in relation to the first.

Now the DML:

INSERT INTO usuarios (nome, email) VALUES(null, '[email protected]');
-- Query OK, 1 row affected (0.01 sec)
INSERT INTO usuarios (nome, email) VALUES('eu', '[email protected]');
-- Query OK, 1 row affected (0.01 sec)

I used mysql 5.6 (via sqlfiddle), 5.7 and 8.0.22 ( @Augusto Vasques partnership)

  • 1

    Example with Mysql 8 https://paiza.io/projects/OPLOST4y9JHEZ1fV2ypjIA?language=mysql if you want to go up to the answer feel free to use.

  • @Augustovasques, I’m going up the solution so thank you.

  • 1

    I looked at the code now is identical, maybe I don’t need to modify it. @Augustovasques

-4

Just remove the NOT NULL

CREATE TABLE usuarios (
    id INT NOT NULL AUTO_INCREMENT,
    nome VARCHAR(64),
    email VARCHAR(255),
    PRIMARY KEY(id)
);

By default, a column can contain NULL values

  • AP wants to modify the fields keeping the data. If you do so you have to delete the data to create a new table.

Browser other questions tagged mysql sql mariadb

You are not signed in. Login or sign up in order to post.