Using SQL phần 4

Chia sẻ: Tuan Nghia | Ngày: | Loại File: PDF | Số trang:8

Thêm vào BST

Báo xấu

87
lượt xem 3
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Removing Rows From a Table You use the DELETE statement to remove rows from a table. When removing a row, you specify the name of the table and the rows to delete using a WHERE clause

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Using SQL phần 4

Removing Rows From a Table You use the DELETE statement to remove rows from a table. When removing a row, you specify the name of the table and the rows to delete using a WHERE clause. Warning If you omit the WHERE clause in a DELETE statement, all rows from the table will be deleted. Make sure you provide a WHERE clause if you don't want to remove all the rows from the table. Typically, you'll specify the value for the primary key in your WHERE clause. The following DELETE statement removes the row from the Customers table where the CustomerID is CRCOM: DELETE FROM Customers WHERE CustomerID = 'CRCOM'; Figure 3.24 shows this DELETE statement, along with a SELECT statement that demonstrates that the row has been removed. Figure 3.24: Using an UPDATE statement to remove a row from the Customers table In the next section, you'll learn how the database software maintains the integrity of the information stored in the database. Maintaining Database Integrity The database software ensures that the information stored in the tables is consistent. In technical terms, it maintains the integrity of the information. Two examples of this are the following: • The primary key of a row always contains a unique value.
• The foreign key of a row in the child table always references a value that exists in the parent table. Let's take a look at what happens when you try to insert a row into a table with a primary key that already exists. The following INSERT statement attempts to add a row to the Customers table with a CustomerID of ALFKI (a row with this primary key already exists in the Customers table): INSERT INTO Customers ( CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax ) VALUES ( 'ALFKI', 'Jason Price Company', 'Jason Price', 'Owner', '1 Main Street', 'New York', NULL, '12345', 'USA', '(800)-555-1212', NULL ); If you attempt to run this INSERT statement, you'll get the following error message from the database: Violation of PRIMARY KEY constraint 'PK_Customers'. Cannot insert duplicate key in object 'Customers'. The statement has been terminated. This INSERT statement fails because an existing row in Customers table already contains the primary key value ALFKI. The message tells you that the primary key specified in the INSERT statement already exists in the Customers table. The constraint name PK_Customers is the name of the table constraint assigned to the primary key when the Customers table was originally created. At the end, the message indicates that the statement has been terminated, meaning that the INSERT statement has not been performed. Let's take a look at what happens when you try to modify a primary key in a parent table with a value that is referenced in a foreign key in a child table. The following UPDATE statement attempts to modify the CustomerID from ALFKI to ALFKZ in the parent Customers table (this row is referenced by rows in the child Orders table): UPDATE Customers SET CustomerID = 'ALFKZ' WHERE CustomerID = 'ALFKI'; If you attempt to run this UPDATE statement, you'll get the following error message:
UPDATE statement conflicted with COLUMN REFERENCE constraint 'FK_Orders_Customers'. The conflict occurred in database 'Northwind', table 'Orders', column 'CustomerID'. The statement has been terminated. This UPDATE statement fails because the row containing the primary key value ALFKI is referenced by rows in the Orders table. The message tells you that the new value for the CustomerID column violates the foreign key constraint on the CustomerID column of the Orders table. This constraint is named FK_Orders_Customers. Also, you can't remove a row from a parent table that is referenced by a row in a child table. For example, the following DELETE statement attempts to remove the row from the Customers table where the CustomerID column equals ALFKI (this row is referenced by rows in the Orders table): DELETE FROM Customers WHERE CustomerID = 'ALFKI'; If you attempt to run this DELETE statement, you'll get the same error message that was shown for the previous UPDATE statement. This DELETE statement fails because the Orders table contains rows that reference the row in the Customers table, and removing this row would make the database inconsistent because the rows in the Orders table wouldn't reference a valid row. Grouping SQL Statements By default, when you run an INSERT, UPDATE, or DELETE statement, SQL Server permanently records the results of the statement in the database. This might not always be your desired result. For example, in the case of a banking transaction, you might want to withdraw money from one account and deposit it into another. If you had two separate UPDATE statements that performed the withdrawal and deposit, then you would want to make the results of each UPDATE statement permanent only as one unit. If either UPDATE failed for some reason, then you would want to undo the results of both UPDATE statements. Note Permanently recording the results of SQL statements is known as a commit, or committing the SQL statements. Undoing the results of SQL statements is known as a rollback, or rolling back the SQL statements. You can group SQL statements into a transaction. You can then commit or roll back the SQL statements in that transaction as one unit. For example, the two UPDATE statements in the previous banking example could be placed into a transaction, and then
you could commit or roll back that transaction as one unit, depending on whether both of the UPDATE statements succeeded. You start a transaction using the BEGIN TRANSACTION statement or the shorthand version, BEGIN TRANS. You then perform your SQL statements that make up the transaction. To commit the transaction, you perform a COMMIT TRANSACTION statement or one of the shorthand versions, COMMIT TRANS or COMMIT. To roll back the transaction, you perform a ROLLBACK TRANSACTION statement or one of the shorthand versions, ROLLBACK TRANS or ROLLBACK. Note By default, transactions are rolled back. You should always explicitly commit or roll back a transaction to indicate what you want to do. Let's take a look at an example. The following transaction consists of two INSERT statements: the first adds a row to the Customers table, and the second adds a row to the Orders table. At the end, the transaction is committed using a COMMIT statement: BEGIN TRANSACTION; INSERT INTO Customers ( CustomerID, CompanyName ) VALUES ( 'SOCOM', 'Steve Orange Company' ); INSERT INTO Orders ( CustomerID ) VALUES ( 'SOCOM' ); COMMIT; Figure 3.25 shows this transaction, along with two SELECT statements that show the two new rows.
Figure 3.25: Using a transaction The next transaction consists of similar INSERT statements, except this time the transaction is rolled back using a ROLLBACK statement. BEGIN TRANSACTION; INSERT INTO Customers ( CustomerID, CompanyName ) VALUES ( 'SYCOM', 'Steve Yellow Company' ); INSERT INTO Orders ( CustomerID ) VALUES ( 'SYCOM' ); ROLLBACK; Because the transaction is rolled back, the two rows added by the INSERT statements are undone. You should check for errors in a transaction before deciding to perform a COMMIT or ROLLBACK because errors do not always stop the next line from processing. To do this in SQL Server, you use the @@ERROR function. This function returns zero whenever a statement is executed and doesn't cause an error. If @@ERROR returns a nonzero value, you know an error occurred. If @@ERROR returns 0, you perform a COMMIT, otherwise you perform a ROLLBACK. You can also assign a name to your transaction in the BEGIN TRANSACTION statement. This is useful as it shows which transaction you are working on.
The following example shows the naming of a transaction, along with the use of the @@ERROR function to determine whether to perform a COMMIT or ROLLBACK: BEGIN TRANSACTION MyTransaction; INSERT INTO Customers ( CustomerID, CompanyName ) VALUES ( 'SYCOM', 'Steve Yellow Company' ); INSERT INTO Orders ( CustomerID ) VALUES ( 'SYCOM' ); IF @@Error = 0 COMMIT TRANSACTION MyTransaction; ELSE ROLLBACK TRANSACTION MyTransaction; Notice that the name of the transaction is MyTransaction, and that this name is used in the COMMIT and ROLLBACK statements. Note You use the IF statement to conditionally execute a statement. You'll learn more about this in Chapter 4, "Introduction to Transact-SQL Programming." Introducing Data Definition Language (DDL) Statements As mentioned earlier, DDL statements allow you to create database structures such as tables and indexes. In this section, you'll learn how to create, alter, and drop a table, and create and drop an index. Creating a Table You create a table using the CREATE TABLE statement. For example, let's say you wanted to store the details for a number of persons in the database. Assume you want to store a person's first name, last name, and date of birth. Let's call this table Persons. You also want to uniquely identify each row in the Persons table using a numeric ID, which acts as the primary key for the table. The following CREATE TABLE statement creates the Persons table: CREATE TABLE Persons ( PersonID int CONSTRAINT PK_Persons PRIMARY KEY, FirstName nvarchar(15) NOT NULL,
LastName nvarchar(15) NOT NULL, DateOfBirth datetime ); You use the CONSTRAINT clause to restrict the values stored in a table or column. You'll notice that the CONSTRAINT clause is used to specify the primary key for the table using the keywords PRIMARY KEY. The primary key is the PersonID column, and this constraint is named PK_Persons. The ID column is an int, meaning that it stores integers. Every row in the Persons table must have a unique number for the PersonID column. The FirstName and LastName columns are nvarchar columns that may store up to 15 characters. Both of these columns are defined using the NOT NULL constraint. NOT NULL indicates that you must supply a value to the column. The default is NULL, meaning that you don't have to supply a value to the column. Note Primary keys always require a value, and are therefore implicitly NOT NULL. The DateOfBirth column is a datetime, meaning that it can store a date and time. This column doesn't have a NOT NULL constraint and therefore uses the default of NULL. Altering a Table You alter an existing table using the ALTER TABLE statement. You can add or drop a column, and add or drop a constraint using the ALTER TABLE statement. For example, the following ALTER TABLE statement adds a column named Address to the Persons table: ALTER TABLE Persons ADD Address nvarchar(50); The Address column is an nvarchar that can store up to 50 characters. The next example drops the Address column from the Persons table: ALTER TABLE Persons DROP COLUMN Address; The next example adds a column named EmployerID to the Persons table, which records the company that a person works for: ALTER TABLE Persons ADD EmployerID nchar(5) CONSTRAINT FK_Persons_Customers REFERENCES Customers(CustomerID);
The EmployerID column is a foreign key to the CustomerID column of the Customers table. The constraint is named FK_Persons_Customers. Dropping a Table You drop a table from the database using the DROP TABLE statement. For example, the following statement drops the Persons table: DROP TABLE Persons; Creating an Index You add an index to a table using the CREATE INDEX statement. An index allows you to potentially find a row more rapidly when you use the column with the index in a WHERE clause. For example, the following CREATE INDEX statement adds an index to the LastName column of the Persons table: CREATE INDEX LastNameIndex ON Persons(LastName); Tip If you frequently use a column in a WHERE clause, you should consider adding an index to that column. Generally, you should create an index on a column only when you find that you are retrieving a small number of rows from a table containing many rows. A good rule of thumb is that an index is useful when you expect any single query to retrieve 10 percent or less of the total rows in a table. This means that the candidate column for an index should be used to store a wide range of values. A good candidate for indexing would be a column containing a unique number for each record, while a poor candidate for indexing would be a column that contains only a small range of numeric codes, such as 1, 2, 3, or 4. This consideration applies to all database types, not just numbers. Normally, the DBA is responsible for creating indexes, but as an application developer, you might know more about the application than the DBA and be able to recommend which columns are good candidates for indexing. Dropping an Index You drop an index from a table using the DROP INDEX statement. For example, the following DROP INDEX statement drops LastNameIndex from the Persons table: DROP INDEX Persons.LastNameIndex;