|
|
|
As mentioned in Tip 64, the DBMS will not erase any database data when you drop a view. However, you still have to be careful that the view you are dropping is not referenced by another view. Some DBMS products let you add the CASCADE or RESTRICT clause to a DROP VIEW statement to control the behavior of the DBMS when you tell the system to DROP a view that is referenced by another view.
If you execute the DROP VIEW statement with the CASCADE clause, the DBMS will not only remove the view you
CREATE VIEW vw_sales_production AS SELECT rep_id, calls, sales, deliveries FROM production
and
CREATE VIEW vw_delivered_sales_commissions (rep_id, deliveries, commission) AS SELECT rep_id, deliveries, deliveries * 150.00 FROM vw_sales_production
when you execute
DROP VIEW vw_sales_production
the DBMS will remove only the VW_SALES_PRODUCTION view from the system tables. If you execute the SELECT statement
SELECT * FROM vw_delivered_sales_commissions
after you DROP the VW_SALES_PRODUCTION view, the DBMS will respond with an error message in the form:
Server: Msg 208, Level 16, State 1, Procedure vw_delivered_sales_commissions, Line 1 Invalid object name 'vw_sales_production'. Server: Msg 4413, Level 16, State 1, Line 1 Could not use view ' vw_delivered_sales_commissions' previous binding errors.
If, on the other hand, you DROP the VW_SALES_PRODUCTION view with the DROP VIEW statement
DROP VIEW vw_sales_production CASCADE
the DBMS will remove both VW_SALES_PRODUCTION and VW_DELIVERED_SALES_COMMISSIONS (which references it) from the system tables.
Conversely, some DBMS products allow you to add the RESTRICT clause to the DROP VIEW statement. The RESTRICT clause will prevent you from dropping a view that is referenced by another view Thus, in the current example, executing the "restricted" DROP VIEW statement
DROP VIEW vw_sales_production RESTRICT
will fail because the view VW_SALES_PRODUCTION view is referenced by the VW_DELIVERED_SALES_COMMISSIONS view.
| Note |
Not all DBMS products provide the CASCADE and RESTRICT clauses for the DROP VIEW statement-MS-SQL Server, for example, does not. As such, check your system documentation to see if you can add the CASCADE or RESTRICT clause to the DROP VIEW statement in your DBMS product. |
|
|
|
|
|
|
Use the INSERT statement to add data to a database table one row at a time. The syntax of the INSERT statement is:
INSERT INTO <table name> [(<column name>...[,<last columnname >])] VALUES (<column value>...[,<last column value>])
This
Although the word INSERT seems to imply that you are placing the new row of data in front of or between existing rows in the table, the INSERT statement merely builds a single row of data that matches the column structure of the table. Once it receives the INSERT statement, the DBMS decides on the physical location in the table for the new row. As a result, a new row of data may end up at the beginning, the end, or somewhere else among the existing rows in a table.
| Note |
As you learned from Tip 6, "Understanding Codd's 12-Rule Relational Database Definition," a relational database must exhibit logical data independence (Rule 9). As such, the actual order of the columns in a row (as long as it is consistent throughout the table) and the order of the rows in the table have no effect on information retrieved when you query the database. Therefore, where the DBMS places a new row of data in a table is unimportant. |
Suppose, for example, that you have an ORDERS table created with the statement:
CREATE orders (order_number INTEGER NOT NULL, customer_number INTEGER NOT NULL, item_number INTEGER NOT NULL, quantity SMALLINT DEFAULT 1, order_date DATETIME, special_instructions VARCHAR(30))
You would use the INSERT statement
INSERT INTO orders (order_number, customer_number, item_number, quantity, order_date, special_instructions) VALUES (1, 10, 1001, 5, '05/18/00', 'keep frozen')
to add an order with order number 1, placed on 05/18/00, by customer 10, for 5 of item 1001, which must be kept frozen to the ORDERS table.
The names of the columns listed after the keyword INSERT must be in the definition of the table into which the DBMS is to insert a row. The
INSERT INTO orders (customer_number, order_number, order_date, item_number, quantity, special_instructions) VALUES (10, 1, '05/18/00', 1001, 5, 'keep frozen')
is equivalent to that given in the
Starting from left to right, the DBMS will take the first value in the VALUES clause and place it into the table column given by the first name in the list of column names; it will take the second value from the list of values and place it in the table column given by the second name in the list of column names, and so on. The list of columns and the list of values must both contain the same number of items. Moreover, the data type of each value in the values list must be compatible with the data type of the corresponding table column into which the DBMS is to place it.
| Note |
Most DBMS products will allow you to omit the list of columns from the INSERT statement. If you don't provide the column list, the DBMS assumes a column list that includes all of the columns in the table in the order in which they appear in the table definition. As such, in the current example
INSERT INTO orders VALUES (1, 10, 1001, 5, '05/18/00', 'keep frozen') is equivalent to:
INSERT INTO orders (order_number, customer_number, item_number, quantity, order_date, special_instructions) VALUES (1, 10, 1001, 5, '05/18/00', 'keep frozen') To get a list of a table's columns and the order in which they appear in each row of the table, without listing any of the table's data, execute the SELECT statement:
SELECT * <table name> WHERE NULL = NULL (Substitute the name of the table whose columns you want to display for <table name>, of course.) (In Tip 317, "Understanding Why the Expression NULL = NULL Evaluates to False," you will learn why the WHERE clause NULL = NULL is FALSE and will therefore cause the SELECT statement to display only the column names and none of the table's data.) |
|
|
|