KNOWLEDGE BASE
Log In    |    Knowledge Base    |    4D Home
Tech Tip: ALTER TABLE takes only one SQL Clause
PRODUCT: 4D | VERSION: 11 | PLATFORM: Mac & Win
Published On: May 11, 2010
Your current browser may not allow you to download the asset. We recommend Mozilla Firefox or Google Chrome.
Log In

The SQL command ALTER TABLE documentation from 4D lists many clauses, as shown here:

ALTER TABLE sql_name
    {ADD column_definition |
    DROP sql_name |
    ADD primary_key_definition |
    DROP PRIMARY KEY |
    ADD foreign_key_definition |
    DROP CONSTRAINT sql_name |
    SET SCHEMA sql_name}


What is important to note in this definition is the "|" character. The definition could be restated as:

ALTER TABLE sql_name
    {ADD column_definition            OR
    DROP sql_name                     OR
    ADD primary_key_definition        OR
    DROP PRIMARY KEY                  OR
    ADD foreign_key_definition        OR
    DROP CONSTRAINT sql_name          OR
    [(ENABLE| DISABLE) STAMP]         OR
    [(ENABLE| DISABLE) REPLICATE]     OR
    SET SCHEMA sql_name}


Any time you need to alter a table you can call ALTER TABLE as many times as necessary within a Begin SQL / End SQL but with only one clause at a time. In the above example the "OR" operators show that only one of those alterations (add or drop a column, add or drop a primary key, etc) can be done per call to ALTER TABLE.

In the following example there are two tables that need to be altered to create a many-to-one releationship. Though it could all be done within one Begin SQL / End SQL statement, it has been broken into two for clarity. Order is important because the PRIMARY KEY must exist before it can be referenced in a FOREIGN KEY definition.

Also note that the example shows two ways to designate a PRIMARY KEY. In the first example it is not given a name, where in the second it is. Having a constraint name is necessary if the constraint is going to be addressed in a later use of ALTER TABLE, where a name will be necessary for specific addressing.

Begin SQL
    ALTER TABLE MyOneTable
    ADD myOneID INT32 NOT NULL UNIQUE AUTO_INCREMENT;

    ALTER TABLE MyOneTable
    ADD PRIMARY KEY (myOneID);
End SQL

Begin SQL
    ALTER TABLE MyManyTable
    ADD myManyID INT32 NOT NULL UNIQUE AUTO_INCREMENT;

    ALTER TABLE MyManyTable
    ADD CONSTRAINT pk_MyManyTable PRIMARY KEY (myManyID);

    ALTER TABLE MyManyTable
    ADD myOneID INT32 NOT NULL;

    ALTER TABLE MyManyTable
    ADD CONSTRAINT fk_MyManyTbl_to_MyOneTbl FOREIGN KEY(myOneID) REFERENCES
       MyOneTable (myOneID) ON DELETE CASCADE;
End SQL


Note: The last line of SQL code in this example is broken across two lines.