create table (minimum)

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. See Datatype Conversions of Transact-SQL to ODBC for several datatypes expect a length, n, in parentheses: datatype (n).

Examples

Example 1 – creates the titles table.

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

These Transact-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
Transact-SQL allows you to specify null or notnull, with a default of not null. ODBC allows only not null to be specified. The default is null.

Null Transformations During Transact-SQL to ODBC CREATE TABLE
Transact-SQL Specification	Transformed to
null	null
not null	not null
<nothing>	not null

The selection order is:

The access service attempts to change the Transact-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.

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.