Mysql 컬럼 not null 변경 - Mysql keolleom not null byeongyeong

Changing the data structure of a column in SQL Server from NULL to NOT NULL, thereby disallowing non-null values in that column, is generally performed using the relatively simple ALTER TABLE syntax to appropriately change the column in question.

In this tutorial we’ll examine the important safety precautions necessary when altering existing data in a column, prior to actually issuing any ALTER commands that would potentially cause harm to the table itself.

Understanding the Limitations of Data in NOT NULL Columns

Before any changes are made to your table, it’s important to briefly go over what data can (and cannot) be specified within an existing column that you wish to alter to NOT NULL, ensuring that no row is allowed to have a NULL value in that column.

Most critically, all existing NULL values within the column must be updated to a non-null value before the ALTER command can be successfully used and the column made NOT NULL. Any attempt to set the column to NOT NULL while actual NULL data remains in the column will result in an error and no change will occur.

Unnullifying Existing Column Data

To ensure that there are no NULL values in our column, we’ll use a basic

UPDATE
  clients
SET
  phone = '0-000-000-0000'
WHERE
  phone IS NULL;

clientsID   name            email                                       phone
1           Neville Estes   [email protected]                       1-843-863-2697
2           Flynn Fry       [email protected]                          0-000-000-0000
3           Wyatt Schmidt   [email protected]                        1-950-895-1847
4           Oleg Hill       [email protected]      1-173-344-1578
5           Randall Bullock [email protected]                                0-000-000-0000
6           Lamar White     [email protected]                            1-421-757-4907
7           Fuller Hill     [email protected]                      1-178-437-8281
8           Ulysses Boyle   [email protected] 1-535-515-1494
9           Paki Palmer     [email protected]                             0-000-000-0000
10          Kamal Buchanan  [email protected]         1-325-847-4838

clientsID   name            email                                       phone
1           Neville Estes   [email protected]                       1-843-863-2697
2           Flynn Fry       [email protected]                          0-000-000-0000
3           Wyatt Schmidt   [email protected]                        1-950-895-1847
4           Oleg Hill       [email protected]      1-173-344-1578
5           Randall Bullock [email protected]                                0-000-000-0000
6           Lamar White     [email protected]                            1-421-757-4907
7           Fuller Hill     [email protected]                      1-178-437-8281
8           Ulysses Boyle   [email protected] 1-535-515-1494
9           Paki Palmer     [email protected]                             0-000-000-0000
10          Kamal Buchanan  [email protected]         1-325-847-4838

clientsID   name            email                                       phone
1           Neville Estes   [email protected]                       1-843-863-2697
2           Flynn Fry       [email protected]                          0-000-000-0000
3           Wyatt Schmidt   [email protected]                        1-950-895-1847
4           Oleg Hill       [email protected]      1-173-344-1578
5           Randall Bullock [email protected]                                0-000-000-0000
6           Lamar White     [email protected]                            1-421-757-4907
7           Fuller Hill     [email protected]                      1-178-437-8281
8           Ulysses Boyle   [email protected] 1-535-515-1494
9           Paki Palmer     [email protected]                             0-000-000-0000
10          Kamal Buchanan  [email protected]         1-325-847-4838

clientsID   name            email                                       phone
1           Neville Estes   [email protected]                       1-843-863-2697
2           Flynn Fry       [email protected]                          0-000-000-0000
3           Wyatt Schmidt   [email protected]                        1-950-895-1847
4           Oleg Hill       [email protected]      1-173-344-1578
5           Randall Bullock [email protected]                                0-000-000-0000
6           Lamar White     [email protected]                            1-421-757-4907
7           Fuller Hill     [email protected]                      1-178-437-8281
8           Ulysses Boyle   [email protected] 1-535-515-1494
9           Paki Palmer     [email protected]                             0-000-000-0000
10          Kamal Buchanan  [email protected]         1-325-847-4838

clientsID   name            email                                       phone
1           Neville Estes   [email protected]                       1-843-863-2697
2           Flynn Fry       [email protected]                          0-000-000-0000
3           Wyatt Schmidt   [email protected]                        1-950-895-1847
4           Oleg Hill       [email protected]      1-173-344-1578
5           Randall Bullock [email protected]                                0-000-000-0000
6           Lamar White     [email protected]                            1-421-757-4907
7           Fuller Hill     [email protected]                      1-178-437-8281
8           Ulysses Boyle   [email protected] 1-535-515-1494
9           Paki Palmer     [email protected]                             0-000-000-0000
10          Kamal Buchanan  [email protected]         1-325-847-4838

clientsID     name              email                                         phone
1             Neville Estes     [email protected]                         1-843-863-2697
2             Flynn Fry         [email protected]
3             Wyatt Schmidt     [email protected]                          1-950-895-1847
4             Oleg Hill         [email protected]        1-173-344-1578
5             Randall Bullock   [email protected]
6             Lamar White       [email protected]                              1-421-757-4907
7             Fuller Hill       [email protected]                        1-178-437-8281
8             Ulysses Boyle     [email protected]   1-535-515-1494
9             Paki Palmer       [email protected]
10            Kamal Buchanan    [email protected]           1-325-847-4838

Therefore, we can insert a default value for all the

clientsID   name            email                                       phone
1           Neville Estes   [email protected]                       1-843-863-2697
2           Flynn Fry       [email protected]                          0-000-000-0000
3           Wyatt Schmidt   [email protected]                        1-950-895-1847
4           Oleg Hill       [email protected]      1-173-344-1578
5           Randall Bullock [email protected]                                0-000-000-0000
6           Lamar White     [email protected]                            1-421-757-4907
7           Fuller Hill     [email protected]                      1-178-437-8281
8           Ulysses Boyle   [email protected] 1-535-515-1494
9           Paki Palmer     [email protected]                             0-000-000-0000
10          Kamal Buchanan  [email protected]         1-325-847-4838

UPDATE
  clients
SET
  phone = '0-000-000-0000'
WHERE
  phone IS NULL;

Now our NULL values have all been replaced with the value we consider the

clientsID   name            email                                       phone
1           Neville Estes   [email protected]                       1-843-863-2697
2           Flynn Fry       [email protected]                          0-000-000-0000
3           Wyatt Schmidt   [email protected]                        1-950-895-1847
4           Oleg Hill       [email protected]      1-173-344-1578
5           Randall Bullock [email protected]                                0-000-000-0000
6           Lamar White     [email protected]                            1-421-757-4907
7           Fuller Hill     [email protected]                      1-178-437-8281
8           Ulysses Boyle   [email protected] 1-535-515-1494
9           Paki Palmer     [email protected]                             0-000-000-0000
10          Kamal Buchanan  [email protected]         1-325-847-4838

ALTER TABLE
  clients
ALTER COLUMN
  phone
    NVARCHAR(20) NOT NULL;

clientsID   name            email                                       phone
1           Neville Estes   [email protected]                       1-843-863-2697
2           Flynn Fry       [email protected]                          0-000-000-0000
3           Wyatt Schmidt   [email protected]                        1-950-895-1847
4           Oleg Hill       [email protected]      1-173-344-1578
5           Randall Bullock [email protected]                                0-000-000-0000
6           Lamar White     [email protected]                            1-421-757-4907
7           Fuller Hill     [email protected]                      1-178-437-8281
8           Ulysses Boyle   [email protected] 1-535-515-1494
9           Paki Palmer     [email protected]                             0-000-000-0000
10          Kamal Buchanan  [email protected]         1-325-847-4838

Alter the Column Data Structure

Now that there are no NULL values any longer, we can issue our ALTER statement to update the column so all future additions do not allow NULL values. Since we’re altering the

clientsID   name            email                                       phone
1           Neville Estes   [email protected]                       1-843-863-2697
2           Flynn Fry       [email protected]                          0-000-000-0000
3           Wyatt Schmidt   [email protected]                        1-950-895-1847
4           Oleg Hill       [email protected]      1-173-344-1578
5           Randall Bullock [email protected]                                0-000-000-0000
6           Lamar White     [email protected]                            1-421-757-4907
7           Fuller Hill     [email protected]                      1-178-437-8281
8           Ulysses Boyle   [email protected] 1-535-515-1494
9           Paki Palmer     [email protected]                             0-000-000-0000
10          Kamal Buchanan  [email protected]         1-325-847-4838

ALTER TABLE
  clients
ALTER COLUMN
  phone
    NVARCHAR(20) NOT NULL;

Verify Altered Nullability

Once the alteration to your column is made, it is a good practice to verify the column no longer allows any NULL values by running a simple

ALTER TABLE
  clients
ALTER COLUMN
  phone
    NVARCHAR(20) NOT NULL;

INSERT INTO
  clients(name, email, phone)
VALUES
  ('John Doe', '[email protected]', NULL);

If all went according to plan, SQL Server will issue an error stating that the column doesn’t allow NULL values: