create table (minimum)

Description

Creates new tables and optional integrity constants.

Syntax

Transact-SQL Syntax

create table [database.[owner.]table_name (column_name datatype
[default {constant_expression | user | null}]
 {[{identity | null | not null}]
 | [[constraint constraint_name]
 {{unique | primary key}
 [clustered | nonclustered]
 [with {fillfactor | max_rows_per_page} = x]
 [on segment_name]
 | references [[database.]owner.]ref_table
[(ref_column)]
 | check (search_condition)}]}...

| [constraint constraint_name]
 {{unique | primary key}
 [clustered | nonclustered]
 (column_name [{, column_name}...])
 [with {fillfactor | max_rows_per_page} = x]
 [on segment_name]
 | foreign key (column_name [{, column_name}...])
 references [[database.]owner.]ref_table
[(ref_column [{, ref_column}...])]
 | [check (search_condition)}

[{, {next_column | next_constraint}}...])

[with max_rows_per_page = x][on segment_name]

ODBC Syntax

CREATE TABLE base_table_name
(column_element[,column_element]...)

column_element::=column_definition|
 table_constraint_definition

column_definition:=
 column_identifier datatype
[DEFAULT default_value]
 [column_constraint_definition
[column_constraint_definition]...]

default_value::=literal|NULL|USER

column_constraint_definition::=
 NOT NULL
 |UNIQUE|PRIMARY KEY
 |REFERENCES ref_table_name referenced_columns

table_constraint_definition::=
 UNIQUE(column_identifier[,column_identifier]...)
 |PRIMARY KEY(column_identifier[,column_identifier]...]
 |CHECK(search_condition)
 |FOREIGN KEY referencing_columns REFERENCES
 ref_table_name referenced_columns

Parameters

null | not null: specifies a NULL value if you do not provide a value during an insertion and no default exists (for null), or that you 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.

on segment_name: specifies the name of the segment on which to place the table.

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 D-3, several datatypes expect a length, n, in parentheses: datatype(n).

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)

Creates the titles table.

Usage

These T-SQL parts of the create table command are not recognized by sybase transformation mode:
- with fillfactor
- clustered | nonclustered
- with max_rows_per_page
- on segment name
T-SQL allows you to specify null or not null, with a default of not null. ODBC allows only not null to be specified. The default is null.

Table D-2 shows how the access service transforms this clause.

**Table D-2: Null transformations during T-SQL to ODBC CREATE TABLE**
T-SQL specification	Transformed to
null	null
not null	not null
<nothing>	not null

Table D-3 shows how the access service transforms T-SQL datatypes. The selection order is:

The access service attempts to change the T-SQL datatype to the primary ODBC datatype.
If the ODBC driver does not support the ODBC datatype, the access service uses the secondary ODBC datatype.
If the secondary ODBC datatype is also unsupported, the access service uses the final ODBC datatype.

Because the ODBC driver may not support extended datatypes such as Tinyint and Bit, a core ODBC type is associated with those datatypes as the second and third choices.

**Table D-3: Datatype conversions of T-SQL to ODBC**
T-SQL datatype	Primary ODBC datatype	Secondary ODBC datatype	Final ODBC datatype
Tinyint	SQL_TINYINT	SQL_SMALLINT	SQL_INTEGER
Smallint	SQL_SMALLINT	SQL_INTEGER	SQL_INTEGER
Int	SQL_INTEGER	SQL_INTEGER	SQL_INTEGER
Numeric	SQL_NUMERIC	SQL_DECIMAL	SQL_FLOAT
Decimal	SQL_DECIMAL	SQL_NUMERIC	SQL_FLOAT
Float	SQL_FLOAT	SQL_DOUBLE	SQL_CHAR
Double Precision	SQL_DOUBLE	SQL_FLOAT	SQL_FLOAT
Real	SQL_REAL	SQL_FLOAT	SQL_FLOAT
Smallmoney	SQL_DECIMAL	SQL_NUMERIC	SQL_FLOAT
Money	SQL_DECIMAL	SQL_NUMERIC	SQL_FLOAT
Smalldatetime	TIMESTAMP	SQL_CHAR	SQL_CHAR
Datetime	TIMESTAMP	SQL_CHAR	SQL_CHAR
Char	SQL_CHAR	SQL_CHAR	SQL_CHAR
Varchar	SQL_VARCHAR	SQL_VARCHAR	SQL_VARCHAR
Nchar	1 (SQL_CHAR(2n))	SQL_CHAR	SQL_CHAR
Nvarchar	12 (SQL_VARCHAR(2n))	SQL_VARCHAR	SQL_VARCHAR
Text	SQL_LONGVARCHAR	SQL_VARCHAR	SQL_VARCHAR
Binary	SQL_BINARY	SQL_VARBINARY	SQL_CHAR
Varbinary	SQL_VARBINARY	SQL_LONGVARBINARY	SQL_VARCHAR
Image	SQL_LONGVARBINARY	SQL_LONGVARCHAR	SQL_VARCHAR
Bit	SQL_BIT	SQL_CHAR	SQL_CHAR

The size of the char value for Nchar and Nvarchar conversions should be doubled to accommodate the double-byte characters possible.

For Text secondary and final ODBC datatype values, and Image final ODBC datatype values, the original value can be truncated to fit the “transformed to” value.

Binary, Varbinary, and Image final ODBC datatype values can go through code set translation, which is not desirable for binary datatypes.