Creating and manipulating tables

17.1.3 specifying a default value SQL allows you to specify a default value that the DBMS automatically takes if the value is not given when the row is inserted. The default value is specified in the column definition of the CREATE TABLE statement with the keyword default.

Take a look at the following example:

Input

CREATE TABLE OrderItems
(
Order_num INTEGER not NULL,
Order_item INTEGER not NULL,
prod_id CHAR (Ten) is not NULL,
Quantity INTEGER not NULL DEFAULT 1,
Item_price DECIMAL (8,2) not NULL
);

Analysis
This statement creates a OrderItems table that contains the items that make up the order (the order itself is stored in the Orders table). quantityThe quantity that is listed for each item in the order. In this example, the description of this column increases DEFAULT 1 , indicating that the DBMS, if not given the quantity, uses the quantity 1 .

The default values are often used for date or timestamp columns. For example, use the system date as the default date by specifying a function or variable that references the system date. MySQL user specified DEFAULT CURRENT_DATE() , Oracle user specified DEFAULT SYSDATE , and SQL Server user specified DEFAULT GETDATE() . Unfortunately, this command to get the system date is almost different in different DBMS. Table 17-1 lists the syntax for this command in some DBMS. If a DBMS is not listed here, please refer to the appropriate documentation.

table 17-1 Obtaining system dates

TR class= "Calibre16" >

dbms	functions/variables /th>
access	now ()
db2	current_date
mysql	current_date ()
oracle	sysdate
postgresql	current_date
sql Server	getdate ()
SQLite	date (' Now ')

Tip: Use DEFAULT instead of a NULL value

Complex table structure changes typically require a manual removal process, which involves the following steps:

1. Create a new table with the new column layout;
2. Use the INSERT SELECT statement (for a detailed introduction to this statement, see Lesson 15th) to copy data from the old table to the new table. If necessary, you can use conversion functions and calculated fields;
3. Examine a new table containing the required data;
4. Rename the old table (you can delete it if you are sure);
5. Rename the new table with the original name of the old table;
6. Recreate the triggers, stored procedures, indexes, and foreign keys as needed.

Creating and manipulating tables