What should we do if we need to modify the SQL Server table structure? Here's how you can modify the SQL Server table structure to help you learn about the structure of your SQL Server table.
add a varchar column to the SQL Server table:
ALTER TABLE Distributors ADD COLUMN address varchar (30);
To delete a field from the SQL Server table:
ALTER TABLE Distributors DROP COLUMN address RESTRICT;
To modify the type of two existing fields in one operation:
ALTER TABLE Distributors
ALTER COLUMN address TYPE varchar (80),
ALTER COLUMN name TYPE varchar (100);
Converts an integer field containing the UNIX timestamp to timestamp with time zone using a using clause:
ALTER TABLE foo
ALTER COLUMN foo_timestamp TYPE timestamp with time zone
USING
Timestamp with time zone ' epoch ' + foo_timestamp * interval ' 1 second ';
Change the name of an existing field:
ALTER TABLE Distributors RENAME COLUMN address to City;
To 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 is not NULL;
Removes 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);
Deletes the OMV constraint for a table and all of its child tables:
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 (multiple-field) Unique constraint to a table:
ALTER TABLE Distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, ZipCode);
To add an automatically named PRIMARY KEY constraint to a table, note that a table can have only one primary key:
ALTER TABLE Distributors ADD PRIMARY KEY (dist_id);
To move a table to another table space:
ALTER TABLE Distributors SET tablespace fasttablespace;