Set maximum number of rows on a table?

Is there a way to limit the number of rows in a table? Lets say I
want to only allow 1000 records to be inserted into a table. Can I
CREATE or ALTER TABLE and set it to only allow a max 1000 record ? I
did not see the option available under either of these.

re: Set maximum number of rows on a table?

On Sep 11, 5:09 pm, shorti <lbrya...@juno.comwrote:How about creating an insert trigger?

re: Set maximum number of rows on a table?

"shorti" <lbryan21@juno.comwrote in message
news:1189544957.737988.128840@e34g2000pro.googlegr oups.com...Something like this perhaps:

CREATE TABLE tbl
(x INT NOT NULL PRIMARY KEY, CHECK (x BETWEEN 1 AND 1000),
.... );

--
David Portas

re: Set maximum number of rows on a table?

On Sep 11, 2:54 pm, Mark A <m00...@yahoo.comwrote:ok..thanks Mark I will do that. I was just hoping I was missing
something that allowed you to set up the table that way.

re: Set maximum number of rows on a table?

On Sep 11, 4:09 pm, shorti <lbrya...@juno.comwrote:CREATE TABLE Foobar
(size_control INTEGER NOT NULL
CHECK (size_control BETWEEN 1 AND 1000) UNIQUE,
etc.);

re: Set maximum number of rows on a table?

Shorti,

You could just use a before trigger with:

CREATE TRIGGER trg1 BEFORE INSERT ON T
REFERENCING NEW_TABLE AS NEWTAB
FRO EACH STATEMENT
WHEN ((SELECT COUNT(*) FROM T) + (SELECT COUNT(*) FROM NEWTAB) 1000)
SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = 'Table too big'

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

re: Set maximum number of rows on a table?

Another idea.

CREATE TABLE LimitRows
(PK INTEGER NOT NULL
,Data1 VARCHAR(5) NOT NULL
)
ORGANIZE BY KEY SEQUENCE (PK STARTING 1 ENDING 10)
DISALLOW OVERFLOW
;

INSERT INTO LimitRows
SELECT PK, 'AAA'
FROM (VALUES 2,3,5,8,9) S(PK)
;

Insert one row.
INSERT INTO LimitRows
SELECT COALESCE(MIN(COALESCE(A.PK, N.PK)), 10) + 1, 'BBB'
FROM LimitRows A
FULL OUTER JOIN
(SELECT NULLIF(COUNT(*),1)
FROM LimitRows
WHERE PK = 1
) N (PK)
ON N.PK = A.PK
WHERE NOT EXISTS
(SELECT *
FROM LimitRows B
WHERE B.PK = A.PK + 1
)
AND COALESCE(A.PK, N.PK) < 10
;

Repeat 4 times by changing Data1 to 'CCC','DDD','EEE','FFF'.
(Filled all 10 rows)

Next Insert gets error message:
SQL1870N A row could not be inserted into a range-clustered table
because a key sequence column is out of range.

Final result is....
----------------------- Commands Entered -----------------------
SELECT * FROM LimitRows;
----------------------------------------------------------------

PK DATA1
----------- -----
1 BBB
2 AAA
3 AAA
4 CCC
5 AAA
6 DDD
7 EEE
8 AAA
9 AAA
10 FFF

10 record(s) selected.

re: Set maximum number of rows on a table?

On Sep 13, 5:35 am, Serge Rielau <srie...@ca.ibm.comwrote:Yes...that is almost the exact trigger I created for this. Thanks.

re: Set maximum number of rows on a table?

>Now I would have to parse [table scan?] the table to find the UNIQUE size_control values to find the available ones. <<

SELECT MIN(size_control +1)
FROM Foobar
WHERE size_control +1
NOT IN (SELECT size_control FROM Foobar
UNION ALL VALUES (0)); -- handles 1

This will get the first open slot. Drop the MIN() and you get the
whole set. I am not usre how well this will perform, but you have a
covering index on size_control and the range is small.

re: Set maximum number of rows on a table?

SELECT MIN(size_control +1)I thought that it is necessary to add "UNION ALL VALUES (0)" for first
"FROM Foobar" and last "UNION ALL VALUES (0)" is not necesary.
Like this .....
SELECT MIN(size_control +1)
FROM (SELECT size_control FROM Foobar
UNION ALL VALUES (0) -- handles 1
) S(size_control)
WHERE size_control +1
NOT IN (SELECT size_control FROM Foobar);