SQL修改数据库表的列属性.doc

SQL修改数据库表的列属性(ALTER语句)我们可以用客户端的语句改写，psql比如把数据库表journal里的keyword,ekeyword属性改为character(350),原来为character(200),那么我们可以这样操作：psql postgres -c "ALTER TABLE journal ALTER keyword TYPE character(350)"psql postgres -c "ALTER TABLE journal ALTER ekeyword TYPE character(350)"好了，修改结束。附ALTER语句的文档。ALTER TABLE ONLY name * action, . ALTER TABLE ONLY name * RENAME COLUMN columnTOnew_columnALTER TABLEnameRENAME TOnew_nameALTER TABLEnameSET SCHEMAnew_schema这里action是下列之一：ADD COLUMN columntypecolumn_constraint . DROP COLUMN column RESTRICT | CASCADE ALTER COLUMN columnTYPEtype USINGexpressionALTER COLUMN columnSET DEFAULTexpressionALTER COLUMN columnDROP DEFAULTALTER COLUMN column SET | DROP NOT NULLALTER COLUMN columnSET STATISTICSintegerALTER COLUMN columnSET STORAGE PLAIN | EXTERNAL | EXTENDED | MAIN ADDtable_constraintDROP CONSTRAINTconstraint_name RESTRICT | CASCADE DISABLE TRIGGER trigger_name| ALL | USER ENABLE TRIGGER trigger_name| ALL | USER CLUSTER ONindex_nameSET WITHOUT CLUSTERSET WITHOUT OIDSOWNER TOnew_ownerSET TABLESPACEnew_tablespace描述ALTER TABLE变更一个现存表的定义。它有好几种子形式：ADD COLUMN这种形式用和CREATE TABLE里一样的语法向表中增加一个新的字段。DROP COLUMN这种形式从表中删除一个字段。请注意，和这个字段相关的索引和表约束也会被自动删除。 如果任何表之外的对象依赖于这个字段， 你必须说CASCADE，比如，外键参考，视图等等。ALTER COLUMN TYPE这种类型改变表中一个字段的类型。该字段涉及的索引和简单的表约束将被自动地转换为使用新的字段类型， 方法是重新分析最初提供的表达式。可选的USING子句声明如何从旧的字段值里计算新的字段值； 如果省略，那么缺省的转换就是从旧类型像新类型的赋值转换。如果从旧数据类型到新类型没有隐含或者赋值的转换， 那么必须提供一个USING。SET/DROP DEFAULT这种形式为一个字段设置或者删除缺省值。请注意缺省值只应用于随后的INSERT命令； 它们不会导致已经在表中的行的数值的修改。我们也可以为视图创建缺省， 这个时候它们是在视图的ON INSERT规则应用之前插入INSERT语句中去的。SET/DROP NOT NULL这些形式修改一个字段是否标记为允许 NULL 值或者是拒绝 NULL 值。 如果表在字段中包含非空值，那么你只可以SET NOT NULL。SET STATISTICS这个形式为随后的ANALYZE操作设置每字段的统计收集目标（default_statistics_target）。 目标的范围可以在 0 到 1000 之内设置；另外，把他设置为 -1 则表示重新恢复到使用系统缺省的统计目标。 有关PostgreSQL查询规划器使用的统计信息的更多信息， 请参考Section 13.2。SET STORAGE这种形式为一个字段设置存储模式。这个设置控制这个字段是内联保存还是保存在一个附属的表里，以及数据是否要压缩。PLAIN必需用于定长的数值，比如integer，并且是内联的，不压缩的。MAIN用于内联，可压缩的数据。EXTERNAL用于外部保存，不压缩的数据， 而EXTENDED用于外部的压缩数据。EXTENDED是大多数支持非PLAIN存储的数据之缺省。 使用EXTERNAL将令在text字段上的子字串操作更快， 付出的代价是增加了存储空间。 请注意SET STORAGE本身并不改变表上的任何东西， 只是设置将来的表操作时，建议使用的策略。参阅Section 50.2获取更多信息。ADDtable_constraint这个形式给表增加一个新的约束，用的语法和CREATE TABLE一样。DROP CONSTRAINT这个形式删除一个表上的约束。 目前，在表上的约束不要求有唯一的名字，因此可能有多个约束匹配声明的名字。 所有这样的约束都将被删除。DISABLE/ENABLE TRIGGER这个形式关闭或者打开属于该表的触发器。 一个被关闭掉的触发器是系统仍然知道的，但是在触发器事件发生的时候不会被执行。 对于一个推迟了的触发器，在事件发生的时候会检查打开状态，而不是在函数实际执行的时候。 我们可以通过申明名字的方法打开或者关闭任意一个触发器， 或者是该表上的所有触发器，或者只是用户触发器（这个选项排除了那些用于实现外键约束的触发器）。 打开或者关闭约束触发器要求超级用户权限； 这么做的时候应该小心，因为如果触发器不执行的话，约束保证的数据完整性也就没有办法确保了。CLUSTER这种形式为将来的CLUSTER选项选择缺省索引。它实际上并不重新对表建簇。SET WITHOUT CLUSTER这种形式从表中删除最新使用的CLUSTER索引。 这样会影响将来那些没有声明索引的建簇操作。SET WITHOUT OIDS这种形式从表中删除oid系统字段。 它和DROP COLUMN oid RESTRICT完全相同， 只不过是如果表上已经没有oid字段了，那么它不会报告错误。请注意，不存在某种ALTER TABLE的变种可以在删除了 OID 之后再把它们恢复回来。RENAMERENAME形式改变一个表的名字（或者是一个索引，一个序列，或者一个视图）或者是表中一个独立字段的名字。 它对存储的数据没有任何影响。OWNER这个形式改变表，序列或者视图的所有者为指定所有者。SET TABLESPACE这种形式把表的表空间修改为指定的表空间并且把与表相关的数据文件移动到新的表空间去。 如果在表上呦索引，则不会移动。但是他们可以通过额外的SET TABLESPACE命令移动。参阅CREATE TABLESPACE。RENAMERENAME形式改变一个表（或者一个索引，序列，或者视图）的名字， 或者是表中独立字段的名字。它们对存储的数据没有影响。SET SCHEMA这种形式把表移动到另外一个模式。相关的索引，约束，以及 SERIAL 字段的序列都跟着移动。除了RENAME和SET SCHEMA之外所有动作都可以捆绑再一个多次修改的列表中同时施用。 比如，我们可以在一个命令里增加几个字段和/或修改几个字段的类型。 对于大表，这么做特别有用，因为只需要对该表做一次处理。要使用ALTER TABLE，你必需拥有该表。 要修改一个表的模式，你必须在新的模式上有CREATE权限。要修改所有者，你必须还是新的所有角色的直接或者间接的成员， 并且该角色在该表的模式上必须拥有CREATE权限。 （这样的限制就保证了修改所有者这个动作和删除、重建该表能做到的动作没有什么区别。 不过，超级用户可以修改任何表的所有者。）参数table试图更改的现存表（可能有模式修饰）的名称。 如果声明了ONLY，则只更改该表。 如果没有声明ONLY，则该表及其所有后代表（如果有）都被更新。 我们可以在表名字后面附加一个*表示后代表都被扫描，但是在目前的版本里，这是缺省行为。 （在7.1之前的版本，ONLY是缺省的行为。）缺省可以通过改变配置选项sql_inheritance来改变。column现存或新的字段名称。new_column现存字段的新名称。new_name表的新名称。type新字段的类型，或者现存字段的新类型。table_constraint表的新的约束定义。constraint_name要删除的现有约束的名字。trigger_name要打开或者关闭的单个触发器的名字。ALL打开或者关闭属于该表的所有触发器。 （如果这些触发器中有外键约束，那么就要求超级用户权限。）USER打开或者关闭所有属于该表的非外键约束触发器。index_name要标记为建簇的表上面的索引名字。CASCADE自动删除依赖于被依赖字段或者约束的对象（比如，引用该字段的视图）。RESTRICT如果字段或者约束还有任何依赖的对象，则拒绝删除该字段。 这是缺省行为。new_owner该表的新所有者的用户名。new_tablespace这个表将要移动往的表空间名字。new_schema表将移动前往的模式的名字。注意COLUMN关键字是多余的，可以省略。如果用ADD COLUMN增加一个字段，那么所有表中现有行都初始化为该字段的缺省值 （如果没有声明DEFAULT子句，那么就是 NULL）。用一个非空缺省增加一个字段或者是改变一个字段的现有类型会要求整个表的重写。 对于大表来说，这个操作可能会花很长时间；并且它还临时需要两倍的磁盘空间。增加一个CHECK或者NOT NULL约束要求扫描该表以保证现有的行复合约束要求。提供在一个ALTER TABLE里面声明多个修改的主要原因是原先需要的对表的多次扫描和重写可以组合成一个回合。DROP COLUMN命令并不是物理上把字段删除， 而只是简单地把它标记为 SQL 操作中不可见的。随后对该表的插入和更新将在该字段存储一个 NULL。 因此，删除一个字段是很快的，但是它不会立即缩减你的表在磁盘上的大小，因为被删除了的字段占据的空间还没有回收。 这些空间将随着现有的行的更新而得到回收。ALTER TYPE要求重写整个表的特性有时候是一个优点， 因为重写的过程消除了任何表中的没用的空间。比如，要想立刻回收被一个已经删除的字段占据的空间， 最快的方法是ALTER TABLE table ALTER COLUMN anycol TYPE anytype;这里anycol是任何在表中还存在的字段，而anytype是和该字段的原类型一样的类型。 这样的结果是在表上没有任何可见的语意的变化，但是这个命令强迫重写，这样就删除了不再使用的数据。ALTER TYPE的USING选项实际上可以声明涉及该行旧值的任何表达式； 也就是说，它可以引用除了正在被转换的字段之外其它的字段。这样，我们就可以用ALTER TYPE语法做非常普遍性的转换。因为这个灵活性，USING表达式并没有施用于该字段的缺省值（如果有的话）； 结果可能不是缺省表达式要求的常量表达式。 这就意味着如果从旧类型到新类型如果没有隐含或者赋值转换的话， 那么即使存在USING子句的情况下，ALTER TYPE也可能无法把缺省值转换成新的类型。 在这种情况下，我们应该用DROP DEFAULT先删除缺省， 执行ALTER TYPE，然后使用SET DEFAULT增加一个合适的新缺省。 类似的考虑也适用于涉及该字段的索引和约束。如果表有任何后代表，那么如果不在后代表上做同样的修改的话， 就不允许在父表上增加，重命名或者修改一个字段的类型，也就是说，ALTER TABLE ONLY将被拒绝。这样就保证了后代表总是有和父表匹配的字段。一个递归DROP COLUMN操作将只有在后代表并不从任何其它父表中继承该字段并且从来没有独立定义该字段的时候才能删除一个后代表的字段。 一个非递归的DROP COLUMN（也就是，ALTER TABLE ONLY . DROP COLUMN）从来不会删除任何后代字段， 而是把他们标记为独立定义的，而不是继承的。TRIGGER，CLUSTER，OWNER，和TABLESPACE行为绝不会递归到后代表； 也就是说，它们的行为就像总是声明了ONLY一样。 添加一个约束只能在CHECK约束上递归。不允许更改系统表结构的任何部分。请参考CREATE TABLE部分获取更多有效参数的描述。Chapter 5里有更多有关继承的信息。例子向表中增加一个varchar列：ALTER TABLE distributors ADD COLUMN address varchar(30);从表中删除一个字段：ALTER TABLE distributors DROP COLUMN address RESTRICT;在一个操作中修改两个现有字段的类型：ALTER TABLE distributorsALTER COLUMN address TYPE varchar(80),ALTER COLUMN name TYPE varchar(100);使用一个USING子句， 把一个包含 UNIX 时间戳的 integer 字段转化成timestamp with time zone：ALTER TABLE fooALTER COLUMN foo_timestamp TYPE timestamp with time zoneUSINGtimestamp with time zone 'epoch' + foo_timestamp * interval '1 second'对现存字段改名：ALTER TABLE distributors RENAME COLUMN address TO city;更改现存表的名字ALTER TABLE distributors RENAME TO suppliers;给一个字段增加一个非空约束：ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;从一个字段里删除一个非空约束：ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;给一个表增加一个检查约束：ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);删除一个表和它的所有子表的监查约束：ALTER TABLE distributors DROP CONSTRAINT zipchk;向表中增加一个外键约束：ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses(address) MATCH FULL;给表增加一个（多字段）唯一约束：ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);给一个表增加一个自动命名的主键约束，要注意的是一个表只能有一个主键：ALTER TABLE distributors ADD PRIMARY KEY (dist_id);把表移动到另外一个表空间：ALTER TABLE distributors SET TABLESPACE fasttablespace;把表移动到另外一个模式：ALTER TABLE myschema.distributors SET SCHEMA yourschema;兼容性ADD，DROP，和SET DEFAULT形式与 SQL 标准兼容。 其它形式是PostgreSQL对 SQL 标准的扩展。 还有，在一个ALTER TABLE命令里声明多于一个操作也是扩展。ALTER TABLE DROP COLUMN可以用于删除表中的唯一的一个字段， 留下一个零字段的表。这是对 SQL 的扩展，它不允许零字段