By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,017 Members | 951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,017 IT Pros & Developers. It's quick & easy.

Set maximum number of rows on a table?

P: n/a
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.

Sep 11 '07 #1
Share this Question
Share on Google+
9 Replies


P: n/a
On Sep 11, 5:09 pm, shorti <lbrya...@juno.comwrote:
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?

Sep 11 '07 #2

P: n/a
"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

Sep 11 '07 #3

P: n/a
On Sep 11, 2:54 pm, Mark A <m00...@yahoo.comwrote:
On Sep 11, 5:09 pm, shorti <lbrya...@juno.comwrote:
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.

Sep 12 '07 #4

P: n/a
On Sep 11, 4:09 pm, shorti <lbrya...@juno.comwrote:
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.);

Sep 12 '07 #5

P: n/a
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
Sep 13 '07 #6

P: n/a
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.
Sep 13 '07 #7

P: n/a
On Sep 13, 5:35 am, Serge Rielau <srie...@ca.ibm.comwrote:
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.

Sep 13 '07 #8

P: n/a
>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.

Sep 16 '07 #9

P: n/a
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);
Sep 16 '07 #10

This discussion thread is closed

Replies have been disabled for this discussion.