ddl.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

    To add a not-null constraint, which cannot be written as a table
    constraint, use this syntax:
<programlisting>
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
</programlisting>
   </para>

   <para>
    The constraint will be checked immediately, so the table data must
    satisfy the constraint before it can be added.
   </para>
  </sect2>

  <sect2>
   <title>Removing a Constraint</title>

   <indexterm>
    <primary>constraint</primary>
    <secondary>removing</secondary>
   </indexterm>

   <para>
    To remove a constraint you need to know its name.  If you gave it
    a name then that's easy.  Otherwise the system assigned a
    generated name, which you need to find out.  The
    <application>psql</application> command <literal>\d
    <replaceable>tablename</replaceable></literal> can be helpful
    here; other interfaces might also provide a way to inspect table
    details.  Then the command is:
<programlisting>
ALTER TABLE products DROP CONSTRAINT some_name;
</programlisting>
    (If you are dealing with a generated constraint name like <literal>$2</>,
    don't forget that you'll need to double-quote it to make it a valid
    identifier.)
   </para>

   <para>
    As with dropping a column, you need to add <literal>CASCADE</> if you
    want to drop a constraint that something else depends on.  An example
    is that a foreign key constraint depends on a unique or primary key
    constraint on the referenced column(s).
   </para>

   <para>
    This works the same for all constraint types except not-null
    constraints. To drop a not null constraint use
<programlisting>
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
</programlisting>
    (Recall that not-null constraints do not have names.)
   </para>
  </sect2>

  <sect2>
   <title>Changing a Column's Default Value</title>

   <indexterm>
    <primary>default value</primary>
    <secondary>changing</secondary>
   </indexterm>

   <para>
    To set a new default for a column, use a command like this:
<programlisting>
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
</programlisting>
    Note that this doesn't affect any existing rows in the table, it
    just changes the default for future <command>INSERT</> commands.
   </para>

   <para>
    To remove any default value, use
<programlisting>
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;
</programlisting>
    This is effectively the same as setting the default to null.
    As a consequence, it is not an error
    to drop a default where one hadn't been defined, because the
    default is implicitly the null value.
   </para>
  </sect2>

  <sect2>
   <title>Changing a Column's Data Type</title>

   <indexterm>
    <primary>column data type</primary>
    <secondary>changing</secondary>
   </indexterm>

   <para>
    To convert a column to a different data type, use a command like this:
<programlisting>
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
</programlisting>
    This will succeed only if each existing entry in the column can be
    converted to the new type by an implicit cast.  If a more complex
    conversion is needed, you can add a <literal>USING</> clause that
    specifies how to compute the new values from the old.
   </para>

   <para>
    <productname>PostgreSQL</> will attempt to convert the column's
    default value (if any) to the new type, as well as any constraints
    that involve the column.  But these conversions may fail, or may
    produce surprising results.  It's often best to drop any constraints
    on the column before altering its type, and then add back suitably
    modified constraints afterwards.
   </para>
  </sect2>

  <sect2>
   <title>Renaming a Column</title>

   <indexterm>
    <primary>column</primary>
    <secondary>renaming</secondary>
   </indexterm>

   <para>
    To rename a column:
<programlisting>
ALTER TABLE products RENAME COLUMN product_no TO product_number;
</programlisting>
   </para>
  </sect2>

  <sect2>
   <title>Renaming a Table</title>

   <indexterm>
    <primary>table</primary>
    <secondary>renaming</secondary>
   </indexterm>

   <para>
    To rename a table:
<programlisting>
ALTER TABLE products RENAME TO items;
</programlisting>
   </para>
  </sect2>
 </sect1>

 <sect1 id="ddl-priv">
  <title>Privileges</title>

  <indexterm zone="ddl-priv">
   <primary>privilege</primary>
  </indexterm>

  <indexterm>
   <primary>permission</primary>
   <see>privilege</see>
  </indexterm>

  <para>
   When you create a database object, you become its owner.  By
   default, only the owner of an object can do anything with the
   object. In order to allow other users to use it,
   <firstterm>privileges</firstterm> must be granted.  (However,
   users that have the superuser attribute can always
   access any object.)
  </para>

  <para>
   There are several different privileges: <literal>SELECT</>,
   <literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>,
   <literal>RULE</>, <literal>REFERENCES</>, <literal>TRIGGER</>,
   <literal>CREATE</>, <literal>TEMPORARY</>, <literal>EXECUTE</>, and
   <literal>USAGE</>.  The privileges applicable to a particular
   object vary depending on the object's type (table, function, etc).
   For complete information on the different types of privileges
   supported by <productname>PostgreSQL</productname>, refer to the
   <xref linkend="sql-grant" endterm="sql-grant-title"> reference
   page.  The following sections and chapters will also show you how
   those privileges are used.
  </para>

  <para>
   The right to modify or destroy an object is always the privilege of
   the owner only.
  </para>

  <note>
   <para>
    To change the owner of a table, index, sequence, or view, use the
    <xref linkend="sql-altertable" endterm="sql-altertable-title">
    command.  There are corresponding <literal>ALTER</> commands for
    other object types.
   </para>
  </note>

  <para>
   To assign privileges, the <command>GRANT</command> command is
   used. For example, if <literal>joe</literal> is an existing user, and
   <literal>accounts</literal> is an existing table, the privilege to
   update the table can be granted with
<programlisting>
GRANT UPDATE ON accounts TO joe;
</programlisting>
   To grant a privilege to a group, use this syntax:
<programlisting>
GRANT SELECT ON accounts TO GROUP staff;
</programlisting>
   The special <quote>user</quote> name <literal>PUBLIC</literal> can
   be used to grant a privilege to every user on the system. Writing
   <literal>ALL</literal> in place of a specific privilege grants all
   privileges that are relevant for the object type.
  </para>

  <para>
   To revoke a privilege, use the fittingly named
   <command>REVOKE</command> command:
<programlisting>
REVOKE ALL ON accounts FROM PUBLIC;
</programlisting>
   The special privileges of the object owner (i.e., the right to do
   <command>DROP</>, <command>GRANT</>, <command>REVOKE</>, etc.)
   are always implicit in being the owner,
   and cannot be granted or revoked.  But the object owner can choose
   to revoke his own ordinary privileges, for example to make a
   table read-only for himself as well as others.
  </para>

  <para>
   Ordinarily, only the object's owner (or a superuser) can grant or
   revoke privileges on an object.  However, it is possible to grant a
   privilege <quote>with grant option</>, which gives the recipient
   the right to grant it in turn to others.  If the grant option is
   subsequently revoked then all who received the privilege from that
   recipient (directly or through a chain of grants) will lose the
   privilege.  For details see the <xref linkend="sql-grant"
   endterm="sql-grant-title"> and <xref linkend="sql-revoke"
   endterm="sql-revoke-title"> reference pages.
  </para>
 </sect1>

 <sect1 id="ddl-schemas">
  <title>Schemas</title>

  <indexterm zone="ddl-schemas">
   <primary>schema</primary>
  </indexterm>

  <para>
   A <productname>PostgreSQL</productname> database cluster
   contains one or more named databases.  Users and groups of users are
   shared across the entire cluster, but no other data is shared across
   databases.  Any given client connection to the server can access
   only the data in a single database, the one specified in the connection
   request.
  </para>

  <note>
   <para>
    Users of a cluster do not necessarily have the privilege to access every
    database in the cluster.  Sharing of user names means that there
    cannot be different users named, say, <literal>joe</> in two databases
    in the same cluster; but the system can be configured to allow
    <literal>joe</> access to only some of the databases.
   </para>
  </note>

  <para>
   A database contains one or more named <firstterm>schemas</>, which
   in turn contain tables.  Schemas also contain other kinds of named
   objects, including data types, functions, and operators.  The same
   object name can be used in different schemas without conflict; for
   example, both <literal>schema1</> and <literal>myschema</> may
   contain tables named <literal>mytable</>.  Unlike databases,
   schemas are not rigidly separated: a user may access objects in any
   of the schemas in the database he is connected to, if he has
   privileges to do so.
  </para>

  <para>
   There are several reasons why one might want to use schemas:

   <itemizedlist>
    <listitem>
     <para>
      To allow many users to use one database without interfering with
      each other.
     </para>
    </listitem>

    <listitem>
     <para>
      To organize database objects into logical groups to make them
      more manageable.
     </para>
    </listitem>

    <listitem>
     <para>
      Third-party applications can be put into separate schemas so
      they cannot collide with the names of other objects.
     </para>
    </listitem>
   </itemizedlist>

   Schemas are analogous to directories at the operating system level,
   except that schemas cannot be nested.
  </para>

  <sect2 id="ddl-schemas-create">
   <title>Creating a Schema</title>

   <indexterm zone="ddl-schemas-create">
    <primary>schema</primary>
    <secondary>creating</secondary>
   </indexterm>

   <para>
    To create a schema, use the command <command>CREATE
    SCHEMA</command>.  Give the schema a name of your choice.  For
    example:
<programlisting>
CREATE SCHEMA myschema;
</programlisting>
   </para>

   <indexterm>
    <primary>qualified name</primary>
   </indexterm>

   <indexterm>
    <primary>name</primary>
    <secondary>qualified</secondary>
   </indexterm>

   <para>
    To create or access objects in a schema, write a
    <firstterm>qualified name</> consisting of the schema name and
    table name separated by a dot:
<synopsis>
<replaceable>schema</><literal>.</><replaceable>table</>
</synopsis>
    This works anywhere a table name is expected, including the table
    modification commands and the data access commands discussed in
    the following chapters.
    (For brevity we will speak of tables only, but the same ideas apply
    to other kinds of named objects, such as types and functions.)
   </para>

   <para>
    Actually, the even more general syntax
<synopsis>
<replaceable>database</><literal>.</><replaceable>schema</><literal>.</><replaceable>table</>
</synopsis>
    can be used too, but at present this is just for <foreignphrase>pro
    forma</> compliance with the SQL standard.  If you write a database name,
    it must be the same as the database you are connected to.
   </para>

   <para>
    So to create a table in the new schema, use
<programlisting>
CREATE TABLE myschema.mytable (
 ...
);
</programlisting>
   </para>

   <indexterm>
    <primary>schema</primary>
    <secondary>removing</secondary>
   </indexterm>

   <para>
    To drop a schema if it's empty (all objects in it have been
    dropped), use
<programlisting>
DROP SCHEMA myschema;
</programlisting>
    To drop a schema including all contained objects, use
<programlisting>
DROP SCHEMA myschema CASCADE;
</programlisting>
    See <xref linkend="ddl-depend"> for a description of the general
    mechanism behind this.
   </para>

   <para>
    Often you will want to create a schema owned by someone else
    (since this is one of the ways to restrict the activities of your
    users to well-defined namespaces).  The syntax for that is:
<programlisting>
CREATE SCHEMA <replaceable>schemaname</replaceable> AUTHORIZATION <replaceable>username</replaceable>;