Edit C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\odbc\7.0.1\help\reference\sqlsupport.12.11.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>Create 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#206855">10 SQL Statements and Extensions for the Salesforce Driver</a> : Create Table</div> <hr align="left" /> <blockquote> <div class="head_1"><a name="206855">Create Table</a></div> <div class="Body"><a name="206856">The Create Table statement creates a new table. You can create either a remote or local </a>table. A remote table is a Salesforce object and is exposed in the SFORCE schema. Creating a table in the SFORCE schema creates a remote table. 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. Creating a table in the PUBLIC schema creates a local table.</div> <div class="Body"><a name="243670">NOTE: Creating tables in Salesforce is not a quick operation. It can take several minutes for </a>Salesforce to create the table and its relationships.</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="243766">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="243768">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="243770">Creating 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.11.html#238218', '');" name="243772">â??Creating 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="243774">Creating 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.11.html#238107', '');" name="243776">â??Creating a Local Tableâ??</a></span></div> </td> </tr> </table> <div class="head_2"><a name="238218">Creating a Remote Table</a></div> <div class="gutter_text_right"><a name="238219">Grammar</a></div> <div class="syntax_first"><a name="238220">CREATE TABLE </a><span class="EquationVariables">table_name</span> (<span class="EquationVariables">column_definition</span> [, ...]</div> <div class="syntax"><a name="238221">[, </a><span class="EquationVariables">constraint_definition</span>...]) </div> <div class="Body"><a name="238222">where:</a></div> <div class="Body"><span class="EquationVariables"><a name="243713">table_name</a></span> specifies the name of the new remote table. The table name can be qualified by a schema name using the format <span class="EquationVariables">schema</span>.<span class="EquationVariables">table</span>. If the schema is not specified, the table is created in the current schema. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.05.html#112073', '');">â??Alter Session (EXT)â??</a></span> for information about changing the current schema. </div> <div class="Body"><span class="EquationVariables"><a name="238226">column_definition</a></span> specifies the definition of a column in the new table. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.11.html#238267', '');">â??Column Definition for Remote Tablesâ??</a></span> for a complete explanation. </div> <div class="Body"><span class="EquationVariables"><a name="238228">constraint_definition</a></span> specifies constraints on the columns of the new table. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.11.html#241198', '');">â??Constraint Definition for Remote Tablesâ??</a></span> for a complete explanation. </div> <div class="head_3"><a name="238267">Column Definition for Remote Tables</a></div> <div class="gutter_text_right"><a name="271482">Grammar</a></div> <div class="syntax_first"><span class="EquationVariables"><a name="238268">column_name</a></span> <span class="EquationVariables">Datatype</span> [(<span class="EquationVariables">precision</span>[,<span class="EquationVariables">scale</span>])...] </div> <div class="syntax"><a name="238269">[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="238270">where:</a></div> <div class="Body"><span class="EquationVariables"><a name="238271">column_name</a></span> is the name to be assigned to the column.</div> <div class="Body"><span class="EquationVariables"><a name="238275">Datatype</a></span> is the data type of the column to be created. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'salesforce.27.06.html#932173', '');">â??Data Typesâ??</a></span> in <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'salesforce.27.01.html#1222348', '');">Chapter 21</a></span> of the <span class="Emphasis">DataDirect Connect Series for ODBC Userâ??s Guide</span> for a list of supported Salesforce data types. You cannot specify ANYTYPE, BINARY, COMBOBOX, ENCRYPTEDTEXT, or TIME data types in the column definition of Create Table statements.</div> <div class="Body"><span class="EquationVariables"><a name="238276">precision</a></span> is the total number of digits for DECIMAL columns, the number of seconds for DATETIME columns, and the length of HTML, LONGTEXTAREA, and TEXT columns.</div> <div class="Body"><span class="EquationVariables"><a name="238277">scale</a></span> is the number of digits to the right of the decimal point for DECIMAL columns.</div> <div class="Body"><span class="EquationVariables"><a name="238280">default_value</a></span> is the default value to be assigned to the column. The following default values are allowed in column definitions for remote tables:</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="241840">For character columns, a single-quoted string or NULL.</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="241841">For datetime columns, a single-quoted Date, Time, or Timestamp value or NULL. You </a>can also use the following datetime SQL functions: CURRENT_DATE, CURRENT_ TIMESTAMP, TODAY, or NOW.</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="241842">For boolean columns, the literals FALSE, TRUE, NULL.</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="241843">For numeric columns, any valid number or NULL.</a></div> </td> </tr> </table> </div> <div class="Body"><span class="EquationVariables"><a name="238282">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="Body"><span class="Syntax"><a name="238283">[NOT]NULL</a></span> is used to specify whether NULL values are allowed or not allowed in a column. If <span class="Syntax">NOT NULL</span> is specified, all rows in the table must have a column value. If <span class="Syntax">NULL</span> is specified or if neither <span class="Syntax">NULL</span> or <span class="Syntax">NOT NULL</span> is specified, NULL values are allowed in the column.</div> <div class="Body"><span class="Syntax"><a name="238284">EXT_ID</a></span> is used to specify that the column is an external ID column.</div> <div class="Body"><span class="Syntax"><a name="238285">PRIMARY KEY</a></span> can only be specified when the data type of the column is ID. ID columns are always the primary key column for Salesforce.</div> <div class="Body"><span class="Syntax"><a name="238619">START WITH</a></span> specifies the sequence of numbers generated for the Identity column. It can only be used when the data type of the column definition is AUTONUMBER.</div> <div class="gutter_text_right"><a name="247387">Examples</a></div> <div class="hanging_label_head"><a name="247388">Example A</a></div> <div class="Body"><a name="247389">Assuming the current schema is SFORCE, the remote table </a><span class="Syntax">Test</span> is created in the SFORCE schema. The <span class="Syntax">id</span> column has a starting value of <span class="Syntax">1000</span>.</div> <div class="syntax_first"><a name="247390">CREATE TABLE Test (id AUTONUMBER START WITH 1000, Name TEXT(30))</a></div> <div class="hanging_label_head"><a name="247391">Example B </a></div> <div class="Body"><a name="247392">The table name is qualified with a schema name that is not the current schema, creating the </a><span class="Syntax">Test</span> table in the <span class="Syntax">SFORCE</span> schema. The table is created with the following columns: <span class="Syntax">id</span>, <span class="Syntax">Name</span>, and <span class="Syntax">Status</span>. The <span class="Syntax">Status</span> column contains a default value of <span class="Syntax">ACTIVE</span>.</div> <div class="syntax_first"><a name="247393">CREATE TABLE SFORCE.Test (id NUMBER(9, 0), Name TEXT(30), Status TEXT(10) </a></div> <div class="syntax"><a name="247394">DEFAULT 'ACTIVE')</a></div> <div class="hanging_label_head"><a name="247428">Example C</a></div> <div class="Body"><a name="247429">Assuming the current schema is SFORCE, the remote table </a><span class="Syntax">dept</span> is created with the <span class="Syntax">name</span> and <span class="Syntax">deptId</span> columns. The <span class="Syntax">deptId</span> column can be used as an external ID column.</div> <div class="syntax_first"><a name="247430">CREATE TABLE dept (name TEXT(30), deptId NUMBER(9, 0) EXT_ID)</a></div> <div class="head_3"><a name="241198">Constraint Definition for Remote Tables</a></div> <div class="gutter_text_right"><a name="260240">Grammar</a></div> <div class="syntax_first"><a name="260241">[CONSTRAINT [</a><span class="EquationVariables">constraint_name</span>] </div> <div class="syntax"><a name="260242"> {foreign_key_constraint</a><span class="Syntax">}</span>]</div> <div class="Body"><a name="260323">where:</a></div> <div class="Body"><span class="EquationVariables"><a name="261609">constraint_name</a></span> is ignored. The driver uses the Salesforce relationship naming convention to generate the constraint name.</div> <div class="Body"><span class="EquationVariables"><a name="260362">foreign_key_constraint</a></span> defines a link between related tables. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.11.html#261690', '');">â??Foreign Key Clauseâ??</a></span> for syntax.</div> <div class="Body"><a name="261038">A column defined as a foreign key in one table references a primary key in the related table. </a>Only values that are valid in the primary key are valid in the foreign key. The following example is valid because the foreign key values of the dept id column in the EMP table match those of the id column in the referenced table DEPT:</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;" colspan="2"> <div class="table_column_heads"><a name="261041">Referenced Table</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="261045"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;" colspan="3"> <div class="table_column_heads"><a name="261047">Main Table</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;" colspan="2"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="261053">DEPT</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="text-align: Center;"><a name="261057"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;" colspan="3"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="261059">EMP</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" style="text-align: Center;"><a name="261065"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="text-align: Center;"><a name="261067"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="text-align: Center;"><a name="261069"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="text-align: Center;"><a name="261071"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="text-align: Center;"><a name="261073"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="261075">(Foreign Key)</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" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="261077">id</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="261079">name</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><a name="261081"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="261083">id</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="261085">name</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="261087">dept id</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="syntax" style="text-align: Center;"><a name="261089">1</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261091">Dev</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261093"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261095">1</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261097">Mark</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261099">1</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="syntax" style="text-align: Center;"><a name="261101">2</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261103">Finance</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261105"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261107">1</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261109">Jim</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261111">3</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="syntax" style="text-align: Center;"><a name="261113">3</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261115">Sales</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261117"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261119">1</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261121">Mike</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261123">2</a></div> </td> </tr> </table> <div class="Body"><a name="261125">The following example, however, is not valid. The value 4 in the dept id column does not </a>match any value in the referenced id column of the DEPT table.</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;" colspan="2"> <div class="table_column_heads" style="text-align: Center;"><a name="261128">Referenced Table</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" style="text-align: Center;"><a name="261132"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;" colspan="3"> <div class="table_column_heads" style="text-align: Center;"><a name="261134">Main Table</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;" colspan="2"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="261140">DEPT</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="text-align: Center;"><a name="261144"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;" colspan="3"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="261146">EMP</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" style="text-align: Center;"><a name="261152"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="text-align: Center;"><a name="261154"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="text-align: Center;"><a name="261156"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="text-align: Center;"><a name="261158"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="text-align: Center;"><a name="261160"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="261162">(Foreign Key)</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" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="261164">id</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="261166">name</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><a name="261168"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="261170">id</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="261172">name</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="261174">dept id</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="syntax" style="text-align: Center;"><a name="261176">1</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261178">Dev</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261180"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261182">1</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261184">Mark</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261186">1</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="syntax" style="text-align: Center;"><a name="261188">2</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261190">Finance</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261192"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261194">1</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261196">Jim</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261198">3</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="syntax" style="text-align: Center;"><a name="261200">3</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261202">Sales</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261204"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261206">1</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261208">Mike</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="261210">4</a></div> </td> </tr> </table> <div class="head_4"><a name="261690">Foreign Key Clause</a></div> <div class="gutter_text_right"><a name="260126">Grammar</a></div> <div class="syntax_first"><a name="255665">FOREIGN KEY (</a><span class="EquationVariables">fcolumn_name</span>) </div> <div class="syntax"><a name="255666"> REFERENCES </a><span class="EquationVariables">ref_table</span> (<span class="EquationVariables">pcolumn_name</span>)</div> <div class="Body"><a name="241205">where:</a></div> <div class="Body"><span class="EquationVariables"><a name="241458">fcolumn_name</a></span> specifies the foreign key column to which the constraint is applied. The data type of this column must be the same as the data type of the column it references.</div> <div class="Body"><span class="EquationVariables"><a name="241459">ref_table</a></span> specifies the table to which the foreign key refers.</div> <div class="Body"><span class="EquationVariables"><a name="260947">pcolumn_name</a></span> specifies the primary key column in the referenced table. For Salesforce, the primary key column is always the rowId column.</div> <div class="gutter_text_right"><a name="260949">Example</a></div> <div class="hanging_label_head"><a name="248626">Example A</a></div> <div class="Body"><a name="248379">Assuming the current schema is SFORCE, the remote table </a><span class="Syntax">emp</span> is created with the <span class="Syntax">name</span>, <span class="Syntax">empId</span>, and <span class="Syntax">deptId</span> columns. The table contains a foreign key constraint on the <span class="Syntax">deptId</span> column, referencing the <span class="Syntax">rowId</span> in the <span class="Syntax">dept</span> table created in <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.11.html#247428', '');">â??Example Câ??</a></span>. For the operation to succeed, the data type of the <span class="Syntax">deptId</span> column must be the same as that of the <span class="Syntax">rowId</span> column.</div> <div class="syntax_first"><a name="248383">CREATE TABLE emp (name TEXT(30), empId NUMBER(9, 0) EXT_ID, deptId TEXT(18), </a>FOREIGN KEY(deptId) REFERENCES dept(rowId))</div> <div class="head_2"><a name="238107">Creating a Local Table</a></div> <div class="gutter_text_right"><a name="147330">Grammar</a></div> <div class="syntax_first"><a name="196568">CREATE [{MEMORY | DISK | [GLOBAL] {TEMPORARY | TEMP}] </a></div> <div class="syntax"><a name="147332">TABLE </a><span class="EquationVariables">table_name</span> (<span class="EquationVariables">column_definition</span> [, ...] </div> <div class="syntax"><a name="249736">[, </a><span class="EquationVariables">constraint_definition</span>...]) </div> <div class="syntax"><a name="147988">[ON COMMIT {DELETE | PRESERVE} ROWS]</a></div> <div class="Body"><a name="147335">where:</a></div> <div class="Body"><span class="Syntax"><a name="157703">MEMORY</a></span> creates the new table in memory. The data for a memory table is held entirely in memory for the duration of the database session. When the database is closed, the data for the memory table is persisted to disk.</div> <div class="Body"><span class="Syntax"><a name="157709">DISK</a></span> creates the new table on disk. A disk table caches a portion of its data in memory and the remaining data on disk.</div> <div class="Body"><span class="Syntax"><a name="157717">TEMPORARY</a></span> and <span class="Syntax">TEMP</span> are equivalent and create the new table as a global temporary table. The <span class="Syntax">GLOBAL</span> qualifier is optional. The definition of a global temporary table is visible to all connections. The data written to a global temporary table is visible only to the connection used to write the data.</div> <div class="Body"><a name="256756">NOTE: If </a><span class="Syntax">MEMORY</span>, <span class="Syntax">DISK</span>, or <span class="Syntax">TEMPORARY</span>/<span class="Syntax">TEMP</span> is not specified, the new table is created in memory.</div> <div class="Body"><span class="EquationVariables"><a name="157692">table_name</a></span> specifies the name of the new table.</div> <div class="Body"><span class="EquationVariables"><a name="147337">column_definition</a></span> specifies the definition of a column in the new table. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.11.html#157932', '');">â??Column Definition for Local Tablesâ??</a></span> for a complete explanation. </div> <div class="Body"><span class="EquationVariables"><a name="157915">constraint_definition</a></span> specifies constraints on the columns of the new table. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.11.html#147380', '');">â??Constraint Definition for Local Tablesâ??</a></span> for a complete explanation. </div> <div class="Body"><span class="Syntax"><a name="157954">ON COMMIT PRESERVE ROWS</a></span> preserves row values in a temporary table while the connection is open; this is the default action. <span class="Syntax">ON COMMIT DELETE ROWS</span> empties row values on each commit or rollback.</div> <div class="gutter_text_right"><a name="221698"> </a></div> <div class="head_3"><a name="157932">Column Definition for Local Tables</a></div> <div class="gutter_text_right"><a name="147347">Grammar</a></div> <div class="syntax_first"><span class="EquationVariables"><a name="197415">column_name</a></span> <span class="EquationVariables">Datatype</span> [(<span class="EquationVariables">precision</span>[,<span class="EquationVariables">scale</span>])] </div> <div class="syntax"><a name="197416">[{DEFAULT </a><span class="EquationVariables">default_value</span> | GENERATED BY DEFAULT AS IDENTITY</div> <div class="syntax"><a name="197417">(START WITH </a><span class="EquationVariables">n</span>[, INCREMENT BY <span class="EquationVariables">m</span>])}] | [[NOT] NULL]</div> <div class="syntax"><a name="197418">[IDENTITY] [PRIMARY KEY]</a></div> <div class="Body"><a name="197419">where:</a></div> <div class="Body"><span class="EquationVariables"><a name="264255">column_name</a></span> is the name to be assigned to the column.</div> <div class="Body"><span class="EquationVariables"><a name="264259">Datatype</a></span> is the data type of the column to be created. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'salesforce.27.06.html#932173', '');">â??Data Typesâ??</a></span> in the <span class="Emphasis">DataDirect </span><span class="Emphasis">Connect Series for ODBC Userâ??s Guide</span> for a list of supported Salesforce data types. You cannot specify ANYTYPE, BINARY, COMBOBOX, or TIME data types in the column definition of Create Table statements.</div> <div class="Body"><span class="EquationVariables"><a name="197421">precision</a></span> is the number characters for CHAR and VARCHAR columns, the number of bytes for BINARY and VARBINARY columns, and the total number of digits for DECIMAL columns.</div> <div class="Body"><span class="EquationVariables"><a name="256809">scale</a></span> is the number of digits to the right of the decimal point for DECIMAL columns and the number of seconds for DATETIME columns.</div> <div class="Body"><span class="EquationVariables"><a name="241885">default_value</a></span> is the default value to be assigned to the column. The following default values are allowed in column definitions for local tables:</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="241886">For character columns, a single-quoted string or NULL. The only SQL function that can </a>be used is CURRENT_USER.</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="241887">For datetime columns, a single-quoted Date, Time, or Timestamp value or NULL. You </a>can also use the following datetime SQL functions: CURRENT_DATE, CURRENT_TIME, CURRENT_ TIMESTAMP, TODAY, or NOW.</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="241888">For boolean columns, the literals FALSE, TRUE, NULL.</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="241889">For numeric columns, any valid number or NULL.</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="241890">For binary columns, any valid hexadecimal string or NULL.</a></div> </td> </tr> </table> </div> <div class="Body"><a name="241891">The </a><span class="Syntax">IDENTITY</span> and <span class="Syntax">GENERATED BY DEFAULT AS IDENTITY</span> clauses define an auto-increment column. Either clause can be specified only on INTEGER or BIGINT columns. Identity columns are considered primary key columns, so a table can have only one Identity column. </div> <div class="Body"><a name="256845">The </a><span class="Syntax">GENERATED BY DEFAULT AS IDENTITY</span> clause is the standard SQL syntax for specifying an Identity column.</div> <div class="Body"><a name="197425">The </a><span class="Syntax">START WITH</span> and <span class="Syntax">INCREMENT BY</span> clauses specify the sequence of numbers generated for the Identity column. <span class="EquationVariables">n</span> and <span class="EquationVariables">m</span> are the starting and incrementing values, respectively, for an Identity column. The default start value is 0 and the default increment value is 1.</div> <div class="Body"><a name="197426">The </a><span class="Syntax">IDENTITY</span> operator is equivalent to <span class="Syntax">GENERATED BY DEFAULT AS IDENTITY</span> without the optional <span class="Syntax">START WITH</span> clause.</div> <div class="gutter_text_right"><a name="249441">Examples</a></div> <div class="hanging_label_head"><a name="249442">Example A</a></div> <div class="Body"><a name="249443">Assuming the current schema is PUBLIC, a local table is created. </a><span class="Syntax">id</span> is an identity column with a starting value of 0 and an increment value of 1 because no Start With and Increment By clauses are specified.</div> <div class="syntax_first"><a name="249444">CREATE TABLE Test (id INTEGER GENERATED BY DEFAULT AS</a></div> <div class="syntax"><a name="249445">IDENTITY, name VARCHAR(30))</a></div> <div class="Body"><a name="249446">This example is equivalent to the previous example.</a></div> <div class="syntax_first"><a name="249447">CREATE TABLE Test (id INTEGER IDENTITY, name VARCHAR(30))</a></div> <div class="hanging_label_head"><a name="249448">Example B</a></div> <div class="Body"><a name="249449">Assuming the current schema is PUBLIC, a local table is created. </a><span class="Syntax">id</span> is an identity column with a starting value of 2 and an increment of 2.</div> <div class="syntax_first"><a name="249450">CREATE TABLE Test (id INTEGER GENERATED BY DEFAULT AS</a></div> <div class="syntax"><a name="249451">IDENTITY (START WITH 2, INCREMENT BY 2), name VARCHAR(30))</a></div> <div class="head_3"><a name="147380">Constraint Definition for Local Tables</a></div> <div class="gutter_text_right"><a name="147381">Grammar</a></div> <div class="syntax_first"><a name="147382">[CONSTRAINT [</a><span class="EquationVariables">constraint_name</span>]</div> <div class="syntax"><a name="147383"> {</a><span class="EquationVariables">unique_constraint</span> |</div> <div class="syntax"><a name="147384"> </a><span class="EquationVariables">primary_key_constraint</span> |</div> <div class="syntax"><a name="147385"> </a><span class="EquationVariables">foreign_key_constraint</span><span class="Syntax">}</span>]</div> <div class="Body"><a name="147387">NOTE: You must specify at least one constraint.</a></div> <div class="Body"><a name="158250">where:</a></div> <div class="Body"><span class="EquationVariables"><a name="147388">constraint_name</a></span> specifies a name for the constraint.</div> <div class="Body"><span class="EquationVariables"><a name="147389">unique_constraint</a></span> specifies a constraint on a single column in the table. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.11.html#147625', '');">â??Unique Clauseâ??</a></span> for syntax. </div> <div class="Body"><a name="262839">Values in the constrained column cannot be repeated, except in the case of null values. For </a>example:</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" style="color: #000000; font-style: normal; font-variant: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="177415">ColA</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="syntax" style="text-align: Center;"><a name="177417">1</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="syntax" style="text-align: Center;"><a name="177419">2</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="syntax" style="text-align: Center;"><a name="177421">NULL</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="syntax" style="text-align: Center;"><a name="177423">4</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="syntax" style="text-align: Center;"><a name="177425">5</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="syntax" style="text-align: Center;"><a name="177427">NULL</a></div> </td> </tr> </table> <div class="Body"><a name="177429">A single table can have multiple columns with unique constraints.</a></div> <div class="Body"><span class="EquationVariables"><a name="147410">primary_key_constraint</a></span> specifies a constraint on one or more columns in the table. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.11.html#147631', '');">â??Primary Key Clauseâ??</a></span> for syntax.</div> <div class="Body"><a name="261746">Values in a single column primary key column must be unique. Values across multiple </a>constrained columns cannot be repeated, but values within a column can be repeated. Null values are not allowed. For example:</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" style="color: #000000; font-style: normal; font-variant: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="147413">Col A</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads" style="color: #000000; font-style: normal; font-variant: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="147415">Col B</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="syntax" style="text-align: Center;"><a name="147417">2</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147419">1</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="syntax" style="text-align: Center;"><a name="147421">3</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147423">1</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="syntax" style="text-align: Center;"><a name="147425">4</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147427">2</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="syntax" style="text-align: Center;"><a name="147429">5</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147431">2</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="syntax" style="text-align: Center;"><a name="147433">6</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147435">2</a></div> </td> </tr> </table> <div class="Body"><a name="147437">Only one primary key constraint is allowed in the table. </a></div> <div class="Body"><span class="EquationVariables"><a name="147441">foreign_key_constraint</a></span> defines a link between related tables. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'sqlsupport.12.11.html#147637', '');">â??Foreign Key Clauseâ??</a></span> for syntax.</div> <div class="Body"><a name="262604">A column defined as a foreign key in one table references a primary key in the related table. </a>Only values that are valid in the primary key are valid in the foreign key. The following example is valid because the foreign key values of the dept id column in the EMP table match those of the id column in the referenced table DEPT:</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;" colspan="2"> <div class="table_column_heads" style="text-align: Center;"><a name="147444">Referenced Table</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" style="text-align: Center;"><a name="147448"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;" colspan="3"> <div class="table_column_heads" style="text-align: Center;"><a name="147450">Main Table</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;" colspan="2"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="147456">DEPT</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="text-align: Center;"><a name="147460"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;" colspan="3"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="147462">EMP</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" style="text-align: Center;"><a name="147468"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="text-align: Center;"><a name="147470"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="text-align: Center;"><a name="147472"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="text-align: Center;"><a name="147474"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="text-align: Center;"><a name="147476"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="147478">(Foreign Key)</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" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="147480">id</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="147482">name</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><a name="147484"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="147486">id</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="147488">name</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="147490">dept id</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="syntax" style="text-align: Center;"><a name="147492">1</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147494">Dev</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147496"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147498">1</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147500">Mark</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147502">1</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="syntax" style="text-align: Center;"><a name="147504">2</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147506">Finance</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147508"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147510">1</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147512">Jim</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147514">3</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="syntax" style="text-align: Center;"><a name="147516">3</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147518">Sales</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147520"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147522">1</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147524">Mike</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147526">2</a></div> </td> </tr> </table> <div class="Body"><a name="147528">The following example, however, is not valid. The value 4 in the dept id column does not </a>match any value in the referenced id column of the DEPT table.</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;" colspan="2"> <div class="table_column_heads" style="text-align: Center;"><a name="147531">Referenced Table</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" style="text-align: Center;"><a name="147535"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;" colspan="3"> <div class="table_column_heads" style="text-align: Center;"><a name="147537">Main Table</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;" colspan="2"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="147543">DEPT</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="text-align: Center;"><a name="147547"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;" colspan="3"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="147549">EMP</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" style="text-align: Center;"><a name="147555"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="text-align: Center;"><a name="147557"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="text-align: Center;"><a name="147559"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="text-align: Center;"><a name="147561"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="text-align: Center;"><a name="147563"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="147565">(Foreign Key)</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" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="147567">id</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="147569">name</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><a name="147571"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="147573">id</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="147575">name</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text" style="color: #000000; font-style: normal; font-variant: normal; font-weight: normal; text-align: Center; text-transform: none; vertical-align: baseline;"><span class="Syntax"><a name="147577">dept id</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="syntax" style="text-align: Center;"><a name="147579">1</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147581">Dev</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147583"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147585">1</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147587">Mark</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147589">1</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="syntax" style="text-align: Center;"><a name="147591">2</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147593">Finance</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147595"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147597">1</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147599">Jim</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147601">3</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="syntax" style="text-align: Center;"><a name="147603">3</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147605">Sales</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147607"> </a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147609">1</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147611">Mike</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="syntax" style="text-align: Center;"><a name="147613">4</a></div> </td> </tr> </table> <div class="head_4"><a name="147625">Unique Clause</a></div> <div class="gutter_text_right"><a name="148811">Grammar</a></div> <div class="syntax_first"><a name="147626">UNIQUE (</a><span class="EquationVariables">column_name</span> [,<span class="EquationVariables">column_name</span>...])</div> <div class="Body"><a name="147627">where </a><span class="EquationVariables">column_name</span> specifies the column to which the constraint is applied. Multiple columns names must be separated by commas.</div> <div class="head_4"><a name="147631">Primary Key Clause</a></div> <div class="gutter_text_right"><a name="148820">Grammar</a></div> <div class="syntax_first"><a name="147632">PRIMARY KEY (</a><span class="EquationVariables">column_name</span> [,<span class="EquationVariables">column_name</span>...])</div> <div class="Body"><a name="147633">where </a><span class="EquationVariables">column_name</span> specifies the primary key column to which the constraint is applied. Multiple column names must be separated by commas.</div> <div class="head_4"><a name="147637">Foreign Key Clause</a></div> <div class="gutter_text_right"><a name="148836">Grammar</a></div> <div class="syntax_first"><a name="147638">FOREIGN KEY (</a><span class="EquationVariables">fcolumn_name</span> [,<span class="EquationVariables">fcolumn_name</span>...]) </div> <div class="syntax"><a name="147639"> REFERENCES </a><span class="EquationVariables">ref_table</span> (<span class="EquationVariables">pcolumn_name</span> [,<span class="EquationVariables">pcolumn_name</span>...])</div> <div class="syntax"><a name="147640"> [ON {DELETE | UPDATE}</a></div> <div class="syntax"><a name="147641"> {CASCADE | SET DEFAULT | SET NULL}]</a></div> <div class="Body"><a name="147642">where:</a></div> <div class="Body"><span class="EquationVariables"><a name="147643">fcolumn_name</a></span> specifies the foreign key column to which the constraint is applied. Multiple column names must be separated by commas.</div> <div class="Body"><span class="EquationVariables"><a name="147644">ref_table</a></span> specifies the table to which a foreign key refers.</div> <div class="Body"><span class="EquationVariables"><a name="147645">pcolumn_name</a></span> specifies the primary key column or columns referenced in the referenced table. Multiple column names must be separated by commas.</div> <div class="Body"><span class="Syntax"><a name="147646">ON DELETE</a></span> is a clause that defines the operation performed when a row in the table referenced by a foreign key constraint is deleted. One of the following operators must be specified in the On Delete clause:</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"><span class="Syntax"><a name="147647">CASCADE</a></span> specifies that all rows in the foreign key table that reference the deleted row in the primary key table are also deleted.</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"><span class="Syntax"><a name="147648">SET DEFAULT</a></span> specifies that the value of the foreign key column is set to the column default value for all rows in the foreign key table that reference the deleted row in the primary key table.</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"><span class="Syntax"><a name="147649">SET NULL</a></span> specifies that the value of the foreign key column is set to NULL for all rows in the foreign key table that reference the deleted row in the primary key table.</div> </td> </tr> </table> </div> <div class="Body"><span class="Syntax"><a name="147650">ON UPDATE</a></span> is a clause that defines the operation performed when the primary key of a row in the table referenced by a foreign key constraint is updated. One of the following operators must be specified in the On Update clause:</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"><span class="Syntax"><a name="147651">CASCADE</a></span> specifies that the value of the foreign key column for all rows in the foreign key table that reference the row in the primary key table that had the primary key updated are updated with the new primary key 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"><span class="Syntax"><a name="147652">SET DEFAULT</a></span> specifies that the value of the foreign key column is set to the column default value for all rows in the foreign key table that reference the row that had the primary key updated in the primary key table.</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"><span class="Syntax"><a name="147653">SET NULL</a></span> specifies that the value of the foreign key column is set to NULL for all rows in the foreign key table that reference the row that had the primary key updated in the primary key table.</div> </td> </tr> </table> </div> <div class="Body"><a name="147654">Both the On Delete and On Update clauses can be used in a single foreign key definition.</a></div> <div class="gutter_text_right"><a name="249496">Example</a></div> <div class="hanging_label_head"><a name="249497">Example A</a></div> <div class="Body"><a name="249498">Assuming the current schema is PUBLIC, the </a><span class="Syntax">emp</span> table is created with the <span class="Syntax">name</span>, <span class="Syntax">empId</span>, and <span class="Syntax">deptId</span> columns. The table contains a foreign key constraint on the <span class="Syntax">deptId</span> column that references the <span class="Syntax">id</span> column in the <span class="Syntax">dept</span> table. In addition, it sets the value of any rows in the <span class="Syntax">deptId</span> column to NULL that point to a deleted row in the referenced <span class="Syntax">dept</span> table.</div> <div class="syntax_first"><a name="249502">CREATE TABLE emp (name VARCHAR(30), empId INTEGER, deptId INTEGER, FOREIGN </a>KEY(deptId) REFERENCES dept(id)) ON DELETE SET NULL)</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