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.
GANGA SANDEEP KUMAR
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.
-- 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.
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)