SQL Server table structure modification Summary

add and delete fields, add and delete constraints, modify default values, modify field data types, rename fields, and rename tables. All these actions are executed using the alter table command.

Add a field
to add a field, run the following command:
alter table products add column description text; the new field will first fill in the given default value for existing rows in the table (if you do not declare the default clause, the default value is null ).
You can also define constraints on the field and use the common syntax:
alter table products add column description text check (description <>''); in fact, all the options described in create table that can be applied to fields can be used here. However, we should note that the default value must meet the given constraints, otherwise the add operation will fail. In addition, you can add a constraint after you correctly fill in the value of the new field (see the following section ).

delete a field
to delete a field, run the following command:
alter table products drop column description; no matter what data is in the field, it will be hourly. The constraints related to this field will also be deleted. However, if this field is referenced by the foreign key of another table, PostgreSQL will not implicitly Delete this constraint. You can authorize cascade to delete anything dependent on this field:
alter table products drop column description cascade; see section 5.11 to obtain information about the mechanisms behind these operations.

Add a constraint
to add a constraint, use the table constraint syntax. For example:
alter table products add check (name <> '');
alter table products add constraint some_name unique (product_no);
alter table products add foreign key (product_group_id) References product_groups; to add a non-empty constraint that cannot be written as a table constraint, use the following syntax:
alter table products alter column product_no set Not NULL;
This constraint is checked immediately. Therefore, the table must meet the constraints before adding the constraint.

delete a constraint
to delete a constraint, you need to know its name. If you give it a name, it's easy. Otherwise, the system will allocate a generated name so that you can find it. The Psql command \ D tablename can be used here for help; Other interfaces may also provide methods to check the details of the table. Then run the following command:
alter table products drop constraint some_name; (if you are processing a generated constraint name, such as $2, don't forget to add double quotation marks to it to make it a valid identifier .)
like deleting a field, if you want to delete a field that is subject to dependency, you need to use cascade. In one example, a foreign key constraint depends on the unique constraint or primary key constraint on the referenced field.
except for non-null constraints, all constraints are used in this way. To delete a non-empty type, use
alter table products alter column product_no drop not null; (remember that the non-empty constraint has no name .)

set the default value of a field
to set the default value for a field, run the following command:
alter table products alter column price set default 7.77; note that this will not affect the existing data rows in any table, it only changes the default value for future insert commands.

Delete the default value , use
alter table products alter column PRI Ce drop default; this is actually equivalent to setting the default value to null. The result is that it is not an error to delete a default value that has not been defined, because the default implicit value is a null value.

modify the Data Type of a field
convert a field to another data type, run the following command:
alter table products alter column price type numeric (10, 2 ); only when the existing items in the field can be converted to the new type by an implicit type conversion. If you need more complex conversions, you can add a using clause that declares how to calculate new values from the old values.
PostgreSQL will try to convert the default value (if any) of a field to a new type, as well as any constraints related to this field. However, these conversions may fail or may generate strange results. Before modifying a field type, you 'd better delete those constraints and add them manually modified.

rename a field
rename a field:
alter table products rename column product_no to product_number;
change the field name
rename a field:
alter table products rename column product_no to product_number;
rename a table
rename a table:
alter table products Rename to items;

 

 

 

 

Transferred from:Http://hi.baidu.com/zjx_8090/blog/item/8271bc2a9fbdbb4c4ec22622.html