How to restrict the no. of rows in a table during its creation?

I have to make a table that can have a maximum of 1000 rows. How do I implement this?

What other constraints should I add in the create table开发者_JAVA百科 query?

This is for MySql

table_option:
{ENGINE|TYPE} [=] engine_name
| AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| CONNECTION [=] 'connect_string'
| DATA DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| INDEX DIRECTORY [=] 'absolute path to directory'
| INSERT_METHOD [=] { NO | FIRST | LAST }
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
| UNION [=] (tbl_name[,tbl_name]...)

As you can see, you have the MAX_ROWS option; just set it to 1000 and things should work fine.

Also check out the manual: http://dev.mysql.com/doc/refman/5.0/en/create-table.html

Depending on your DBMS, you might not be able to implement this requirement as a constraint

One thing you could do is add a trigger that removes the >1000 records in case of insert. If your DBMS supports it, a 'before' trigger could allow you to prevent the insertion if the total number of rows after insertion would exceed 1000 records

You could create AFTER INSERT Trigger that checks row numbers in your table. Something like

 CREATE TRIGGER MAXROWSCHECKER ON MyTable
    AFTER INSERT
 AS
    BEGIN
        DECLARE @rows INT ;
        SELECT  @rows = COUNT(*)
        FROM    MyTable ;
        IF ( @rows > 1000 ) 
            RAISERROR ('Too many rows in TABLE myTable', 16, 1) ;
        ROLLBACK TRANSACTION
        RETURN 
    END