Edit C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\odbc\7.0.1\help\reference\sqlsupport.12.06.html
<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xml:lang="en" lang="en" xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html;charset=utf-8" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <!-- MOTW-DISABLED saved from url=(0014)about:internet --> <title>Alter Table</title> <link rel="StyleSheet" href="css/sqlsupport.css" type="text/css" media="all" /> <link rel="StyleSheet" href="css/webworks.css" type="text/css" media="all" /> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/context.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/towwhdir.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/wwhpagef.js"></script> <script type="text/javascript" language="JavaScript1.2"> <!-- var WebWorksRootPath = ""; // --> </script> <script type="text/javascript" language="JavaScript1.2"> <!-- // Set reference to top level help frame // var WWHFrame = WWHGetWWHFrame("", true); // --> </script> <script type="text/javascript" language="JavaScript1.2" src="scripts/expand.js"></script> </head> <body class="" style="background-color: #FFFFEE;" onload="WWHUpdate();" onunload="WWHUnload();" onkeydown="WWHHandleKeyDown((document.all||document.getElementById||document.layers)?event:null);" onkeypress="WWHHandleKeyPress((document.all||document.getElementById||document.layers)?event:null);" onkeyup="WWHHandleKeyUp((document.all||document.getElementById||document.layers)?event:null);"> <br /> <div class="WebWorks_Breadcrumbs" style="text-align: left;"> <a class="WebWorks_Breadcrumb_Link" href="sqlsupport.12.01.html#250110">10 SQL Statements and Extensions for the Salesforce Driver</a> : Alter Table</div> <hr align="left" /> <blockquote> <div class="head_1"><a name="250110">Alter Table</a></div> <div class="Body"><a name="250111">The Alter Table statement adds a column, removes a column, or redefines a column in a </a>table. The table being altered can be either a remote or local table. A remote table is a Salesforce object and is exposed in the SFORCE schema. A local table is maintained by the driver and is local to the machine on which the driver is running. A local table is exposed in the PUBLIC schema. </div> <table class="Format_B" style="margin-top: 0.0pt;" cellspacing="0" summary=""> <caption></caption> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="244166">For information on...</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="244168">See</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="244170">Altering a remote table</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.06.html#233214', '');" name="244172">â??Altering a Remote Tableâ??</a></span></div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="244174">Altering a local table</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.06.html#234027', '');" name="244176">â??Altering a Local Tableâ??</a></span></div> </td> </tr> </table> <div class="head_2"><a name="233214">Altering a Remote Table</a></div> <div class="gutter_text_right"><a name="234257">Grammar</a></div> <div class="syntax_first"><a name="234338">ALTER TABLE </a><span class="EquationVariables">table_name</span></div> <div class="syntax"><a name="234258">[</a><span class="EquationVariables">add_clause</span>]</div> <div class="syntax"><a name="234259">[</a><span class="EquationVariables">drop_clause</span>]</div> <div class="Body"><a name="234260">where:</a></div> <div class="Body"><span class="EquationVariables"><a name="234261">table_name</a></span> specifies an existing remote table.</div> <div class="Body"><span class="EquationVariables"><a name="234262">add_clause</a></span> specifies a column or a foreign key constraint to be added to the table. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.06.html#234359', '');">â??Add Clause: Columnsâ??</a></span> and <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.06.html#234373', '');">â??Add Clause: Constraintsâ??</a></span> for a complete explanation.</div> <div class="Body"><span class="EquationVariables"><a name="234268">drop_clause</a></span> specifies a column to be dropped from the table. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.06.html#234386', '');">â??Drop Clause: Columnsâ??</a></span> for a complete explanation.</div> <div class="Body"><a name="237190">NOTE: You cannot drop a constraint from a remote table.</a></div> <div class="head_3"><a name="234359">Add Clause: Columns</a></div> <div class="Body"><a name="234360">Use the Add clause to add a column to an existing table. It is optional.</a></div> <div class="gutter_text_right"><a name="236255">Grammar</a></div> <div class="syntax_first"><a name="236256">ADD [COLUMN] </a><span class="EquationVariables">column_name</span> <span class="EquationVariables">Datatype</span> ...</div> <div class="syntax"><a name="236257">[DEFAULT </a><span class="EquationVariables">default_value</span>] [[NOT]NULL] [EXT_ID] [PRIMARY KEY] <br />[START WITH <span class="EquationVariables">starting_value</span>]</div> <div class="Body"><a name="236258">This clause adds a column to the table. It defines a column with the same syntax as the </a>Create Table command (see <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.11.html#238267', '');">â??Column Definition for Remote Tablesâ??</a></span>). If <span class="Syntax">NOT NULL</span> is specified and the table is not empty, a default value must be specified. In all other respects, this command is the equivalent of a column definition in a Create Table statement.</div> <div class="Body"><span class="EquationVariables"><a name="236383">default_value</a></span> is the default value to be assigned to the column. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.11.html#238267', '');">â??Column Definition for Remote Tablesâ??</a></span> for details.</div> <div class="Body"><a name="236262">You cannot specify ANYTYPE, BINARY, COMBOBOX, or TIME data types in the column </a>definition of Alter Table statements.</div> <div class="Body"><a name="236264">If a SQL view includes </a><span class="Syntax">SELECT * FROM</span> for the table to which the column was added in the viewâ??s Select statement, the new column is added to the view.</div> <div class="Body"><span class="EquationVariables"><a name="252644">starting_value</a></span> is the starting value for the Identity column. The default start value is <span class="Syntax">0</span>.</div> <div class="gutter_text_right"><a name="249553">Examples</a></div> <div class="hanging_label_head"><a name="236455">Example A</a></div> <div class="Body"><a name="236462">Assuming the current schema is SFORCE, this example adds the </a><span class="Syntax">status</span> column with a default value of <span class="Syntax">ACTIVE</span> to the <span class="Syntax">test</span> table. </div> <div class="syntax_first"><a name="236700">ALTER TABLE test ADD COLUMN status TEXT(30) DEFAULT 'ACTIVE'</a></div> <div class="hanging_label_head"><a name="236778">Example B</a></div> <div class="Body"><a name="236779">Assuming the current schema is SFORCE, this example adds a </a><span class="Syntax">deptId</span> column that can be used as a foreign key column. </div> <div class="syntax_first"><a name="236780">ALTER TABLE test ADD COLUMN deptId TEXT(18)</a></div> <div class="head_3"><a name="234373">Add Clause: Constraints</a></div> <div class="Body"><a name="237143">Use the Add clause to add a constraint to an existing table. It is optional.</a></div> <div class="gutter_text_right"><a name="234374">Grammar</a></div> <div class="syntax_first"><a name="234375">ADD [CONSTRAINT </a><span class="EquationVariables">constraint_name</span>] ...</div> <div class="Body"><a name="234376">This command adds a constraint using the same syntax as the Create Table command (see </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.11.html#241198', '');">â??Constraint Definition for Remote Tablesâ??</a></span>).</div> <div class="Body"><a name="234383">NOTES: </a></div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="237099">The only type of constraint you can add is a foreign key constraint.</a></div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="237112">When adding a foreign key constraint, the table that contains the foreign key must be </a>empty.</div> </td> </tr> </table> </div> <div class="hanging_label_head"><a name="237027">Example A</a></div> <div class="Body"><a name="237028">Assuming the current schema is SFORCE, a foreign key constraint is added to the </a><span class="Syntax">deptId</span> column of the <span class="Syntax">test</span> table, referencing the <span class="Syntax">rowId</span> of the <span class="Syntax">dept</span> table. For the operation to succeed, the <span class="Syntax">dept</span> table must be empty.</div> <div class="syntax_first"><a name="237029">ALTER TABLE test ADD FOREIGN KEY (deptId) REFERENCES dept(rowId)</a></div> <div class="head_3"><a name="234386">Drop Clause: Columns</a></div> <div class="Body"><a name="234387">Use the Drop clause to drop a column from an existing table. It is optional.</a></div> <div class="gutter_text_right"><a name="234389">Grammar</a></div> <div class="syntax_first"><a name="234390">DROP {[COLUMN] </a><span class="EquationVariables">column_name</span>}</div> <div class="Body"><a name="234391">where </a><span class="EquationVariables">column_name</span> specifies an existing column in an existing table.</div> <div class="Body"><a name="237313">NOTES: </a></div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="237386">The column being dropped cannot have a constraint defined on it.</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="237333">Drop fails if a SQL view includes the column.</a></div> </td> </tr> </table> </div> <div class="hanging_label_head"><a name="237287">Example A</a></div> <div class="Body"><a name="237288">This example drops the </a><span class="Syntax">status</span> column. For the operation to succeed, the status column cannot have a constraint defined on it and cannot be used in a SQL view.</div> <div class="syntax_first"><a name="237289">ALTER TABLE test DROP COLUMN status</a></div> <div class="head_2"><a name="234027">Altering a Local Table</a></div> <div class="gutter_text_right"><a name="144049">Grammar</a></div> <div class="syntax_first"><a name="228203">ALTER TABLE </a><span class="EquationVariables">table_name</span></div> <div class="syntax"><a name="144051">[</a><span class="EquationVariables">add_clause</span>]</div> <div class="syntax"><a name="144052">[</a><span class="EquationVariables">drop_clause</span>]</div> <div class="syntax"><a name="144053">[</a><span class="EquationVariables">rename_clause</span>]</div> <div class="Body"><a name="144054">where:</a></div> <div class="Body"><span class="EquationVariables"><a name="144055">table_name</a></span> specifies an existing local table.</div> <div class="Body"><span class="EquationVariables"><a name="144056">add_clause</a></span> specifies a column or constraint to be added to the table. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.06.html#144070', '');">â??Add Clause: Columnsâ??</a></span> and <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.06.html#226455', '');">â??Add Clause: Constraintsâ??</a></span> for a complete explanation.</div> <div class="Body"><span class="EquationVariables"><a name="144060">drop_clause</a></span> specifies a column or constraint to be dropped from the table. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.06.html#144093', '');">â??Drop Clause: Columnsâ??</a></span> and <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.06.html#224085', '');">â??Drop Clause: Constraintsâ??</a></span> for a complete explanation.</div> <div class="Body"><span class="EquationVariables"><a name="144064">rename_clause</a></span> specifies a new name for the table. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.06.html#144103', '');">â??Rename Clauseâ??</a></span> for a complete explanation.</div> <div class="gutter_text_right"><a name="228215"> </a></div> <div class="head_3"><a name="144070">Add Clause: Columns</a></div> <div class="Body"><a name="144071">Use the Add clause to add a column to an existing table. It is optional.</a></div> <div class="gutter_text_right"><a name="236215">Grammar</a></div> <div class="syntax_first"><a name="236216">ADD [COLUMN] </a><span class="EquationVariables">column_name</span> <span class="EquationVariables">Datatype</span> ...</div> <div class="syntax"><a name="236217">[BEFORE </a><span class="EquationVariables">existing_column</span>]</div> <div class="Body"><a name="236218">This clause adds a column to the end of the column list. It defines a column with the same </a>syntax as the Create Table command (see <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.11.html#157932', '');">â??Column Definition for Local Tablesâ??</a></span>). If <span class="Syntax">NOT NULL</span> is specified and the table is not empty, a default value must be specified. In all other respects, this command is the equivalent of a column definition in a Create Table statement.</div> <div class="Body"><a name="236222">You cannot specify ANYTYPE, BINARY, COMBOBOX, or TIME data types in the column </a>definition of Alter Table statements.</div> <div class="Body"><a name="236223">The optional </a><span class="Syntax">Before</span> <span class="EquationVariables">existing_column</span> can be used to specify the name of an existing column so that the new column is inserted in a position just before the existing column.</div> <div class="Body"><a name="236225">If a SQL view includes </a><span class="Syntax">SELECT * FROM</span> for the table to which the column was added in the viewâ??s Select statement, the new column is added to the view.</div> <div class="gutter_text_right"><a name="249572">Examples</a></div> <div class="hanging_label_head"><a name="237510">Example A</a></div> <div class="Body"><a name="237511">Assuming the current schema is PUBLIC, this example adds the </a><span class="Syntax">status</span> column with a default value of <span class="Syntax">ACTIVE</span> to the <span class="Syntax">test</span> table. </div> <div class="syntax_first"><a name="237512">ALTER TABLE test ADD COLUMN status VARCHAR(30) DEFAULT 'ACTIVE'</a></div> <div class="hanging_label_head"><a name="237513">Example B</a></div> <div class="Body"><a name="237514">Assuming the current schema is PUBLIC, this example adds a </a><span class="Syntax">deptId</span> column that can be used as a foreign key column. </div> <div class="syntax_first"><a name="237489">ALTER TABLE test ADD COLUMN deptId VARCHAR(18)</a></div> <div class="head_3"><a name="226455">Add Clause: Constraints</a></div> <div class="Body"><a name="237463">Use the Add clause to add a constraint to an existing table. It is optional.</a></div> <div class="gutter_text_right"><a name="144083">Grammar</a></div> <div class="syntax_first"><a name="144084">ADD [CONSTRAINT </a><span class="EquationVariables">constraint_name</span>] ...</div> <div class="Body"><a name="144085">This command adds a constraint using the same syntax as the Create Table command (see </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.11.html#147380', '');">â??Constraint Definition for Local Tablesâ??</a></span>).</div> <div class="Body"><a name="144089">NOTES:</a></div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="229760">You cannot add a Unique constraint if one is already assigned to the same column list. </a>A Unique constraint works only if the values of the columns in the constraint columns list for the existing rows are unique or include a Null value.</div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="144090">Adding a foreign key constraint to the table fails if, for each existing row in the referring </a>table, a matching row (with equal values for the column list) is not found in the referenced table.</div> </td> </tr> </table> </div> <div class="gutter_text_right"><a name="249596">Example</a></div> <div class="hanging_label_head"><a name="237605">Example A</a></div> <div class="Body"><a name="237606">Assuming the current schema is PUBLIC, this example adds a foreign key constraint to the </a><span class="Syntax">deptId</span> column of the <span class="Syntax">test</span> table that references the rowId of the <span class="Syntax">dept</span> table.</div> <div class="syntax_first"><a name="237578">ALTER TABLE test ADD CONSTRAINT test_fk FOREIGN KEY (deptId) REFERENCES </a>dept(id)</div> <div class="head_3"><a name="144093">Drop Clause: Columns</a></div> <div class="Body"><a name="224064">Use the Drop clause to drop a column from an existing table. It is optional.</a></div> <div class="gutter_text_right"><a name="144649">Grammar</a></div> <div class="syntax_first"><a name="173795">DROP {[COLUMN] </a><span class="EquationVariables">column_name</span>}</div> <div class="Body"><a name="144096">where </a><span class="EquationVariables">column_name</span> specifies an existing column in an existing table.</div> <div class="Body"><a name="224202">Drop fails if a SQL view includes the column.</a></div> <div class="gutter_text_right"><a name="249620">Example</a></div> <div class="hanging_label_head"><a name="237627">Example A</a></div> <div class="Body"><a name="237628">This example drops the </a><span class="Syntax">status</span> column. For the operation to succeed, the status column cannot have a constraint defined on it and cannot be used in a SQL view.</div> <div class="syntax_first"><a name="237629">ALTER TABLE test DROP COLUMN status</a></div> <div class="head_3"><a name="224085">Drop Clause: Constraints</a></div> <div class="Body"><a name="237888">Use the Drop clause to drop a constraint from an existing table. It is optional.</a></div> <div class="gutter_text_right"><a name="224190">Grammar</a></div> <div class="syntax_first"><a name="224191">DROP {[CONSTRAINT] </a><span class="EquationVariables">constraint_name</span>}</div> <div class="Body"><a name="224192">where:</a></div> <div class="Body"><span class="EquationVariables"><a name="224089">constraint_name</a></span> specifies an existing constraint.</div> <div class="Body"><a name="230854">NOTE: The specified constraint cannot be a primary key constraint or unique constraint.</a></div> <div class="gutter_text_right"><a name="237964">Example</a></div> <div class="hanging_label_head"><a name="249644">Example A</a></div> <div class="hanging_label_1"><a name="238046">This example drops the </a><span class="Syntax">test_fk</span> constraint.</div> <div class="syntax_first"><a name="237996">ALTER TABLE test DROP CONSTRAINT test_fk</a></div> <div class="head_3"><a name="144103">Rename Clause</a></div> <div class="Body"><a name="144104">Use the Rename clause to rename an existing table. It is optional.</a></div> <div class="gutter_text_right"><a name="144760">Grammar</a></div> <div class="syntax_first"><a name="144105">RENAME TO </a><span class="EquationVariables">new_name</span></div> <div class="Body"><a name="144106">where </a><span class="EquationVariables">new_name</span> specifies the new name for the table.</div> <div class="gutter_text_right"><a name="237907">Example</a></div> <div class="hanging_label_head"><a name="249676">Example A</a></div> <div class="hanging_label_1"><a name="238061">This example renames the table to </a><span class="Syntax">test2</span>.</div> <div class="syntax_first"><a name="238062">ALTER TABLE test RENAME TO test2</a></div> <script type="text/javascript" language="JavaScript1.2"> <!-- // Clear related topics // WWHClearRelatedTopics(); document.writeln(WWHRelatedTopicsInlineHTML()); // --> </script> </blockquote> <script type="text/javascript" language="JavaScript1.2"> <!-- document.write(WWHRelatedTopicsDivTag() + WWHPopupDivTag() + WWHALinksDivTag()); // --> </script> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de