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
Nội dung trích xuất từ tài liệu:
Using SQL phần 4Removing Rows From a TableYou use the DELETE statement to remove rows from a table. When removing a row, youspecify 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 dont want to remove all the rows from the table. Typically, youll specify the value for the primary key in your WHERE clause.The following DELETE statement removes the row from the Customers table where theCustomerID is CRCOM:DELETE FROM CustomersWHERE CustomerID = CRCOM;Figure 3.24 shows this DELETE statement, along with a SELECT statement thatdemonstrates that the row has been removed.Figure 3.24: Using an UPDATE statement to remove a row from the Customers tableIn the next section, youll learn how the database software maintains the integrity of theinformation stored in the database.Maintaining Database IntegrityThe database software ensures that the information stored in the tables is consistent. Intechnical terms, it maintains the integrity of the information. Two examples of this are thefollowing: • 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.Lets take a look at what happens when you try to insert a row into a table with a primarykey that already exists. The following INSERT statement attempts to add a row to theCustomers table with a CustomerID of ALFKI (a row with this primary key alreadyexists 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, youll get the following error message fromthe 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 containsthe primary key value ALFKI. The message tells you that the primary key specified in theINSERT statement already exists in the Customers table. The constraint namePK_Customers is the name of the table constraint assigned to the primary key when theCustomers table was originally created. At the end, the message indicates that thestatement has been terminated, meaning that the INSERT statement has not beenperformed.Lets take a look at what happens when you try to modify a primary key in a parent tablewith a value that is referenced in a foreign key in a child table. The following UPDATEstatement attempts to modify the CustomerID from ALFKI to ALFKZ in the parentCustomers table (this row is referenced by rows in the child Orders table):UPDATE CustomersSET CustomerID = ALFKZWHERE CustomerID = ALFKI;If you attempt to run this UPDATE statement, youll get the following error message:UPDATE statement conflicted with COLUMN REFERENCE constraintFK_Orders_Customers. The conflict occurred in databaseNorthwind, table Orders, column CustomerID.The statement has been terminated.This UPDATE statement fails because the row containing the primary key value ALFKIis referenced by rows in the Orders table. The message tells you that the new value forthe CustomerID column violates the foreign key constraint on the CustomerID column ofthe Orders table. This constraint is named FK_Orders_Customers.Also, you cant remove a row from a parent table that is referenced by a row in a childtable. For example, the following DELETE statement attempts to remove the row fromthe Customers table where the CustomerID column equals ALFKI (this row is referencedby rows in the Orders table):DELETE FROM CustomersWHERE CustomerID = ALFKI;If you attempt to run this DELETE statement, youll get the same error message that wasshown for the previous UPDATE statement. This DELETE statement fails because theOrders table contains rows that reference the row in the Customers table, and removingthis row would make the database inconsistent because the rows in the Orders tablewouldnt reference a valid row.Grouping SQL StatementsBy default, when you run an INSERT, UPDATE, or DELETE statement, SQL Serverpermanently records the results of the statement in the database. This might not always beyour desired result. For example, in the case of a banking transaction, you might want towithdraw money from one account and deposit it into another. If you had two separateUPDATE statements that performed the withdrawal and deposit, then you would want tomake the results of each UPDATE statement permanent only as one unit. If eitherUPDATE failed for some reason, then you would want to undo the results of bothUPDATE 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 theSQL statements in that transaction as one unit. For example, the two UPDATEstatements in the previous banking example could be placed into a transaction, and thenyou could commit or roll back that transaction as one unit, depending on whether both ofthe UPDATE statements succeeded.You start a transaction using the BEGIN TRANSACTION statement or the shorthandversion, BEGIN TRANS. You then perform your SQL statements that make up thetransaction. To commit the transaction, you perform a COMMIT TRANSACTIONstatement or one of the shorthand versions, COMMIT TRAN ...