SQL command to modify the table structure of SQL Server database

Time:2020-10-22

Specific nonsense, please see the following code details.

Add a varchar column to the table:

  ALTER TABLE distributors ADD COLUMN address varchar(30);

To delete a field from the 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 timestamp into 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’;

To rename an existing field:

  ALTER TABLE distributors RENAME COLUMN address TO city;

To change the name of an existing table:

  ALTER TABLE distributors RENAME TO suppliers;

Add a non NULL constraint to a field:

  ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

Remove a non NULL 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);

Delete audit constraints for a table and all 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 (multi field) unique constraint to the table:

  ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);

Add an auto 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 table space:

  ALTER TABLE distributors SET TABLESPACE fasttablespace;

PS: SQL command line modify database

Add columns:

alter table tableName add columnName varchar(30) 

Modify column type:

alter table tableName alter column columnName varchar(4000)

Modify the name of the column:

EXEC sp_ rename ‘ tableName.column1 ‘,’column2’ (change the column name of column1 with tablename to column2)

Delete column:

alter table tableName drop column columnName

The above content is the whole description of this paper, I hope to help you.

Recommended Today

Comparison and analysis of Py = > redis and python operation redis syntax

preface R: For redis cli P: Redis for Python get ready pip install redis pool = redis.ConnectionPool(host=’39.107.86.223′, port=6379, db=1) redis = redis.Redis(connection_pool=pool) Redis. All commands I have omitted all the following commands. If there are conflicts with Python built-in functions, I will add redis Global command Dbsize (number of returned keys) R: dbsize P: print(redis.dbsize()) […]