|
Tu
devrais pouvoir faire un ALTER table. Mais il ne permet pas de delete un
champ.
Syntax
ALTER
TABLE [database.[owner].]table_name
[WITH NOCHECK] [ADD {col_name column_properties [column_constraints] | [[,] table_constraint]} [, {next_col_name | next_table_constraint}]...] | [DROP [CONSTRAINT] constraint_name [, constraint_name2]...] where
table_name
Specifies which table to alter. You can include local or global temporary
tables, but FOREIGN KEY constraints are not enforced on temporary
tables.
WITH
NOCHECK
Allows
CHECK or FOREIGN KEY constraints to be added to a table without verifying
existing data for constraint violations. PRIMARY KEY and UNIQUE constraints are
always checked. When this option is not specified (the default), any added
constraints will be validated against existing data. If there are any constraint
violations, the ALTER TABLE statement fails and a message is returned, stating
the type of constraint and name that caused the violation. Use this option with
extreme caution. This option is useful when you know your data already meets the
new constraints or when a business rule requires the constraint to be enforced
only from this point forward.
Note The WITH NOCHECK option will bypass checking FOREIGN KEY and
CHECK constraints only at the time the table is altered. Future data
modifications made against any column will demand that all columns satisfy all
CHECK constraints, even those columns not included in the UPDATE column
list.
ADD
Allows
a column or table-level constraint to be added to an existing
table.
col_name
Is a
new column for the table. Column names must conform to the rules for identifiers
and must be unique in the table.
column_properties =
datatype [NULL | IDENTITY[(seed, increment)]]
datatype
Specifies the datatype of the column. System or user-defined datatypes
are acceptable. Columns added to a table must be defined as NULL. When a column
is added, the initial value for the column will be set to NULL. This restriction
forces the ALTER TABLE statement to fail if the bit or timestamp datatypes are
used.
IDENTITY[(seed, increment)]
Generates values for existing rows based on the seed and increment
parameters. If used, the seed value will be assigned to the first row in the
table and each subsequent row will receive the next identity value, equal to the
last identity plus the increment value. If neither argument is given, both
default to 1.
Note If the maximum value, based on the datatype for the identity
column, is exceeded during the generation of identity values, the ALTER TABLE
statement fails and an error is returned.
The
IDENTITY property cannot be added to an existing column; it can be added only to
a new column. The IDENTITY property can be assigned a tinyint, smallint, int,
decimal(p,0) or numeric(p,0) column that does not allow null values. Defaults
and DEFAULT constraints cannot be bound to an identity column, and an identity
value cannot be changed. Only one column per table can be defined as an identity
column.
column_constraints =
Can
include up to one UNIQUE or FOREIGN KEY constraint, one DEFAULT constraint, and
any number of CHECK constraints for each new or existing column. All can be
entered within the same ALTER TABLE statement.
For a
column-level UNIQUE constraint:
[CONSTRAINT constraint_name]
UNIQUE [CLUSTERED | NONCLUSTERED] [(col_name)] [WITH FILLFACTOR = fillfactor] [ON segment_name] For a
column-level FOREIGN KEY constraint:
[CONSTRAINT constraint_name]
[FOREIGN KEY [(col_name)]] REFERENCES [owner.]ref_table [(ref_col)] For a
column-level DEFAULT constraint:
[CONSTRAINT constraint_name]
DEFAULT {constant_expression | niladic-function | NULL} For a
column-level CHECK constraint:
[CONSTRAINT constraint_name]
CHECK [NOT FOR REPLICATION] (expression) table_constraint =
Can
include up to one PRIMARY KEY constraint per table, one DEFAULT constraint per
column, and any number of FOREIGN KEY, UNIQUE, or CHECK constraints on any
column or columns. All can be entered within the same ALTER TABLE
statement.
For a
table-level PRIMARY KEY constraint:
[CONSTRAINT constraint_name]
PRIMARY KEY [CLUSTERED | NONCLUSTERED] (col_name [, col_name2 [..., col_name16]]) [WITH FILLFACTOR = fillfactor] [ON segment_name] For a
table-level UNIQUE constraint:
[CONSTRAINT constraint_name]
UNIQUE [CLUSTERED | NONCLUSTERED] (col_name [, col_name2 [..., col_name16]]) [WITH FILLFACTOR = fillfactor] [ON segment_name] For a
table-level FOREIGN KEY constraint:
[CONSTRAINT constraint_name]
FOREIGN KEY (col_name [, col_name2 [..., col_name16]]) REFERENCES [owner.]ref_table [(ref_col [, ref_col2 [..., ref_col16]])] For a
table-level DEFAULT constraint:
[CONSTRAINT constraint_name]
DEFAULT {constant_expression | niladic-function | NULL} FOR col_name For a
table-level CHECK constraint:
[CONSTRAINT constraint_name]
CHECK [NOT FOR REPLICATION] (expression) Important When a PRIMARY KEY or UNIQUE constraint is added, an
index is automatically created to enforce the constraint. If the constraint
creates a clustered index, other indexes (previously created with CREATE INDEX
or with other constraints) will need to be rebuilt by the system. Rebuilding
indexes can be a potentially time-intensive operation with numerous concurrency
ramifications. Whenever possible, make data definition changes when database
activity is minimal.
CONSTRAINT constraint_name
Names
the given constraint within the database. Constraint names must follow the rules
for identifiers, except that the name cannot begin with a pound sign (#). If
constraint_name is not supplied, a system-generated name is assigned to the
constraint.
UNIQUE
[CLUSTERED | NONCLUSTERED]
Provides entity integrity for a given column or columns. Although columns
participating in UNIQUE constraints can allow null values, it is not recommended
that you use null values. If null values are necessary, the complete key (a
single column or multiple columns for a composite index) cannot be NULL for more
than one row. Multiple UNIQUE constraints can be defined on a specific table.
SQL Server automatically creates a UNIQUE index on this column or columns.
UNIQUE key constraints can be dropped only by dropping the associated table or
constraint. If no index type is specified, a NONCLUSTERED index is created by
default.
WITH
FILLFACTOR = fillfactor
Specifies how full SQL Server makes each index page when creating an
index with existing data.
ON
segment_name
Creates the index on the specified segment. If the index specified is a
clustered index, the entire table will be moved to the specified segment. It is
important that the segment have at least 1.2 times the space required for the
entire table. For more information, see the CREATE INDEX
statement.
[FOREIGN KEY [(col_name)]] REFERENCES [owner.]ref_table [(ref_col)]
or FOREIGN KEY (col_name [, col_name2 [..., col_name16]]) REFERENCES [owner.]ref_table [(ref_col [, ref_col2 [..., ref_col16]])] Provides single- or multicolumn referential integrity. When defined, the
number of columns and datatypes of each column specified in the FOREIGN KEY
clause must identically match the columns in the REFERENCES clause. Values
entered in this column or columns must exist in the table, and column(s) defined
in the REFERENCES clause and the referenced table's columns should have a
PRIMARY KEY or UNIQUE constraint defined on them. For column-level constraints,
using the FOREIGN KEY and col_name identifiers are optional.
When a FOREIGN KEY constraint is added to a table, all non-null values in the foreign key column(s) must reference an existing key within the referenced table. If any rows are invalid, the ALTER TABLE statement will fail. Important REFERENCE constraints can reference only tables within
the same database; this can include the same table on which the reference is
defined (self-referenced tables). If you want cross-database referential
integrity or custom messaging, implement these through
triggers.
A
table can have a maximum of 31 FOREIGN KEY constraints. This limit is an
absolute upper limit; the maximum may be lower depending on the number of work
tables the server has to create to enforce the constraint and varies by the type
of query being executed. FOREIGN KEY constraints are not enforced for temporary
tables.
FOREIGN KEY constraints, unlike PRIMARY KEY constraints, do not create an index. To improve performance of data retrieval operations, use the CREATE INDEX statement to create an index on a column with a FOREIGN KEY constraint. This will allow for quicker execution times when a referenced key is modified. For a
FOREIGN KEY to be successfully created, the user must have SELECT or REFERENCES
permission on the referenced column or columns. For details, see the GRANT
statement. When a specific reference column(s) is not provided, the primary key
for the referenced table is used.
Note If REFERENCE constraints are added or altered, any stored
procedures that reference the table will be recompiled. This allows changes made
to a table definition (through the ALTER TABLE statement) to be visible to
procedures created prior to the schema change.
DEFAULT constant_expression | niladic-function | NULL
Specifies the value that will be provided for the column when one is not
explicitly supplied during an insert. A DEFAULT constraint can contain constant
values, functions, built-in functions that do not take arguments
(niladic-function), or NULL. A constant_expression must follow the same rules as
defaults.
DEFAULT constraints can be added to columns that are not the timestamp datatype or have the IDENTITY property. If the column was defined with a user-defined datatype that has a default bound to it or if the column has a default bound to it, the DEFAULT constraint will not be allowed and the ALTER TABLE statement will fail. If a default or a DEFAULT constraint already exists for that column, it must be dropped before a DEFAULT constraint can be added. If the default exists on a user-defined datatype, the default must be unbound before that datatype can be used in a table definition with a DEFAULT constraint. Niladic-functions allow a system-supplied value to be inserted when no value is specified. ANSI-standard niladic-functions include: � USER
� CURRENT_USER � SESSION_USER � SYSTEM_USER � CURRENT_TIMESTAMP USER,
CURRENT_USER, and SESSION_USER all default to the database username of the user
performing the insert or update. SYSTEM_USER will provide the login ID, and
CURRENT_TIMESTAMP will provide the same information as the GETDATE() function.
A benefit to using a DEFAULT constraint instead of a default (created with the CREATE DEFAULT statement) is that no explicit binding/unbinding is required and DEFAULT constraints are removed when the table is dropped. When a DEFAULT is added for an existing column (as a table-level constraint), the column to which it applies is specified with FOR col_name. CHECK
(expression)
Enforces domain integrity by limiting the possible values that can be
entered into a column or columns. The search condition must evaluate to a
Boolean expression and cannot contain subqueries. CHECK constraints adhere to
the same policies as rules, but they are automatically bound to the column(s) on
which they are defined and can reference other columns. Multiple CHECK
constraints can be defined for a table; however, only one can be defined per
column per ALTER TABLE statement (although each column constraint can have
multiple conditions). When a rule and one or more CHECK constraints exist for a
column or columns, all restrictions are evaluated. If defined on multiple
columns, they must be defined as table-level constraints.
NOT
FOR REPLICATION
Prevents the specified CHECK constraint from being enforced for the
distribution process used by replication; however, CHECK constraints will be
enforced for all other users (including the system administrator). NOT FOR
REPLICATION is used to protect horizontally partitioned tables that receive
"source" data from a publishing server from data modifications made to the
replicated data. When replication is in effect, any modifications to the data
that is replicated should be made on the publishing server (the main source of
the data) and not on any of the subscribing servers (each of which receives
exact copies of the publishing server's data).
The NOT FOR REPLICATION CHECK constraint will be applied to both the "before" and "after" image of an updated record to prevent records from being added to or deleted from the replicated range. All deletes and inserts will be checked; if they fall within the replicated range, they will be rejected. When NOT FOR REPLICATION is used with ALTER TABLE, existing data is not checked to see if it meets the requirements of the constraint. The NOT FOR REPLICATION clause behaves as though the NOCHECK option were used on the column(s). All future data modifications, except those occurring through replication, will be checked. PRIMARY KEY [CLUSTERED | NONCLUSTERED]
Enforces entity integrity only for an existing column or columns. New
columns cannot be added as a PRIMARY KEY because a new column cannot be defined
NOT NULL and a PRIMARY KEY constraint must be NOT NULL. All columns changed to
PRIMARY KEY constraint columns must have been defined as NOT NULL.
To enforce uniqueness of a primary key, SQL Server automatically creates a unique index on this column or columns. This unique index can be dropped only by dropping the associated table or PRIMARY KEY constraint. If no index type is specified, a clustered index is created by default. If NONCLUSTERED is specified or if CLUSTERED is specified for a different UNIQUE constraint in the same statement block, a nonclustered index is created. Only one PRIMARY KEY constraint can be specified for a given table. However, "alternate" or "candidate" keys can be effectively created with a UNIQUE constraint. DROP
CONSTRAINT constraint_name
Allows
an existing column- or table-level constraint to be removed from the table
definition.
Remarks
ALTER
TABLE adds columns or constraints to a table, or drops constraints from a table,
but it does not allow columns to be removed. When constraints are added, all
existing data will be verified for constraint violations. If you do not want to
verify new CHECK or FOREIGN KEY constraints against existing data, use the WITH
NOCHECK option; but this is not recommended.
The number of columns in a table cannot exceed 250. The maximum number of bytes per row is 1962, not including text or image columns, which are stored with their own separate chain of data pages. Important If a stored procedure using SELECT * references a table
that has been altered with new columns, the procedure (even if you use the WITH
RECOMPILE option) does not recognize the columns you added to the table. In
order for the new columns to be visible to the procedure, you must drop the
stored procedure and then re-create it.
To
rename a table, execute the sp_rename system stored procedure. To get
information on a table and its columns, use the sp_help system stored procedure
or the sp_helpconstraint system stored procedure.
|
- [CCTK] SQL Server 6.5 Netens Laurent
- Duhoux Patrice
