What should we do if we need to modify the SQL server table structure? The following describes how to modify the SQL server table structure.
Add a varchar column to the SQL server table:
Alter table distributors add column address varchar (30 );
Delete a field from the SQL server table:
Alter table distributors drop column address RESTRICT;
Modify the types of two existing fields in one operation:
Alter table distributors
Alter column address TYPE varchar (80 ),
Alter column name TYPE varchar (100 );
Use a USING clause to convert an integer field containing UNIX timestamps to timestamp with time zone:
Alter table foo
Alter column foo_timestamp TYPE timestamp with time zone
USING
Timestamp with time zone 'epoch' + foo_timestamp * interval '1 second ';
Rename existing fields:
Alter table distributors rename column address TO city;
Change the name of an existing SQL server table:
Alter table distributors rename to suppliers;
Add a non-empty constraint to a field:
Alter table distributors alter column street set not null;
Delete A non-empty constraint from a field:
Alter table distributors alter column street drop not null;
Add a check constraint to a table:
Alter table distributors add constraint zipchk CHECK (char_length (zipcode) = 5 );
To delete a table and all its sub-tables, follow these constraints:
Alter table distributors drop constraint zipchk;
Add a foreign key constraint to the table:
Alter table distributors add constraint distfk foreign key (address) REFERENCES addresses (address) match full;
Add a unique (Multi-field) constraint to the table:
Alter table distributors add constraint dist_id_zipcode_key UNIQUE (dist_id, zipcode );
Add an automatically named primary key constraint to a table. Note that a table can only have one primary key:
Alter table distributors add primary key (dist_id );
Move the table to another tablespace:
Alter table distributors set tablespace fasttablespace;