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.
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: