loading..
Ðóññêèé    English
00:04

Default values

For a column, a default value can be specified, that is, a value the column will take in case the insert statement doesn’t 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(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL standard, the statement for our example would look as follows:

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

However,  A database management system (DBMS) by Microsoft Corporation.SQL Server doesn’t 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 don’t 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. Let’s 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 CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.