Set maximum number of rows on a table?

[shorti]

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.

[Mark A]

On Sep 11, 5:09 pm, shorti <[e-mail]:

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.

How about creating an insert trigger?

[David Portas]

"shorti" <lb******@juno.comwrote in message
news:11**********************@e34g2000pro.googlegr oups.com...

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.

Something like this perhaps:

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

--
David Portas

[shorti]

On Sep 11, 2:54 pm, Mark A <[e-mail]:

On Sep 11, 5:09 pm, shorti <[e-mail]:

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.

How about creating an insert trigger?

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.

[--CELKO--]

On Sep 11, 4:09 pm, shorti <[e-mail]:

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.

CREATE TABLE Foobar
(size_control INTEGER NOT NULL
CHECK (size_control BETWEEN 1 AND 1000) UNIQUE,
etc.);

[Serge Rielau]

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

[Tonkuma]

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.

[shorti]

On Sep 13, 5:35 am, Serge Rielau <[e-mail]:

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

Yes...that is almost the exact trigger I created for this. Thanks.

[--CELKO--]

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

[Tonkuma]

SELECT MIN(size_control +1)

FROM Foobar
WHERE size_control +1
NOT IN (SELECT size_control FROM Foobar
UNION ALL VALUES (0)); -- handles 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);