create table

Description

Creates a new table.

Syntax

create table [database.[owner].]table_name (column_name datatype {null | not null} [{, next_column }...]) [on segment_name]

Parameters

table_name

is the name of the new table. It conforms to the rules for identifiers and is unique within the database and to the owner.

column_name

is the name of the column in the table. It conforms to the rules for identifiers and is unique in the table.

datatype

is the datatype of the column. Only system datatypes are used. As shown in Table C-3, several datatypes expect a length, n, in parentheses:

 	datatype(n)

null | not null

specifies a null value if a user does not provide a value during an insertion and no default exists (for null), or that a user must provide a non-null value if no default exists (for not null).

next_column

indicates that you can include additional column definitions (separated by commas) using the same syntax described for a column definition.

Examples

Example 1

create table titles
 	(title_id 	tid 	not null,
 	title 	varchar(80) 	not null,
 	type 	char(12) 	not null,
 	pub_id 	char(4) 	null,
 	price 	money 	null,
 	advance 	money 	null,
 	total_sales 	int 	null,
 	notes 	varchar(200)	null,
 	pubdate 	datetime 	not null,
 	contract 	bit 	not null)

Usage

• T-SQL allows you to specify null or not null, with a default of not null. DB2 allows only not null to be specified, and the default is null. The following table shows how the DB2 access service transforms this clause.

  Table C-2: Null transformations during T-SQL to DB2 create table

  Transact-SQL specification	Transformed to...
  null	<nothing>
  not null	not null
  <nothing>	not null

• The following table shows the DB2 access service transformation of datatype specifications.

  Table C-3: Datatype conversions during T-SQL to DB2 create table

  Transact-SQL datatype	DB2 datatype
  tinyint	SMALLINT
  smallint	SMALLINT
  int	INT
  numeric(p,s)	NUMERIC(p,s)
  decimal(p,s)	DECIMAL(p,s)
  float (double precision)	FLOAT
  real	REAL
  smallmoney	DECIMAL(10,4)
  money	DECIMAL(19,4)
  smalldatetime	TIMESTAMP
  datetime	TIMESTAMP
  char(n)	CHAR(n)
  varchar(n)	VARCHAR(n)
  text	LONG VARCHAR(n)
  binary(n)	CHAR(n) FOR BIT DATA
  varbinary(n)	VARCHAR(n) FOR BIT DATA
  image	LONG VARCHAR (n) FOR BIT DATA
  bit	SMALLINT

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF