Do we have any way to limit number of rows on teradata table?

Database
Teradata Employee

Do we have any way to limit number of rows on teradata table?

Hi Forum,

 

Do we have any way to limit number of rows on teradata table?

Tried someting like below, no luck.

create table systemfe.kk1 (a int check (count(a)=1));

I heard, trigger would help with having a check for before insert with count of rows.

Any inputs?

 

Best Regards,

Sandeep.

 

GANGA SANDEEP KUMAR

3 REPLIES 3
Junior Contributor

Re: Do we have any way to limit number of rows on teradata table?

What exactly are you trying to do?

 

Restricting to a single row is simple, use a View instead:

replace view systemfe.kk1 as select 999 as a;

Instead of updating the table replace the view.

Tourist

Re: Do we have any way to limit number of rows on teradata table?

-- Assume you want a table with zero or one row
-- create a column with a default value and use it as the unique primary index
create table only_one_row
(a integer
,force_only_one_row integer not null default 1)
unique primary index(force_only_one_row);

-- create a view that projects only the other columns
replace view only_one_row_view as
select a from only_one_row;

-- perform your updates via that view (so you cannot override the default value)
insert into only_one_row_view values(100);
-- the second insert will fail due to the "duplicate unique primary key error"
insert into only_one_row_view values(200);
 *** Failure 2801 Duplicate unique prime key error in xyz.only_one_row.
Teradata Employee

Re: Do we have any way to limit number of rows on teradata table?

The answer is Yes, it is possible in general to limit the number of rows in a table.

 

You can specify an INTEGER NOT NULL column and apply a check constraint to the column to restrict the column values to a particular range, and also ensure that the column values are unique either with a unique constraint or a unique index.

 

For example, to limit a table to contain 20 rows at most:

 

create table mytab (c1 integer not null check(c1 between 1 and 20)) unique primary index (c1)