Difference between revisions of "ALTER TABLE"

From Recital Documentation Wiki
Jump to: navigation, search
 
(11 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
=ALTER TABLE=
 
−
 
−
 
−
==Class==
 
−
SQL Applications
 
−
 
−
 
 
==Purpose==
 
==Purpose==
 
Used to add, modify or delete table columns and constraints
 
Used to add, modify or delete table columns and constraints
Line 15: Line 8:
 
ADD [COLUMN] (<column> <datatype> [<column constraints>] [,...])
 
ADD [COLUMN] (<column> <datatype> [<column constraints>] [,...])
 
| &#060;table constraint&#062;
 
| &#060;table constraint&#062;
 +
 
| ALTER | MODIFY [COLUMN] <column> [SET DEFAULT <value> | DROP DEFAULT]
 
| ALTER | MODIFY [COLUMN] <column> [SET DEFAULT <value> | DROP DEFAULT]
 
| (<column> <datatype> [<column constraint>] [,...])
 
| (<column> <datatype> [<column constraint>] [,...])
 +
 
| CONSTRAINT (<column> SET <column constraint> <value> [,...])
 
| CONSTRAINT (<column> SET <column constraint> <value> [,...])
−
| <table constraint>
+
| &#060;table constraint&#062;
 +
 
 
| DROP [COLUMN] <column>
 
| DROP [COLUMN] <column>
 
| (<column> [,...])
 
| (<column> [,...])
 
| CONSTRAINT (<column> <column constraint> [,...]) | &#060;table constraint&#062;
 
| CONSTRAINT (<column> <column constraint> [,...]) | &#060;table constraint&#062;
 +
 
| SET CHECK <condition> [ERROR <message>]
 
| SET CHECK <condition> [ERROR <message>]
 +
 
| RENAME (<column>,<new column>)
 
| RENAME (<column>,<new column>)
  
  
 
==See Also==
 
==See Also==
−
[[ADD TABLE]], [[ALTER INDEX]], [[SQL Constraints|CONSTRAINTS]], [[CREATE TABLE]], [[SQL Data Types|DATA TYPES]], [[GETENV()]], [[SQL INSERT|INSERT]], [[SQL SELECT|SELECT]], [[SET TCACHE]]
+
[[ADD TABLE]], [[ALTER INDEX]], [[SQL Constraints|CONSTRAINTS]], [[CREATE DATABASE]], [[CREATE INDEX]], [[CREATE TABLE]], [[CREATE TRIGGER]], [[SQL Data Types|DATA TYPES]], [[DELETE TRIGGER]], [[DROP DATABASE]], [[DROP INDEX]], [[DROP TABLE]], [[GETENV()]], [[SQL INSERT|INSERT]], [[OPEN DATABASE]], [[SQL SELECT|SELECT]]
−
 
+
  
 
==Description==
 
==Description==
Line 84: Line 81:
 
<code lang="recital">
 
<code lang="recital">
 
// Add new column with column constraints
 
// Add new column with column constraints
−
EXEC SQL
+
ALTER TABLE customer ADD COLUMN timeref CHAR(8);
−
ALTER TABLE customer ADD COLUMN timeref char(8)
+
  CHECK validtime(timeref);
−
CHECK validtime(timeref)
+
  ERROR "Not a valid time string"
−
ERROR "Not a valid time string";
+
  
 
// Alter existing columns to add column constraints
 
// Alter existing columns to add column constraints
−
EXEC SQL
+
ALTER TABLE customer;
−
ALTER TABLE customer
+
  ALTER COLUMN available CALCULATED limit-balance;
−
ALTER COLUMN available CALCULATED limit-balance
+
  ALTER COLUMN limit RECALCULATE;
−
ALTER COLUMN limit RECALCULATE
+
  ALTER COLUMN balance RECALCULATE
−
ALTER COLUMN balance RECALCULATE;
+
  
 
//or
 
//or
−
EXEC SQL
+
ALTER TABLE customer;
−
ALTER TABLE customer
+
  ALTER (available CALCULATED limit-balance,;
−
ALTER (available CALCULATED limit-balance,
+
  limit RECALCULATE,;
−
limit RECALCULATE,
+
  balance RECALCULATE)
−
balance RECALCULATE);
+
  
 
// Add new column, add column constraint,
 
// Add new column, add column constraint,
 
// modify column datatype and drop constraints then drop column
 
// modify column datatype and drop constraints then drop column
−
EXEC SQL
+
ALTER TABLE customer ADD (timeref CHAR(8))
−
ALTER TABLE customer ADD (timeref char(8));
+
  
−
EXEC SQL
+
ALTER TABLE customer;
−
ALTER TABLE customer
+
  ALTER CONSTRAINT;
−
ALTER CONSTRAINT
+
  (timeref SET CHECK validtime(timeref);
−
(timeref SET CHECK validtime(timeref)
+
  ERROR "Not a valid time string")
−
ERROR "Not a valid time string");
+
  
−
EXEC SQL
+
ALTER TABLE customer;
−
ALTER TABLE customer
+
  ALTER (timeref datetime);
−
ALTER (timeref datetime)
+
  DROP CONSTRAINT (timeref CHECK, timeref ERROR)
−
DROP CONSTRAINT (timeref CHECK, timeref ERROR);
+
  
−
EXEC SQL
+
ALTER TABLE customer DROP (timeref)
−
ALTER TABLE customer DROP (timeref);
+
  
 
// Add an ONUPDATE table constraint  
 
// Add an ONUPDATE table constraint  
−
EXEC SQL
+
ALTER TABLE customer;
−
ALTER TABLE customer
+
  MODIFY ONUPDATE "check_update"
−
MODIFY ONUPDATE "do check_update";
+
  
 
// Add and then remove CHECK table constraint
 
// Add and then remove CHECK table constraint
−
EXEC SQL
+
ALTER TABLE customer SET CHECK checkit() error "Invalid operation"
−
ALTER TABLE customer SET CHECK checkit() error "Invalid operation";
+
−
 
+
−
set sql off
+
−
use customer
+
−
display dictionary
+
−
edit  && Save and Exit will call validation
+
−
use
+
  
−
EXEC SQL
+
ALTER TABLE customer DROP CHECK
−
ALTER TABLE customer DROP CHECK;
+
  
 
// Rename column
 
// Rename column
−
EXEC SQL
+
ALTER TABLE customer RENAME(first_name,forename)
−
ALTER TABLE customer RENAME(first_name,forename);
+
 
</code>
 
</code>
  
  
 
==Products==
 
==Products==
−
Recital Database Server, Recital Mirage Server, Recital Terminal Developer
+
Recital Server, Recital  
 
[[Category:Documentation]]
 
[[Category:Documentation]]
 
[[Category:SQL]]
 
[[Category:SQL]]
 
[[Category:Commands]]
 
[[Category:Commands]]

Latest revision as of 16:34, 30 July 2013

Purpose

Used to add, modify or delete table columns and constraints


Syntax

ALTER [IGNORE] TABLE [<database>!]<table>

ADD [COLUMN] (<column> <datatype> [<column constraints>] [,...]) | <table constraint>

| ALTER | MODIFY [COLUMN] <column> [SET DEFAULT <value> | DROP DEFAULT] | (<column> <datatype> [<column constraint>] [,...])

| CONSTRAINT (<column> SET <column constraint> <value> [,...]) | <table constraint>

| DROP [COLUMN] <column> | (<column> [,...]) | CONSTRAINT (<column> <column constraint> [,...]) | <table constraint>

| SET CHECK <condition> [ERROR <message>]

| RENAME (<column>,<new column>)


See Also

ADD TABLE, ALTER INDEX, CONSTRAINTS, CREATE DATABASE, CREATE INDEX, CREATE TABLE, CREATE TRIGGER, DATA TYPES, DELETE TRIGGER, DROP DATABASE, DROP INDEX, DROP TABLE, GETENV(), INSERT, OPEN DATABASE, SELECT

Description

The ALTER TABLE command is used to add, modify or delete table columns and constraints and to rename columns. The ALTER TABLE statement automatically reloads the original data of the table back into the original columns. You must have ALTER privilege on the table. The table will be locked for EXCLUSIVE use during the operation.


Keywords Description
IGNORE If IGNORE is omitted and there are duplicate UNIQUE keys in the table, the ALTER TABLE is aborted with an error. If IGNORE is specified, records containing a duplicate UNIQUE key are deleted, leaving only the first row with that key.
database The name of the database to which the table to be altered belongs. Databases in Recital are implemented as directories containing files that correspond to the tables and associated files in the database. Operating System file protection can be applied individually to the files for added security. The directory is a sub-directory of the Recital data directory. The environment variable / symbol DB_DATADIR points to the current Recital data directory and can be queried using the GETENV() function. Files from other directories can be added to the database using the ADD TABLE command or via the database catalog and SET AUTOCATALOG functionality. The '!' character must be included between the database name and the table name.
table The name of the table to be altered.
ADD This will insert one or more new columns into the table
COLUMN Optional COLUMN keyword.
column The name of the column to operate on.
datatype The data type to be stored in the column, and the applicable length or precision.
column constraint The column constraint.
table constraint The table constraint.
MODIFY These are used to change existing column definitions and column and table constraints.
SET DEFAULT <expr> Specify the DEFAULT column constraint to set a default value for the specified column. The <expr> must evaluate to the same data type as the target column. The column's value can subsequently be updated.
DROP DEFAULT Remove the DEFAULT column constraint for the specified column.
DROP This is used to delete existing column definitions and column and table constraints.
CONSTRAINT This keyword is used if the constraint refers to a column.
SET Precedes an existing column constraint whose value is being changed.
value The new value for the specified column constraint.
SET CHECK <condition> Specify CHECK table constraint. This validation is activated when an operation to insert, update or delete records in the table is called. The <condition> specified must evaluate to True (.T.) for the operation to succeed. If the <condition> evaluates to False (.F.) the operation is abandoned and the ERROR table constraint message is displayed. If the ERROR table constraint has not been defined, a default error message is displayed.
ERROR <message> Specify ERROR table constraint. The <message> is the error message to be displayed if the CHECK table constraint evaluates to False (.F.).
RENAME This is used to change the name of an existing column.
new column The new name for the column.


Example

// Add new column with column constraints
ALTER TABLE customer ADD COLUMN timeref CHAR(8);
  CHECK validtime(timeref);
  ERROR "Not a valid time string"
 
// Alter existing columns to add column constraints
ALTER TABLE customer;
  ALTER COLUMN available CALCULATED limit-balance;
  ALTER COLUMN limit RECALCULATE;
  ALTER COLUMN balance RECALCULATE
 
//or
ALTER TABLE customer;
  ALTER (available CALCULATED limit-balance,;
  limit RECALCULATE,;
  balance RECALCULATE)
 
// Add new column, add column constraint,
// modify column datatype and drop constraints then drop column
ALTER TABLE customer ADD (timeref CHAR(8))
 
ALTER TABLE customer;
  ALTER CONSTRAINT;
  (timeref SET CHECK validtime(timeref);
  ERROR "Not a valid time string")
 
ALTER TABLE customer;
  ALTER (timeref datetime);
  DROP CONSTRAINT (timeref CHECK, timeref ERROR)
 
ALTER TABLE customer DROP (timeref)
 
// Add an ONUPDATE table constraint 
ALTER TABLE customer;
  MODIFY ONUPDATE "check_update"
 
// Add and then remove CHECK table constraint
ALTER TABLE customer SET CHECK checkit() error "Invalid operation"
 
ALTER TABLE customer DROP CHECK
 
// Rename column
ALTER TABLE customer RENAME(first_name,forename)


Products

Recital Server, Recital

Retrieved from "https://www.recitalsoftware.com/wiki/index.php?title=ALTER_TABLE&oldid=16134"