create database  create existing table

Chapter 1: Commands

create default

Description

Specifies a value to insert in a column (or in all columns of a user-defined datatype) if no value is explicitly supplied at insert time.

Syntax

create default [owner.]default_name 
	as constant_expression

Parameters

default_name

is the name of the default. It must conform to the rules for identifiers and cannot be a variable. Specify the owner’s name to create another default of the same name owned by a different user in the current database. The default value for owner is the current user.

constant_expression

is an expression that does not include the names of any columns or other database objects. It can include global variables and built-in functions that do not reference database objects. Enclose character and date constants in quotes and use a “0x” prefix for binary constants.

Examples

Example 1

Creates a default called D1 that uses the @@spid global variable:

create default D1 as @@spid

Example 2

Defines a default value. Now, bind it to the appropriate column or user-defined datatype with sp_bindefault:

create default phonedflt as "UNKNOWN"

Example 3

sp_bindefault phonedflt, "authors.phone"

The default takes effect only if there is no entry in the phone column of the authors table. No entry is different from a null value entry. To get the default, issue an insert command with a column list that does not include the column that has the default.

Example 4

Creates a default value, todays_date, that inserts the current date into the columns to which it is bound:

create default todays_date as getdate()

Usage


Restrictions


Datatype compatibility


Getting information about defaults


Defaults and rules


Defaults and nulls


Specifying a default value in create table

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Use the default clause of the create table statement to create ANSI SQL-compliant defaults.

Permissions

create default permission defaults to the Database Owner, who can transfer it to other users.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event

Audit option

Command or access audited

Information in extrainfo

14

create

create default

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Other information – NULL

  • Current value – NULL

  • Proxy information – Original login name, if set proxy in effect

See also

Commands alter table, create rule, create table, drop default, drop rule

System procedures sp_bindefault, sp_help, sp_helptext, sp_rename, sp_unbindefault





Copyright © 2005. Sybase Inc. All rights reserved. create existing table

View this book as PDF