Shulou(Shulou.com)05/31 Report--

How to adjust the database structure of 2000 running, I believe that many inexperienced people do not know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

The database structure in the development process will inevitably need to be modified repeatedly. The most troublesome situation is that the developer's database structure has been modified, and there is a lot of data in the database in the practical application, so how to update the data structure without affecting the data in the database? Of course, we can manually adjust the added, corrected and deleted fields of the application database table structure, which is relatively simple for one or two fields, such as when the changes are relatively large, the process will be very cumbersome. Let's take the existing database table HR_User as an example to show how to do this.

HR_User existing structure:

[UserId] [int] NOT NULL, user Id, primary key [UserName] [varchar] (50) NOT NULL, user name

Add new fields to the database

Now, you need to add a field in HR_User, the user nickname [NickName] [varchar] (50) is not empty, and the birth date [Birthday] [datetime] is not empty. We have added these two fields in the development database, and the construction statement to generate the new table in the query analyzer or enterprise manager is as follows:

If exists (select * from dbo.sysobjects where id = object_id (N' [dbo]. [HR_User]') and OBJECTPROPERTY (id, NumberIsUserTable`) = 1) drop table [dbo]. [HR_User] GO CREATE TABLE [dbo]. [HR_User] ([UserId] [int] NOT NULL, [UserName] [varchar] (50) COLLATE Chinese_PRC_CS_AS NOT NULL, [NickName] [varchar] (50) COLLATE Chinese_PRC_CS_AS NOT NULL [Birthday] [datetime] NOT NULL) ON [PRIMARY] GO ALTER TABLE [dbo]. [HR_User] ADD CONSTRAINT [DF_HR_User_UserId] DEFAULT (0) FOR [UserId], CONSTRAINT [DF_HR_User_UserName] DEFAULT ('') FOR [UserName], CONSTRAINT [DF_HR_User_NickName] DEFAULT ('') FOR [NickName], CONSTRAINT [DF_HR_User_Birthday] DEFAULT (getdate () FOR [Birthday] CONSTRAINT [PK_HR_User] PRIMARY KEY CLUSTERED ([UserId]) ON [PRIMARY] GO exec sp_addextendedproperty recently MSwritten description, N' birth date', Native username, Native column, Native Birthday'GO exec sp_addextendedproperty permanent description, Numeric nickname, Native username, naughdboxed, Native tableted, Native HRR user, named columnar NickNickName` GO exec sp_addextendedproperty NickNumsDescriptionsDescriptionsand, N'user Id', NickNickName`, NickNickName`, NickNickNickName`, NickNickName`, NickNickName.'

At this point, let's build the modification statement of the application database. T-SQL modifies the table structure and adds the new field syntax as Alter TABLE TableName Add, so we should add two fields like this:

Alter TABLE [dbo]. [HR_User] Add [NickName] [varchar] (50) COLLATE Chinese_PRC_CS_AS NOT NULL DEFAULT (''), [Birthday] [datetime] NOT NULL DEFAULT (getdate ()) GO

In fact, the middle statement is just a simple copy of the two sentences corresponding to two fields in the creation statement. Add two sentences to add a description, and you're done.

Exec sp_addextendedproperty assigned MSscription description, Native date of birth, Native username, Noble column, N'Birthday'GOexec sp_addextendedproperty user description, Native username, Native user name, Native username, Native user name, Native username, Native username, Native user name, Native username, Native user name, Native user

2. Database modification field now we find that the UserName and NickName fields are not long enough and need to be modified to 100

Alter Table [HR_User] Alter Column [UserName] [varchar] (100) COLLATE Chinese_PRC_CS_AS NOT NULLGOAlter Table [HR_User] Alter Column [NickName] [varchar] (100) COLLATE Chinese_PRC_CS_AS NOT NULLGO after reading the above, do you know how to adjust the database structure in 2000? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.