Search code examples
sqlteradatavolatilecreate-table

Creating a volatile table with an automatically incremented field and referential integrity constraints


As a junior SQA, I don't have write/read access to tables in Teradata - only read access for views. I recently discovered volatile tables, and they have made my life so much easier. While they are extremely helpful, I'm wondering if it's possible to create a volatile table with an auto incremented primary key (or index), and also referential integrity constraints?

Here is the SQL I've been to working with.

CREATE VOLATILE TABLE Article (
    user_id INTEGER GENERATED BY DEFAULT AS IDENTITY
    (
        START WITH 1
        INCREMENT BY 20
        MAXVALUE 2147483647
    )
    ,user_url varchar(1000) NOT NULL
    ,user_title varchar(200)
    --,PRIMARY KEY (art_id)
) ON COMMIT PRESERVE ROWS;

The error message I'm getting with this code:

CREATE TABLE Failed. 5784: Illegal usage of Identity Column user_id

As you can see, I'm trying to create an auto incremented field, but haven't yet tried implementing any referential integrity constraints.

So to sum it up, my questions would be:

1. Can a field of a volatile table be automatically incremented and, if
   possible, how is this accomplished?
2. Can referential integrity constraints be included in creating a volatile 
   table and, if possible, how is this accomplished?

If anyone could shed some light on my questions, I would really appreciate it. Thanks.


Solution

  • As per this: http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/General_Reference/B035_1096_111A/Database.26.1905.html

    Illegal usage of Identity Column user_id: An identity column is defined in a temporary or volatile table. It may only be defined in a permanent table.

    However, there are other ways to generate surrogate keys in a volatile table of Teradata, but only when inserting data in your table, and not when it is created: http://forums.teradata.com/forum/database/generate-surrogatekey-with-a-huge-table

    In general:

    1. You can have 1000 volatile tables in an active session.
    2. CHECK and REFERENTIAL constraints are not allowed.
    3. DEFAULT clause not allowed.