Home Man
Today's Posts

Linux & Unix Commands - Search Man Pages

RedHat 9 (Linux i386) - man page for create_table (redhat section 7)

CREATE TABLE(7) 			   SQL Commands 			  CREATE TABLE(7)

       CREATE TABLE - define a new table

       CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
	   { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
	   | table_constraint }  [, ... ]
       [ INHERITS ( parent_table [, ... ] ) ]

       where column_constraint is:

       [ CONSTRAINT constraint_name ]
	 CHECK (expression) |
	 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
	   [ ON DELETE action ] [ ON UPDATE action ] }

       and table_constraint is:

       [ CONSTRAINT constraint_name ]
       { UNIQUE ( column_name [, ... ] ) |
	 PRIMARY KEY ( column_name [, ... ] ) |
	 CHECK ( expression ) |
	 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
	   [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }

       CREATE  TABLE  will create a new, initially empty table in the current database. The table
       will be owned by the user issuing the command.

       If a schema name is given (for example, CREATE TABLE myschema.mytable ...) then the  table
       is created in the specified schema. Otherwise it is created in the current schema (the one
       at the front of the search path; see CURRENT_SCHEMA()).	TEMP tables exist  in  a  special
       schema, so a schema name may not be given when creating a TEMP table.  The table name must
       be distinct from the name of any other table, sequence, index, or view in the same schema.

       CREATE TABLE also automatically creates a data type that represents the tuple type (struc-
       ture  type)  corresponding to one row of the table. Therefore, tables cannot have the same
       name as any existing data type in the same schema.

       A table cannot have more than 1600 columns. (In practice, the  effective  limit	is  lower
       because of tuple-length constraints).

       The  optional  constraint  clauses specify constraints (or tests) that new or updated rows
       must satisfy for an insert or update operation to succeed. A constraint is a  named  rule:
       an  SQL object which helps define valid sets of values by putting limits on the results of
       insert, update, or delete operations performed on a table.

       There are two ways to define constraints: table constraints and column constraints. A col-
       umn constraint is defined as part of a column definition. A table constraint definition is
       not tied to a particular column, and it can encompass more than one column.  Every  column
       constraint  can also be written as a table constraint; a column constraint is only a nota-
       tional convenience if the constraint only affects one column.

	      If specified, the table is created as a  temporary  table.   Temporary  tables  are
	      automatically  dropped  at the end of a session. Existing permanent tables with the
	      same name are not visible to the current session while the temporary table  exists,
	      unless  they  are referenced with schema-qualified names.  Any indexes created on a
	      temporary table are automatically temporary as well.

	      The LOCAL word is optional. But see under Compatibility [create_table(7)].

	      The name (optionally schema-qualified) of the table to be created.

	      The name of a column to be created in the new table.

	      The data type of the column. This may  include  array  specifiers.   Refer  to  the
	      User's Guide for further information about data types and arrays.

	      The DEFAULT clause assigns a default data value for the column whose column defini-
	      tion it appears within. The value is any variable-free expression  (subselects  and
	      cross-references	to  other columns in the current table are not allowed). The data
	      type of the default expression must match the data type of the column.

	      The default expression will be used in any insert operation that does not specify a
	      value  for  the  column.	If  there is no default for a column, then the default is

       INHERITS ( parent_table [, ... ] )
	      The optional INHERITS clause specifies a list of tables from which  the  new  table
	      automatically inherits all columns. If the same column name exists in more than one
	      parent table, an error is reported unless the data types of the  columns	match  in
	      each  of the parent tables. If there is no conflict, then the duplicate columns are
	      merged to form a single column in the new table. If the column name list of the new
	      table  contains  a column that is also inherited, the data type must likewise match
	      the inherited column(s), and the column definitions are merged into  one.  However,
	      inherited  and  new column declarations of the same name need not specify identical
	      constraints: all constraints provided from any declaration are merged together  and
	      all  are	applied to the new table. If the new table explicitly specifies a default
	      value for the column, this default overrides any defaults from  inherited  declara-
	      tions  of  the  column.  Otherwise, any parents that specify default values for the
	      column must all specify the same default, or an error will be reported.

	      This optional clause specifies whether rows of  the  new	table  should  have  OIDs
	      (object identifiers) assigned to them. The default is to have OIDs. (If the new ta-
	      ble inherits from any tables that have OIDs, then WITH OIDS is forced even  if  the
	      command says WITHOUT OIDS.)

	      Specifying  WITHOUT OIDS allows the user to suppress generation of OIDs for rows of
	      a table. This may be worthwhile for large tables, since it will reduce OID consump-
	      tion  and  thereby  postpone wraparound of the 32-bit OID counter. Once the counter
	      wraps around, uniqueness of OIDs can  no	longer	be  assumed,  which  considerably
	      reduces their usefulness.

       CONSTRAINT constraint_name
	      An  optional  name  for  a column or table constraint. If not specified, the system
	      generates a name.

       NOT NULL
	      The column is not allowed to contain NULL values.

       NULL   The column is allowed to contain NULL values. This is the default.

	      This clause is only available for compatibility with  non-standard  SQL  databases.
	      Its use is discouraged in new applications.

       UNIQUE (column constraint)

       UNIQUE ( column_name [, ... ] ) (table constraint)
	      The UNIQUE constraint specifies a rule that a group of one or more distinct columns
	      of a table may contain only unique values. The behavior of the  unique  table  con-
	      straint  is the same as that for column constraints, with the additional capability
	      to span multiple columns.

	      For the purpose of a unique constraint, NULL values are not considered equal.

	      Each unique table constraint must name a set of columns that is different from  the
	      set  of columns named by any other unique or primary key constraint defined for the
	      table. (Otherwise it would just be the same constraint listed twice.)

       PRIMARY KEY (column constraint)

       PRIMARY KEY ( column_name [, ... ] ) (table constraint)
	      The primary key constraint specifies that a column or columns of a table	may  con-
	      tain  only  unique  (non-duplicate),  non-NULL values.  Technically, PRIMARY KEY is
	      merely a combination of UNIQUE and NOT NULL, but identifying a set  of  columns  as
	      primary  key  also  provides meta-data about the design of the schema, as a primary
	      key implies that other tables may rely on this set of columns as a  unique  identi-
	      fier for rows.

	      Only  one  primary key can be specified for a table, whether as a column constraint
	      or a table constraint.

	      The primary key constraint should name a set of  columns	that  is  different  from
	      other sets of columns named by any unique constraint defined for the same table.

       CHECK (expression)
	      CHECK clauses specify integrity constraints or tests which new or updated rows must
	      satisfy for an insert or update operation to succeed. Each constraint  must  be  an
	      expression  producing a Boolean result. A condition appearing within a column defi-
	      nition should reference that column's value only, while a condition appearing as	a
	      table constraint may reference multiple columns.

	      Currently, CHECK expressions cannot contain subselects nor refer to variables other
	      than columns of the current row.

       REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE
       action ] (column constraint)

       FOREIGN KEY ( column [, ... ] )
	      The  REFERENCES  column constraint specifies that a group of one or more columns of
	      the new table must only contain values which match against values in the referenced
	      column(s)  refcolumn of the referenced table reftable. If refcolumn is omitted, the
	      primary key of the reftable is used. The referenced columns must be the columns  of
	      a unique or primary key constraint in the referenced table.

	      A  value added to these columns is matched against the values of the referenced ta-
	      ble and referenced columns using the given match type. There are three match types:
	      MATCH  FULL,  MATCH  PARTIAL,  and a default match type if none is specified. MATCH
	      FULL will not allow one column of a multicolumn foreign key to be NULL  unless  all
	      foreign  key  columns are NULL. The default match type allows some foreign key col-
	      umns to be NULL while other parts of the foreign key are not NULL. MATCH PARTIAL is
	      not yet implemented.

	      In  addition,  when  the data in the referenced columns is changed, certain actions
	      are performed on the data in this table's columns. The ON DELETE	clause	specifies
	      the  action  to  do when a referenced row in the referenced table is being deleted.
	      Likewise, the ON UPDATE clause specifies the action to perform  when  a  referenced
	      column  in  the  referenced  table  is  being updated to a new value. If the row is
	      updated, but the referenced column is not actually  changed,  no	action	is  done.
	      There are the following possible actions for each clause:

	      NO ACTION
		     Produce  an error indicating that the deletion or update would create a for-
		     eign key constraint violation. This is the default action.

		     Same as NO ACTION.

		     Delete any rows referencing the deleted row, or update the value of the ref-
		     erencing column to the new value of the referenced column, respectively.

	      SET NULL
		     Set the referencing column values to NULL.

		     Set the referencing column values to their default value.

       If  primary key column is updated frequently, it may be wise to add an index to the REFER-
       ENCES column so that NO ACTION and CASCADE actions associated with the  REFERENCES  column
       can be more efficiently performed.

	      This  controls  whether  the  constraint	can be deferred. A constraint that is not
	      deferrable will be checked immediately after every command. Checking of constraints
	      that  are  deferrable  may be postponed until the end of the transaction (using the
	      SET CONSTRAINTS [set_constraints(7)] command).  NOT DEFERRABLE is the default. Only
	      foreign  key  constraints  currently accept this clause. All other constraint types
	      are not deferrable.

	      If a constraint is deferrable, this clause specifies the default time to check  the
	      constraint.  If  the  constraint	is  INITIALLY IMMEDIATE, it is checked after each
	      statement. This is the default. If the constraint  is  INITIALLY	DEFERRED,  it  is
	      checked  only  at  the  end  of  the  transaction. The constraint check time can be
	      altered with the SET CONSTRAINTS [set_constraints(7)] command.


       Message returned if table is successfully created.


       Message returned if table creation failed. This is usually accompanied by some descriptive
       text, such as: ERROR: Relation 'table' already exists, which occurs at run time if the ta-
       ble specified already exists in the database.

       o Whenever an application makes use of OIDs to identify specific rows of a  table,  it  is
	 recommended  to  create  a  unique constraint on the oid column of that table, to ensure
	 that OIDs in the table will indeed uniquely identify rows even after counter wraparound.
	 Avoid	assuming  that	OIDs are unique across tables; if you need a database-wide unique
	 identifier, use the combination of tableoid and row OID for the purpose. (It  is  likely
	 that  future PostgreSQL releases will use a separate OID counter for each table, so that
	 it will be necessary, not optional, to include tableoid  to  have  a  unique  identifier

	 Tip:  The  use  of WITHOUT OIDS is not recommended for tables with no primary key, since
	 without either an OID or a unique data key, it is difficult to identify specific rows.

       o PostgreSQL automatically creates an index for each unique  constraint	and  primary  key
	 constraint  to  enforce  the uniqueness. Thus, it is not necessary to create an explicit
	 index for primary key columns. (See CREATE INDEX  [create_index(7)]  for  more  informa-

       o The  SQL92 standard says that CHECK column constraints may only refer to the column they
	 apply to; only CHECK table constraints may refer to multiple  columns.  PostgreSQL  does
	 not enforce this restriction; it treats column and table check constraints alike.

       o Unique  constraints  and  primary  keys are not inherited in the current implementation.
	 This makes the combination of inheritance and unique constraints rather dysfunctional.

       Create table films and table distributors:

       CREATE TABLE films (
	   code        CHARACTER(5) CONSTRAINT firstkey PRIMARY KEY,
	   title       CHARACTER VARYING(40) NOT NULL,
	   did	       DECIMAL(3) NOT NULL,
	   date_prod   DATE,
	   kind        CHAR(10),

       CREATE TABLE distributors (
	    name   VARCHAR(40) NOT NULL CHECK (name <> '')

       Create a table with a 2-dimensional array:

       CREATE TABLE array (
	   vector  INT[][]

       Define a unique table constraint for the table films.  Unique  table  constraints  can  be
       defined on one or more columns of the table:

       CREATE TABLE films (
	   code        CHAR(5),
	   title       VARCHAR(40),
	   did	       DECIMAL(3),
	   date_prod   DATE,
	   kind        VARCHAR(10),
	   CONSTRAINT production UNIQUE(date_prod)

       Define a check column constraint:

       CREATE TABLE distributors (
	   did	   DECIMAL(3) CHECK (did > 100),
	   name    VARCHAR(40)

       Define a check table constraint:

       CREATE TABLE distributors (
	   did	   DECIMAL(3),
	   name    VARCHAR(40)
	   CONSTRAINT con1 CHECK (did > 100 AND name <> '')

       Define  a  primary key table constraint for the table films. Primary key table constraints
       can be defined on one or more columns of the table.

       CREATE TABLE films (
	   code        CHAR(5),
	   title       VARCHAR(40),
	   did	       DECIMAL(3),
	   date_prod   DATE,
	   kind        VARCHAR(10),
	   CONSTRAINT code_title PRIMARY KEY(code,title)

       Define a primary key constraint for table distributors. The  following  two  examples  are
       equivalent,  the first using the table constraint syntax, the second the column constraint

       CREATE TABLE distributors (
	   did	   DECIMAL(3),
	   name    CHAR VARYING(40),
	   PRIMARY KEY(did)

       CREATE TABLE distributors (
	   did	   DECIMAL(3) PRIMARY KEY,
	   name    VARCHAR(40)

       This assigns a literal constant default value for the column name, and  arranges  for  the
       default	value  of  column  did	to be generated by selecting the next value of a sequence
       object. The default value of modtime will be the time at which the row is inserted.

       CREATE TABLE distributors (
	   name      VARCHAR(40) DEFAULT 'luso films',
	   did	     INTEGER DEFAULT NEXTVAL('distributors_serial'),

       Define two NOT NULL column constraints on the table distributors, one of which is  explic-
       itly given a name:

       CREATE TABLE distributors (
	   did	   DECIMAL(3) CONSTRAINT no_null NOT NULL,
	   name    VARCHAR(40) NOT NULL

       Define a unique constraint for the name column:

       CREATE TABLE distributors (
	   did	   DECIMAL(3),
	   name    VARCHAR(40) UNIQUE

       The above is equivalent to the following specified as a table constraint:

       CREATE TABLE distributors (
	   did	   DECIMAL(3),
	   name    VARCHAR(40),

       The  CREATE TABLE conforms to SQL92 Intermediate and to a subset of SQL99, with exceptions
       listed below and in the descriptions above.

       In addition to the local temporary table, SQL92 also defines a CREATE GLOBAL TEMPORARY TA-
       BLE statement.  Global temporary tables are also visible to other sessions.

       For temporary tables, there is an optional ON COMMIT clause:


       The  ON	COMMIT	clause	specifies whether or not the temporary table should be emptied of
       rows whenever COMMIT is executed. If the ON COMMIT clause is omitted, SQL92 specifies that
       the  default  is ON COMMIT DELETE ROWS. However, the behavior of PostgreSQL is always like

       The NULL ``constraint'' (actually a non-constraint) is a  PostgreSQL  extension	to  SQL92
       that  is  included  for compatibility with some other RDBMS (and for symmetry with the NOT
       NULL constraint). Since it is the default for any column, its presence is simply noise.

       An assertion is a special type of integrity constraint and shares the  same  namespace  as
       other  constraints.  However,  an assertion is not necessarily dependent on one particular
       table as constraints are, so SQL92 provides the CREATE ASSERTION statement as an alternate
       method for defining a constraint:

       CREATE ASSERTION name CHECK ( condition )

       PostgreSQL does not implement assertions at present.

       Multiple  inheritance  via  the	INHERITS clause is a PostgreSQL language extension. SQL99
       (but not SQL92) defines single inheritance using a different syntax and	different  seman-
       tics. SQL99-style inheritance is not yet supported by PostgreSQL.

       The PostgreSQL concept of OIDs is not standard.

       ALTER TABLE [alter_table(7)], DROP TABLE [drop_table(l)]

SQL - Language Statements		    2002-11-22				  CREATE TABLE(7)

All times are GMT -4. The time now is 04:42 PM.

Unix & Linux Forums Content Copyrightę1993-2018. All Rights Reserved.
Show Password