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