loading..
   English
15:03

Default values

For a column, a default value can be specified, that is, a value the column will take in case the insert statement doesnt provide any value for it. Generally, the most common value is used as the default one.

Let most models in our database be personal computers (desktops). Thus, we could make PC the default value for the type column. This could be done by means of the ALTER TABLE statement. According to the SQL standard, the statement for our example would look as follows:

  1. ALTER TABLE Product
  2. ALTER COLUMN "type" SET DEFAULT 'PC';

However, SQL Server doesnt support the standard syntax for this case; in the T-SQL dialect, the same action can be carried out the following way: 

  1. ALTER TABLE Product
  2. ADD DEFAULT 'PC' FOR type;

Now, we dont need to specify a type when adding a PC model to the Product table.

  1. INSERT INTO Product(maker, model) VALUES('A', '1124');

Note that not just a literal can be used as a default value but also a function without arguments. In particular, we can use the function CURRENT_TIMESTAMP, which returns the current value for date and time. Lets add a column containing the time of insertion of model data into the database to the Product table. 

  1. ALTER TABLE Product
  2. ADD add_date DATETIME DEFAULT CURRENT_TIMESTAMP;

Now, when we add model 1125 by maker A

  1. INSERT INTO Product(maker, model) VALUES('A', '1125');
we get the following result:

  1. SELECT * FROM Product WHERE model ='1125';

maker model type add_date
A 1125 PC 2015-08-24 22:21:23.310

Notes

1.    If no default value is specified, NULL, that is, a NULL mark is implied. Of course, such a default value can only be used if no NOT NULL constraint is applied to the column.

2.    If a column is added to an existing table, this column, according to the standard, will be filled with default values for the records already existing. In SQL server, the column adding behavior deviates from the standard a bit. If you execute the statement

  1. ALTER TABLE Product ADD available VARCHAR(20) DEFAULT 'Yes';[[/CODE]which adds the COLUMN available WITH the DEFAULT value yes TO the Product TABLE, this COLUMN, strangely enough, will be filled WITH NULL marks. There are two possible ways TO force the server TO fill the COLUMN WITH yeses:
  2. a). Forbid the usage of NULL, that IS, rewrite the above command AS follows:
  3. [[CODE]]ALTER TABLE Product ADD available VARCHAR(20) NOT NULL DEFAULT 'Yes';

Obviously, this method is not applicable if the column allows NULLs.

b). Use the special WITH VALUES clause: 

  1. ALTER TABLE Product ADD available VARCHAR(20) DEFAULT 'Yes' WITH VALUES;


Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100