Create, Modify, and Delete Tables

6,6 Tạo, Sửa đổi, và Xóa Bàn Nó là phổ biến trong các ứng dụng cơ sở dữ liệu để lập trình tạo, chỉnh sửa, và xoá các bảng. Làm thế nào để làm điều này bằng cách sử dụng T-SQL? Kỹ thuật Thực hiện các nhiệm vụ này, bạn sẽ sử dụng CREATE TABLE, ALTER TABLE,
Nội dung trích xuất từ tài liệu:
Create, Modify, and Delete Tables 6.6 Create, Modify, and Delete TablesIt is common in database applications to programmatically create, modify, and deletetables. How do I do this using T-SQL?TechniqueTo perform these tasks, you will use the CREATE TABLE, ALTER TABLE, and DROPTABLE T-SQL statements. With these statements, you can handle any requirements thatyour application might have. Look at these statements one at a time.Creating a Table Using CREATE TABLEWith the CREATE TABLE statement, not only can you specify columns and their datatypes, but you also can specify indexes, check constraints, and other table levelproperties. For this How-To, you will be use the following T-SQL statement:CREATE TABLE ListsExample( ListID smallint IDENTITY(1, 1) PRIMARY KEY CLUSTERED, LastName varchar(50) NOT NULL, FirstName varchar(50) NOT NULL, Age smallint , DateEntered datetime NOT NULL DEFAULT GetDate() CHECK (DateEntered The example T-SQL statement used for modifying the table in this How-To is prettysimple in that it adds a column and removes (drops) a column:ALTER TABLE ListsExample ADD MyNewColumn varchar(30)ALTER TABLE ListsExample DROP COLUMN AgeYou can perform quite a few other tasks with this statement. You can even see by thesyntax displayed here that you can handle many tasks, including dropping constraints.ALTER TABLE table{ [ ALTER COLUMN column_name { new_data_type [ ( precision [ , scale ] ) ] [ COLLATE < collation_name > ] [ NULL | NOT NULL ] | {ADD | DROP } ROWGUIDCOL } ] | ADD { [ < column_definition > ] | column_name AS computed_column_expression } [ ,...n ] | [ WITH CHECK | WITH NOCHECK ] ADD { < table_constraint > } [ ,...n ] | DROP { [ CONSTRAINT ] constraint_name | COLUMN column } [ ,...n ] | { CHECK | NOCHECK } CONSTRAINT { ALL | constraint_name [ ,...n ] } | { ENABLE | DISABLE } TRIGGER { ALL | trigger_name [ ,...n ] }}You can do even more. Look at the Books Online for SQL Server to see completecoverage of this statement.Deleting a Table Using the DROP TABLE StatementThis statement is the easiest, and its a one liner:DROP TABLE ListsExampleHowever, you need to keep some things in mind when you are trying to drop a table: • You cant use the DROP TABLE statement when the table is used in a relationship and is referenced in the FOREIGN KEY constraint. You will need to drop the other table or the constraint. • You will need to be the administrator or owner of the table to be able to use the DROP TABLE statement. • You cant use the DROP TABLE statement on system tables.StepsOpen and run the Visual Basic .NET-Chapter 6 solution. From the main form, click onthe button with the caption How-To 6.6 (see Figure 6.7). 1. Create a Windows Form. Then place the controls listed in Table 6.6 with the following properties set, as displayed in Figures 6.7. Table 6.6. Control Property Settings for This How-To Object Property Setting Label Text SQL Statement to Create a Table Label Name lblCreateTable Button Name btnCreateTable Text Create Table Label Text SQL Statement to Modify a Table Label Name lblModifyTable Button Name btnModifyTable Text Modify Table Label Text SQL Statement to Delete a Table Label Name lblDeleteTable Button Name btnDeleteTable Text Delete Table 2. Add the code in Listing 6.11 to the Load event of the form. (Double-click on the form to bring up the code.) This routine creates the SQL statements for all three tasks and assigns them to the appropriate label for display. Listing 6.11 frmHowTo6_6.vb: Loading the SQL Statements into the Appropriate Labels Private Sub frmHowTo6_6_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load -- Build the SQL String for Creating a Table Dim strSQL As String strSQL = CREATE TABLE ListsExample & vbCrLf strSQL &= ( & vbCrLf strSQL &= ListID smallint IDENTITY(1, 1) & _ PRIMARY KEY CLUSTERED, & vbCrLf strSQL &= LastName varchar(50) NOT NULL, & vbCrLf strSQL &= FirstName varchar(50) NOT NULL, & vbCrLf strSQL &= Age smallint , & vbCrLf strSQL &= DateEntered datetime NOT NULL DEFAULT GetDate() & vbCrLf strSQL &= CHECK (DateEntered Private Sub btnCreateTable_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnCreateTable.Click If PerformTask(Me.lblCreateTable.Text) Then MessageBox.Show(Table Created Successfully, & _ _Look for ListsExample & Table in Northwind Database in Server Explorer, _ Action Performed) End If End Sub4. In the class module of the form created for this How-To, create the code displayed in Listing 6.13 for the PerformTask() function. This code creates a Connection object. Next, create a Command object that is based on the string passed in strSQL. Open the connection and execute the command. Notice that the execution of the command has been wrapped in the Try..Catch..End Try code block to make sure the command is executed correctly; if its not, a message is displayed. Listing 6.13 frmHowTo6_6.vb: Executing the SQL Statement Passed in Using strSQL Function PerformTask(ByVal strSQL As String) As Boolean Dim cnn As New OleDb.OleDbConnection(BuildCnnStr((local), Northwind)) ...