CREATE TABLE Statement

Creates a new table.

Category:	Data Definition
Restriction:	Constraint syntax is defined by the data source. The constraint syntax provided here is a general syntax. For complete constraint syntax, see the documentation for your datasource. The SPD Engine data source does not support constraints.
Supports:	EXECUTE Statement
Data source:	SAS data set, SPD Engine data set, SASHDAT file, Aster, DB2 under UNIX and PC, Greenplum, HDMD, Hive, MDS, MySQL, Netezza, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata
Note:	Braces in the syntax convention indicate a syntax grouping. The escape character ( \ ) before a brace indicates that the brace is required in the syntax. Table options must be contained by braces ( { } ).

Syntax

Arguments

<column-definition> Arguments

<column-constraint> and <table-constraint> Arguments

Details

Overview of the CREATE TABLE Statement

SAS Extensions for the CREATE TABLE Statement to Support SAS Data Sources

Defining Columns for a Table

Creating and Populating Tables from a Query Expression

Data Sources

Integrity Constraints

Syntax

CREATE TABLE {table | _NULL_}
[\{OPTIONS SAS-table-option=value
[...SAS-table-option=value] \}]

| ( <column-definition> [, ...<column-definition> | <table-constraint>])

| AS query-expression

;

<column-definition>::=

column data-type [<column-constraint>] [DEFAULT value]

[HAVING [FORMAT format][INFORMAT informat][LABEL 'label']]

<column-constraint>::=

CONSTRAINT constraint

{ CHECK (search-condition)

| PRIMARY KEY

| UNIQUE

| NOT NULL }

<table-constraint>::=

CONSTRAINT constraint

{ CHECK (search-condition)

| PRIMARY KEY (column [, ...column])

| UNIQUE (column [, ...column])

| <referential-constraint> }

[<constraint-check-time>]

<referential-constraint>::=

FOREIGN KEY (referencing-column [, ...referencing-column])

REFERENCES referenced-table (referenced-column [, ...referenced-column])

[<referential-trigger-action>]

<referential-trigger-action>::=

{ ON UPDATE <referential-action> [ON DELETE <referential-action>] }

| { ON DELETE <referential-action> [ON UPDATE <referential-action>] }

<referential-action>::=

CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION

<constraint-check-time>::=

{ DEFERRABLE [INITIALLY DEFERRED | INITIALLY IMMEDIATE] }

| { [INITIALLY DEFERRED | INITIALLY IMMEDIATE] DEFERRABLE }

| INITIALLY DEFERRED

| { NOT DEFERRABLE [INITIALLY IMMEDIATE] }

| { [INITIALLY IMMEDIATE] NOT DEFERRABLE }

Arguments

table

specifies a table to modify. table can be one of these forms

catalog.schema.table-name
schema.table-name
catalog.table-name
table-name

catalog

is an implementation of the ANSI SQL standard for an SQL catalog. The catalog is a data container object that groups logically related schemas. The catalog is the first-level (top) grouping mechanism in a data organization hierarchy that is used along with a schema to provide a means of qualifying names. A catalog is a metadata object in a SAS Metadata Repository.

schema

is an implementation of the ANSI SQL standard for an SQL schema The schema is a data container object that groups files such as tables and views and other objects supported by a data source such as stored procedures. The schema provides a grouping object that is used along with a catalog to provide a means of qualifying names.

table-name

is the name of the table.

Requirement

Table naming conventions are based on the data source. When more than one data source is involved, the maximum length of a table name is determined by the maximum length that is supported by all of the data sources and FedSQL. For example, if your data sources are a SAS data set that has a maximum of 32 characters and MySQL that has a maximum of 64 characters, the maximum length of a table name is 32 characters. For more information, see the documentation for your data source.

_NULL_

specifies to test the performance of creating a table using the AS query-expression clause. FedSQL creates the table internally as if it were to be saved, writing the normal progress messages. The table creation appears to be successful. Once the query expression is complete, the table is discarded.

Interaction

A SELECT select-list FROM _NULL_ statement returns an error.

{OPTIONS SAS-table-option=value [ … SAS-table-option=value ]}

specifies one or more table options and their respective values to apply to the table.

Requirement	The OPTION argument and all table options must be enclosed in braces ( { } ).
See	FedSQL Statement Table Options

AS query-expression

specifies to create a new table from an existing table by selecting rows from the existing table using a query expression. The column attributes, such as formats and labels, are copied from the existing table to the new table.

query-expression

specifies the SELECT statement that retrieves information from an existing table to use in creating a new table.

Requirement	The number of columns used in the SELECT statement must equal the number of columns in the table.
See	Creating and Populating Tables from a Query Expression
	Query Expressions and Subqueries
	SELECT Statement

Data source

SAS data set, SPD Engine data set, Aster, DB2 under UNIX and PC, Greenplum, HDMD, Hive, MDS, MySQL, Netezza, ODBC, Oracle, SAP HANA, Sybase IQ, Teradata

<column-definition> Arguments

column

specifies the name of the column. The column name is either a local or schema qualified name, using one of these forms:

schema.column-name
column-name

schema

is an implementation of the ANSI SQL standard for an SQL schema. The schema is a data container object that groups files such as tables and views and other objects supported by a data source such as stored procedures. The schema provides a grouping object that is used along with a catalog to provide a means of qualifying names.

column-name

is the name of the column.

Restriction	For SAS data sets and SPD Engine data sets, the column name cannot be longer than 32 characters.
Requirement	When more than one data source is involved, the maximum length of a column name is determined by the maximum length that is supported by all of the data sources and FedSQL. For example, if your data sources are a SAS data set that has a maximum of 32 characters and MySQL that has a maximum of 64 characters, the maximum length of a column name is 32 characters. Each column in a table must be unique.
See	For column name requirements, see the documentation for your data source.

data-type

specifies the type of data that the column can store. If the column definition is for a SAS data set or an SPD Engine data set, and the data type is other than CHAR or DOUBLE, FedSQL converts character data types to a CHAR and numeric data types to DOUBLE. FedSQL supports the following table data types:

BIGINT

stores a large signed, exact whole number.

Range	-9,223,372,036,854,775,808 to 9,233,372,036,854,775,807
Data source	Aster, DB2 under UNIX and PC, Greenplum, HDMD, Hive, MDS, MySQL, Netezza, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata
Storage size	8 bytes

BINARY(n)

stores varying-length binary data.

n

specifies the maximum number of bytes that can be used to store the binary data. The number of bytes that are used to store the binary data is the number of bytes that are necessary to represent the binary data, up to n bytes.

Range
Data source	DB2 under UNIX and PC, Hive, MDS, ODBC, SAP HANA, Sybase IQ
Storage size	up to n bytes

CHAR(n) [CHARACTER SET "character-set-identifier"]

stores a fixed-length character string.

n

specifies the number of bytes that are used to store the character string. If the character string is less than n bytes, the value is right-padded with spaces.

CHARACTER SET "character-set-identifier"

specifies character set encoding information for CHAR data types.

Default	Default encoding depends on your operating system and locale.
Restriction	SAS data sets, SPD Engine data sets, and SASHDAT files do not support the use of CHARACTER SET "character-set-identifier". SASHDAT files use the encoding specified in the connection string. If the encoding option is not specified, the encoding defaults to the character set associated with the operating system for SAS Federation Server.
See	LOCALE= Values and Default Settings for ENCODING, PAPERSIZE, DFLANG, and DATESTYLE Options in SAS National Language Support (NLS): Reference Guide

Alias	CHARACTER(n)
Requirement	n must be specified.
Data source	SAS data set, SPD Engine data set, SASHDAT file, DB2 under UNIX and PC, Greenplum, HDMD, MDS, MySQL, Netezza, ODBC, Oracle, PostgreSQL, SAP HANA, Teradata
Storage size	n bytes

DATE

stores a date value in the format yyyy-mm-dd.

Date Element	Description	Valid Values
yyyy	a four-digit year	0001 – 9999
mm	a two-digit month	01 – 12
dd	a two-digit day	00 – 31

Requirement	Input values must be specified as a DATE constant. For more information, see FedSQL Date, Time, and Datetime Constants.
Data source	SAS data set, SPD Engine data set, SASHDAT file, Aster, DB2 under UNIX and PC, Greenplum, HDMD, Hive, MDS, MySQL, Netezza, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata
Storage size	8 bytes

DECIMAL

stores a signed, fixed-point decimal number.

Alias	NUMERIC
Data source	Aster, DB2 under UNIX and PC, Greenplum, Hive, MDS, MySQL, Netezza, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata
Storage size	8 bytes

DOUBLE

stores a signed, approximate, floating point number.

Alias	DOUBLE PRECISION
Data source	SAS data set, SPD Engine data set, SASHDAT file, Aster, DB2 under UNIX and PC, Greenplum, HDMD, Hive, MDS, MySQL, Netezza, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata
Storage size	8 bytes

FLOAT(p)

stores a signed, approximate, single-precision or double-precision, floating point number. The user-specified precision determines whether the data type stores a single precision or double precision number. If the specified precision is equal to or greater than 25, the value is stored as a double precision number, which is a DOUBLE. If the specified precision is less than 25, the value is stored as a single precision number, which is a REAL.

p

specifies the maximum number of digits in the floating point number.

Data source	SAS data set, SPD Engine data set, Aster, DB2 under UNIX and PC, Greenplum, Hive, MySQL, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata
Storage size	4 bytes
Tip	If p is not specified, a DOUBLE is used.

INTEGER

stores an exact whole number.

Range	-2,147,483,648 to 2,147,483,647
Data source	SAS data set, SPD Engine data set, Aster, DB2 under UNIX and PC, Greenplum, HDMD, Hive, MDS, MySQL, Netezza, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata
Storage size	4 bytes

NCHAR(n)

stores a fixed-length character string by using the Unicode national character set.

n

specifies the maximum number of multibyte characters that are used to store the character string. If the character string is less than n bytes, the value is right-padded with spaces.

Requirement	n must be specified.
Data source	Aster, DB2 under UNIX and PC, MDS, MySQL, ODBC, Oracle, SAP HANA, Sybase IQ
Storage size	n bytes. Depending on the operating system, Unicode characters use either 2 or 4 bytes per character and support all international characters.

NUMERIC

stores a signed, fixed-point decimal number.

Alias	DECIMAL
Data source	Aster, DB2 under UNIX and PC, Greenplum, MDS, MySQL, Netezza, ODBC, Oracle, PostgreSQL, Sybase IQ, Teradata
Storage size	8 bytes

NVARCHAR(n)

stores a varying-length character string by using the Unicode national character set.

n

specifies the maximum number of multibyte characters that can be used to store the character string. The number of bytes that are stored is the actual number of characters specified, up to n bytes.

Requirement	n must be specified.
Data source	DB2 under UNIX and PC, MDS, ODBC, Oracle, Netezza, SAP HANA, Sybase IQ
Storage size	up to n bytes. Depending on the platform, Unicode characters use either 2 or bytes per character and can support all international characters.

REAL

stores a signed, approximate, single-precision, floating-point number.

Requirement
Data source	SAS data set, SPD Engine data set, Aster, DB2 under UNIX and PC, Greenplum, HDMD, Hive, MySQL, Netezza, Oracle, PostgreSQL, SAP HANA, Sybase IQ
Storage size	4 bytes

SMALLINT

stores a small signed, exact whole number.

Range	-32,768 to 32,767
Data source	SAS data set, SPD Engine data set, Aster, DB2 under UNIX and PC, Greenplum, HDMD, Hive, MySQL, Netezza, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata
Storage size	2 bytes

TIME(p)

stores a time value with seconds precision.

Time values are in the following form:hh:mm:ss[.nnnnnn]

Time Element	Description	Valid Values
hh	a two-digit hour	00 – 23
mm	a two-digit minute	00 – 59
ss	a two-digit second	00 – 61
nnnnnn	up to six digits to indicate a fraction of a second	0 – 999999

p	specifies 0–6 digits to use for the precision of a fraction of a second.

Requirement	Input values must be specified as a TIME constant. For more information, see FedSQL Date, Time, and Datetime Constants.
Data source	SAS data set, SPD Engine data set, SASHDAT file, Aster, DB2 under UNIX and PC, Greenplum, HDMD, MDS, MySQL, Netezza, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata
Storage size	8 bytes

TIMESTAMP(p)

stores both the date and time value with seconds precision.

Date and time values are in the following form: yyyy-mm-dd hh:mm:ss[.nnnnnn]

Time Element	Description	Valid Values
Date Components
yyyy	a four-digit year	0001- 9999
mm	a two-digit month	01- 12
dd	a two-digit day	01- 31
Time Components
hh	a two-digit hour	00 – 23
mm	a two-digit minute	00 – 59
ss	a two-digit second	00 – 61
nnnnnn	up to six digits to indicate a fraction of a second	0 – 999999

p	specifies 0–6 digits to use for the precision of a fraction of a second.

Requirement	Input values must be specified as a TIMESTAMP constant. For more information, see FedSQL Date, Time, and Datetime Constants.
Data source	SAS data set, SPD Engine data set, SASHDAT file, DB2 under UNIX and PC, Greenplum, HDMD, Hive, MDS, MySQL, Netezza, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata
Storage size	8 bytes

TINYINT

stores a very small signed exact, whole number.

Range	-128 to 127
Data source	SAS data set, SPD Engine data set, HDMD, Hive, MySQL, Netezza, ODBC, Oracle, SAP HANA, Sybase IQ, Teradata
Storage size	1 byte

VARBINARY(n)

stores varying-length binary data.

n

Range
Data source	DB2 under UNIX and PC, MDS, ODBC, Oracle, SAP HANA, Sybase IQ, Teradata
Storage size	up to n bytes

VARCHAR(n) [CHARACTER SET "character-set-identifier"]

stores a varying-length character string.

n

specifies the number of bytes used to store the character string. The number of bytes that are stored is the actual number of characters, up to n bytes.

[CHARACTER SET "character-set-identifier"]

specifies character set encoding information for CHAR data types.

Default	Default encoding depends on your operating system and locale.
See	LOCALE= Values and Default Settings for ENCODING, PAPERSIZE, DFLANG, and DATESTYLE Options in SAS National Language Support (NLS): Reference Guide

Data source	Aster, DB2 under UNIX and PC, Greenplum, HDMD, Hive, MDS, MySQL, Netezza, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata
Storage size	up to n bytes

<column-constraint>

specifies to place an integrity constraint on the column.

Data source	SAS data set, DB2 under UNIX and PC, MDS, MySQL, ODBC, Oracle, SAP HANA, Teradata
See	<column-constraint> and <table-constraint> Arguments
See	Integrity Constraints

DEFAULT value

for each row that is added to the table, specifies a value for the column that is assumed when no other value is entered for that column.

value

specifies the default value.

Requirement

value must be in the range of the specified data type.

Data source

DB2 under UNIX and PC, MySQL, ODBC, Oracle, SAP HANA, Teradata

HAVING [FORMAT format][INFORMAT informat][LABEL 'label ']

specifies a clause that is used to associate a format, informat, or label with the column.

FORMAT format

specifies a SAS data format that is stored as column metadata. The format is not applied to the column data until execution time.

format

specifies a valid SAS data format. If the format is a format other than a FedSQL or DS2 format, it must be a valid format for the Base SAS.

See	FedSQL Formats,
	DS2 Formats in SAS DS2 Language Reference
	Dictionary of Formats in SAS Formats and Informats: Reference

Restriction

Formats that are created by PROC FORMAT cannot be stored as column metadata.

Note

When you create a table, you can associate a SAS data format with a column. The format is not validated or applied to the column until execution time, such as in a PUT function. If the format is applied to the column in a Base SAS environment, any format can be stored with the column. If the format is applied to the column in the FedSQL environment, the format must be a valid FedSQL or DS2 format.

INFORMAT informat

specifies a SAS data informat that is stored as column metadata. The informat is not applied to the column data. No validation is done on the informat; it is only informational.

informat

specifies a SAS data informat.

See	FedSQL Informats
	Dictionary of Informats in SAS Formats and Informats: Reference

LABEL 'text-string'

specifies a text string to use as an alternate column heading that appears in place of the column name in a query expression result.

Range	1 - 255 characters
Restriction	A label can be created only with the CREATE TABLE statement and it cannot be used in a query expression operation.
Requirement	text-string must be enclosed in either double or single quotation marks. If text-string contains a single quotation mark, enclose text-string in double quotation marks.

Data source

SAS data set, SPD Engine data set

<column-constraint> and <table-constraint> Arguments

CONSTRAINT

begins a column constraint or a table constraint.

Data source

SAS data set, DB2 under UNIX and PC, MDS, MySQL, ODBC, Oracle, SAP HANA, Teradata

constraint

specifies a name to identify the constraint.

See	For constraint name requirements, see the documentation for your data source.

CHECK(search-condition)

specifies a condition for values in the column or table. search-condition is a valid FedSQL expression that resolves to a Boolean value. If search-condition is false, no changes are made to the table.

Restriction	For a SAS data set, the search condition cannot contain FedSQL functions.
Data source	SAS data set, Aster, DB2 under UNIX and PC, Greenplum, MySQL, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata
See	FedSQL Expressions

PRIMARY KEY

specifies that, for each row in the table, the value in the column can be used to uniquely identify its respective row in the table. It is a value that does not change. A primary key cannot have a null value.

Data source

SAS data set, Aster, DB2 under UNIX and PC, Greenplum, MySQL, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata

PRIMARY KEY (column [, …column])

specifies that, for each row in the table, the values for all of the columns specified are used to uniquely identify its respective row in the table. A primary key cannot have a null value and it must be unique for each row in the column. It is a value that does not change.

Data source

SAS data set, Aster, DB2 under UNIX and PC, Greenplum, MySQL, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata

UNIQUE

specifies that no two rows in the column can have the same value. A null value is allowed unless you specify NOT NULL.

Data source

SAS data set, DB2 under UNIX and PC, MySQL, ODBC, Oracle, PostgreSQL, SAP HANA, Teradata

UNIQUE(column [, …column])

specifies that no two rows in any of the specified columns can have the same value. A null value is allowed unless you specify NOT NULL.

Data source

SAS data set, Aster, DB2 under UNIX and PC, Greenplum, MySQL, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata

NOT NULL

specifies that a null value is not valid in any row for the specified column.

Data source

SAS data set, Aster, DB2 under UNIX and PC, Greenplum, MDS, MySQL, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata

FOREIGN KEY (referencing-column [, …referencing column]) REFERENCES referenced-table (referenced-column [, …referenced-column <referential-trigger-action>])

specifies the clause that relates columns in table to columns in another table through the values for those columns. Foreign keys must always include enough columns to uniquely identify a row in the referenced table. Foreign key constraints help ensure the integrity of related data in multiple tables.

FOREIGN KEY

begins the clause that identifies one or more columns in table that relate to columns in another table.

Data source

Aster, DB2 under UNIX and PC, Greenplum, MySQL, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata

referencing-colunm

specifies the columns in table that relate to columns in another table.

REFERENCES

begins the clause that identifies the columns in another table.

referenced-table

specifies the table whose columns relate to columns in table.

referenced-column

specifies one or more columns in the referenced table that relate to the columns in table.

<referential-trigger-action>

specifies the action to take in the referenced table when columns in table are updated or deleted.

ON UPDATE <referential-action>

specifies the clause for the action to take when a column in table is updated.

ON DELETE <referential-action>

specifies the clause for the action to take when a column in table is deleted.

<referential-action>

specifies the action to take when a column in table is updated or deleted.

CASCADE

ON UPDATE, specifies to update all rows in table that reference an updated value in the referenced table. ON DELETE, removes all rows in table that reference the deleted rows in the referenced table.

SET NULL

specifies to set the referenced columns to a null value.

SET DEFAULT

specifies to set the referenced columns to their default value.

RESTRICT

specifies that if the referential constraint is not satisfied at any time while the FedSQL statement is processing, no action is taken and the update or deletion fails.

NO ACTION

specifies that if the referential constraint is not satisfied at the end of the FedSQL statement, no action is taken and the update or deletion fails.

<constraint-check-time>

specifies when the constraint is checked:

DEFERRABLE | NOT DEFERRABLE

specifies whether the constraint violation check can be performed after the transaction completes or if it must be checked at the end of each FedSQL statement. If DEFERRABLE is specified, the constraint check can occur at the end of the transaction. If NOT DEFERRABLE is specified, the constraint must be checked when the FedSQL statement terminates. Specifying DEFERRABLE is useful when more than one statement is necessary to complete a transaction.

Default

DEFERRABLE

INITIALLY DEFERRED

specifies that the constraint violation check is deferred, by default, at the beginning of each transaction and it does not occur until the end of a transaction.

INITIALLY IMMEDIATE

specifies that the constraint violation check occurs at the end of each FedSQL statement.

Details

Overview of the CREATE TABLE Statement

The CREATE TABLE statement enables you to create tables by defining table columns or by selecting columns from an existing table using a query expression. SAS provides extensions to the CREATE TABLE statement to support SAS data sources.

SAS Extensions for the CREATE TABLE Statement to Support SAS Data Sources

SAS extensions for the CREATE TABLE statement using FedSQL enable you to assign formats and informats to columns and add a label to a column.

SAS Formats

You can specify that the column data is stored and retrieved as a SAS formatted value by using the HAVING FORMAT clause. If the data type is not either CHAR or DOUBLE, it is converted to either CHAR or DOUBLE.

For example, to store the date in the format ddmmmyy, where dd is a two-digit year, mmm is a three-digit month, and yy is a two-digit year, the column definition would specify DOUBLE as the data type and FORMAT='DATE7.'. November 1, 2012 would be stored as 01Nov12. Your column definition might look like the following:

saleenddate double not null having format date7.;

In comparison, a column that stores date information using the DATE data type stores the date in the format yyyy-mm-dd. November 1, 2012 would be stored as 2012-11-01.

SAS Informats

SAS and user-defined informats can be stored and retrieved with the column data by using the HAVING INFORMAT clause. The informat is not applied to the data; it is information only. The client application is responsible for applying the informat to the data. Informats can be associated with all data types, but all data types will be converted to either CHAR or DOUBLE.

saledates double having informat anydtdte14.;

For more information about informats, see FedSQL Informats.

Column Labels

A label is a descriptive, quoted, text string that is displayed in query expression results instead of the column name. You specify a label by using the LABEL argument in the HAVING clause:

saleenddate double not null having label 'Last Day of Sale';

A label cannot be used in a query expression operation.

Defining Columns for a Table

When you create columns for a table, the column name and the data type are required. All other column definition arguments are optional. For column naming conventions, see the documentation for your data source. FedSQL reserved words cannot be used as column names. For a list of FedSQL reserved words, see FedSQL Reserved Words .

You can add an unspecified number of columns to a table by separating each column definition with a comma. Enclose the complete group of column definitions in parenthesis. The following CREATE TABLE statement creates the Customers table:

create table customers
   ( custid double primary key,
     name char(16),
     address char(64),
     city char(16),
     state char(2),
     country char(16),
     phone char(16),
     initorder date having label 'Initial Order'
   );

Each column definition names the column and assigns a data type. The column ID contains the primary key integrity constraint, which is used to uniquely identify a table row. The INITORDER column is of the DATE data type, and when the column is displayed, the label Initial Order is used in place of the column name initorder.

You can add rows to the table by using the INSERT statement. The INSERT statement enables you to specify values for each of the table columns defined with the CREATE TABLE statement. For more information, see the INSERT Statement.

Creating and Populating Tables from a Query Expression

When you create a table using a query expression, you add rows to the table as the table is created. You use a SELECT statement to retrieve data from an existing table to create the new table. The number of columns in the CREATE TABLE statement column definition must equal the number of columns that are returned by the SELECT statement. If no column names are specified in the CREATE TABLE statement, the columns and default values that are returned by the SELECT statement are used in the new table.

This CREATE TABLE statement creates a new table that is based on only three columns from the CorpData table:

create table spainEmails
as select name, emailid, lastPurchaseDate from corpdata where country='Spain';

The following CREATE TABLE statement selects all columns from the CorpData table:

create table spain
as select * from corpdata where country='Spain';

You can test the performance of a query expression before creating a table by using a table name of _NULL _ for the query expression, as in this example:

create table _null_ as select * from corpdata where country='Spain';

FedSQL performs normal processing to create the table and the table appears to be created. The table is discarded when the query is complete.

Data Sources

When defining data types using the FedSQL language, in order for data to be stored, the defined data type must be available for data storage in that data source. Although FedSQL provides support for several data types, the data types that can be defined for a particular table depend on the data source. Each data source does not necessarily support all of the FedSQL data types.

In addition, data sources support variations of the standard SQL data types. That means that a data type that you specify might default to a different data type that might also have different attributes in the underlying data source. This is done when a data source does not natively support a specific data type, but data values of a similar data type can be converted without data loss. For example, to support the INTEGER data type, a SAS data set defaults the data type definition to SAS numeric, which is a DOUBLE. For details about data source implementation for each data type such as data source-dependent attributes, see Data Type Reference.

Integrity Constraints

Integrity constraints are a set of data validation rules that you can specify to preserve the validity and consistency of your data. Integrity constraints that are specified in the CREATE TABLE statement are passed through to the data source. When a transaction modifies the table, the data source enforces integrity constraints.

A column constraint is a constraint that is defined for one column. A table constraint defines a constraint for two or more columns.

The following statements create SAS data sets using integrity constraints:

Create a Products table using the product ID as the primary key; no two product IDs can identify the same product, and the product cannot be a null value. Check constraints at the end of a transaction:
```
create table products (prodid double primary key, 
   product char(8) unique not null initially deferred);
```

Create a Sales table with totals in US dollars, and include the country:

create table sales (prodid double, 
   custid double  primary key,
   totals double having format dollar10., 
   country char(30) not null);

Create a SAS data set for customer credit card information. Customers are uniquely identified by their name and customer number.
```
create table custcredit (name char(30),
   custNum double,
   ccType char(15) having label 'Credit Card Type',
   ccNum char(20) having label 'Credit Card Number',
   ccExp date having label 'Expiration Date',
   CONSTRAINT ccconst primary key(name, CustNum)); 
```
When FedSQL encounters a DATE data type for a SAS data set, it does a type conversion from DATE to DOUBLE and assigns the DATE9. format to the column.

For information about constraints, see the documentation for your data source.