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
orUNIQUE
constraint on the table.- For more information, see
CREATE INDEX
.
- For more information, see
DROP
constraint_name- drops the constraint.
- For more information, see
DROP INDEX
.
- For more information, see
- 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.
- the optional
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, andNULL | 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])
);