December 4, 2016

SQL insert query validation constraints to limit number of records


This is sample of query for  SQL insert query validation constraints to limit number of records lets you want to insert only 1000 records in table .

We can restrict number of rows, but need to add a column on which we can add some constraint ... check below example.!

CREATE TABLE TAB_1000_ROWS
AS SELECT ROWNUM COL1 FROM DUAL CONNECT BY ROWNUM <1001;

ALTER TABLE TAB_1000_ROWS
ADD CONSTRAINT CHECK_CONST CHECK (COL1 BETWEEN 1 AND 1000);

ALTER TABLE TAB_1000_ROWS
MODIFY COL1 NUMBER NOT NULL;

ALTER TABLE TAB_1000_ROWS
ADD CONSTRAINT UNIQUE_COL1 UNIQUE (COL1); -- or we can also use sequence with max as 100 and no-cycle!


after insertion of 100 values it wont allow any new values to get inserted! will throw check or unique constraint!