Shulou(Shulou.com)06/01 Report--

SQL Prompt is a practical SQL grammar prompt tool. SQL Prompt automatically retrieves according to the object name, syntax and code snippet of the database to provide users with appropriate code choices. Automatic script settings make the code easy to read-especially useful when developers are not familiar with scripts. SQL Prompt can be installed and used, which can greatly improve the coding efficiency. In addition, users can customize it as needed to make it work in the desired way.

This tutorial explains the problems you might encounter when adding a non-nullable column to an existing table or changing a column that contains a null value to a non-nullable column. He demonstrates a migration script that can securely deploy such changes. This article is the second half of the tutorial, immediately following the previous article (click here to see) ~

Listing 5: update existing rows with default values before creating column NOT NULL

So, it works very well.

Roll out all changes

Now is the time to extend all these changes to people with only design version 1, no Location tables, and CountingWords without TheLanguage columns and empty Word columns.

Introduce a new Location table because it is the first version of the table and is not a real problem (see listing 3). However, extending the change to the new CountingWords table requires the addition of a Word column where TheLangauge does not allow NULLs, and NOT NULL changes the column in both cases to avoid the problems we have already discussed.

We also want our migration script to work properly, whether we are updating the existing v1 CountingWords or whether we need to build the v2 of the table from scratch. In addition, if the script is run again unexpectedly, we do not want the script to cause any harm or trigger errors.

In addition, in either case, we need to set the TheLangauge column CountingWords to FOREIGN KEY automatic update in response to the update or deletion of the parent key, as well as to change the PRIMARY KEY. As a final step, we will add 11-20 old Welsh words that we didn't know before.

The following is a migration script that migrates CountingWords from v1 to v2, or creates v2 from scratch, which will do no harm if it is rerun unexpectedly. Before trying, either delete the CountingWords table or rerun listing 1 to re-establish the v1 of the table.

Listing the secure migration script for 6:CountingWords v2

It would be much easier if not to ensure that the migration script works in all cases. I just hate build scripts that can only be run under certain circumstances.

Now let's test it by adding one to twenty counting words in different languages or regions:

DECLARE @ Language NVARCHAR (30) = 'West Cumbrian';IF NOT EXISTS (SELECT * FROM dbo.Location WHERE TheLanguage LIKE @ Language) INSERT INTO dbo.Location (TheLanguage) VALUES (@ Language) / * and now we can add in any other ways of counting up to twenty that we want * / IF NOT EXISTS (SELECT * FROM CountingWords WHERE Thelanguage LIKE @ Language) INSERT INTO CountingWords (TheValue, Word, TheLanguage) SELECT TheValue, word, @ Language FROM (VALUES ('yan', 1), (' tyan', 2), ('tethera', 3), (' methera', 4), ('pimp', 5), (' sethera', 6), ('lethera', 7)) ('hovera', 8), (' dovera', 9), ('dick', 10), (' yan-a-dick', 11), ('tyan-a-dick', 12), (' tethera-dick', 13), ('nethera-dick', 14), (' bumfit', 15), ('yan-a-bumfit', 16), (' tyan-a-bumfi tweets, 17), ('tithera-bumfit', 18) ('methera-bumfit', 19), (' giggot', 20) AS f (word, TheValue) GO

Listing 7 Pluto Yanwon TyanMagi Tetheramuri-count to 20 in West Cumbria County

Now I've changed my mind. It should not be called "Old Wales", but "Old Wales". What a pity, but we can now test our foreign key constraints.

UPDATE dbo.Location SET TheLanguage = 'Archaic Welsh' WHERE TheLanguage LIKE' Old Welsh' SELECT * FROM locationSELECT * FROM CountingWords

Listing 8: cascading updates after changing the language

Like magic, all references have been changed. Now I have a database I can use!

I provide a FillCountingWordsTable script that contains a complete count word set of 47 record locations and languages that can be used for a wider range of testing. Apologizing to friends and relatives in the United States, I left out several Indian tribes that rhymed with the same count. It turns out that they were woven by British colonists who thought the words used to calculate stitches were part of magic.

Conclusion

We have just created a fairly elaborate demonstration of how to avoid some of the problems of changing tables with populated data. In this example, the problem involves the use of null values, which occur when you try to add a new column that cannot accept null values, or when NOT NULL changes an existing nullable column to a column when an existing null value exists in the column. In the first case, you just need DEFAULT to add a constraint to the column first, using a value other than NULL, and in the second case, NULL first deletes the value by updating the table.

These are techniques for making changes to existing tables, which is why I elaborated on how to demonstrate how to operate these two operations as part of a flexible script, which can be run, whether new or migrated, and can be rerun without any adverse effects.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.