ALTER TABLE (TSQL Statement)

changes the name of the specified table, or adds or drops a constraint.

Syntax

ALTER TABLE <table_qname>
    WITH NAME = new_table_name


ALTER TABLE <table_qname>
    ALTER COLUMN column_name
    {
          { WITH NAME = new_column_name }
        | { NULL | NOT NULL }
    }


ALTER TABLE <table_qname> [ WITH NOCHECK ]
{
      ADD <table_constraint>

    | DROP [ CONSTRAINT ] constraint_name
}

<table_constraint> ::=
    [ CONSTRAINT constraint_name ]
    { PRIMARY KEY | UNIQUE }
    [ CLUSTERED | NONCLUSTERED ]
    ( column [ ASC ] [ ,...n ] )
    [ ON <filegroup> ]

<table_qname> ::= [ database_name. [ schema_name ] . | schema_name. ] table_name

Arguments

<table_qname>

is the qualified or unqualified name of the table.

WITH NOCHECK

this clause is just ignored by RSQL.

WITH NAME = new_table_name

is the new name of the table.

• this clause is not implemented by MS SQL Server.

ALTER COLUMN column_name

is the column to alter.

WITH NAME = new_column_name

is the new name of the column.

• this clause is not implemented by MS SQL Server.

NULL | NOT NULL

specifies whether the column allows NULL value.

• this clause is not implemented by MS SQL Server.

ADD <table_constraint>

create a new PRIMARY KEY or UNIQUE constraint on the table.

• For more information, see CREATE INDEX.

DROP constraint_name

drops the constraint.

• For more information, see DROP INDEX.

constraint_name

is the name of the constraint or index to drop or to create.

column [ ASC ] [ ,…n ]

list of columns (also called the index key) of the index.

• the optional ASC specifier means ascending sort order and is the default. DESC is not implemented.

ON <file_group>

this clause often exists in scripts generated by MS SQL Server, but it is just ignored by RSQL.

Remarks

Only sa or dbo is allowed to execute this statement.

The clauses WITH NAME = new_table_name, WITH NAME = new_column_name, and NULL | NOT NULL are not implemented by MS SQL Server.

To create the different index types, use the following statements:

Index Type	Statement
PRIMARY KEY	ALTER TABLE ADD PRIMARY KEY ...
UNIQUE	ALTER TABLE ADD UNIQUE ... or CREATE UNIQUE INDEX ...
INDEX	CREATE INDEX ...

See also CREATE INDEX and DROP INDEX.

It is not possible to drop a clustered index. In this case, you must drop the whole table and create a new one.

Examples

CREATE TABLE employees (
    id             BIGINT      NULL,
    name           VARCHAR(30) NOT NULL,
    first_name     VARCHAR(30) NOT NULL,
    date_of_birth  DATE        NULL,
    badge_number   INT         NULL,
);

ALTER TABLE employees ALTER COLUMN id NOT NULL;

ALTER TABLE employees ADD CONSTRAINT pk PRIMARY KEY CLUSTERED (id); 

ALTER TABLE employees ALTER COLUMN date_of_birth WITH NAME = birth_date;

ALTER TABLE employees ADD CONSTRAINT uq_badge UNIQUE (badge_number);

CREATE INDEX idx_name ON employees (name, first_name);

SHOW T employees;

The result is:

CREATE TABLE [dbo].[employees] (
    [id]                  BIGINT               NOT NULL,
    [name]                VARCHAR(30)          NOT NULL,
    [first_name]          VARCHAR(30)          NOT NULL,
    [birth_date]          DATE                 NULL,
    [badge_number]        INT                  NULL,
    CONSTRAINT [pk] PRIMARY KEY CLUSTERED ([id]),
    CONSTRAINT [uq_badge] UNIQUE NONCLUSTERED ([badge_number]),
    INDEX [idx_name] NONCLUSTERED ([name], [first_name])
);