合并拉取请求时的 Jira Automation 规则
Posted by: AJ Welch
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.
超越敏捷开发
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
command, applicable explicitly to rows where the value is currently NULL
. For example, we have a basic table of client
data with name
, email
, and phone
. Currently a few of the records have a NULL
phone
value, which we don’t want to allow:
clientsID name email phone
1 Neville Estes Quisque@diamProin.com 1-843-863-2697
2 Flynn Fry velit@senectus.net
3 Wyatt Schmidt nibh.dolor@sit.co.uk 1-950-895-1847
4 Oleg Hill lacinia.vitae.sodales@acrisusMorbi.edu 1-173-344-1578
5 Randall Bullock eu@lacus.net
6 Lamar White ut.sem@risus.com 1-421-757-4907
7 Fuller Hill Vivamus.nisi@tempor.ca 1-178-437-8281
8 Ulysses Boyle sem.mollis.dui@Integeraliquamadipiscing.net 1-535-515-1494
9 Paki Palmer nec@euismod.org
10 Kamal Buchanan sapien.gravida@tellusnonmagna.co.uk 1-325-847-4838
Therefore, we can insert a default value for all the phone
values that are currently NULL
with the following statement:
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 default
, 0-000-000-0000
:
clientsID name email phone
1 Neville Estes Quisque@diamProin.com 1-843-863-2697
2 Flynn Fry velit@senectus.net 0-000-000-0000
3 Wyatt Schmidt nibh.dolor@sit.co.uk 1-950-895-1847
4 Oleg Hill lacinia.vitae.sodales@acrisusMorbi.edu 1-173-344-1578
5 Randall Bullock eu@lacus.net 0-000-000-0000
6 Lamar White ut.sem@risus.com 1-421-757-4907
7 Fuller Hill Vivamus.nisi@tempor.ca 1-178-437-8281
8 Ulysses Boyle sem.mollis.dui@Integeraliquamadipiscing.net 1-535-515-1494
9 Paki Palmer nec@euismod.org 0-000-000-0000
10 Kamal Buchanan sapien.gravida@tellusnonmagna.co.uk 1-325-847-4838
超越敏捷开发
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 phone
column in this example, the statement will look something like this:
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 INSERT
test and trying to insert a new record with the NULL
value in the altered column:
INSERT INTO
clients(name, email, phone)
VALUES
('John Doe', 'jdoe@domain.com', NULL);
If all went according to plan, SQL Server will issue an error stating that the column doesn’t allow NULL
values:
Cannot insert the value NULL into column 'phone', table 'library.dbo.clients'; column does not allow nulls. INSERT fails. [SQL State=23000, DB Errorcode=515]