Limit the number of records a table can have
I have created a table master-domain
. This table should only have 3 records. How can I limit the mysql database to allow NO MORE than this number of records? Is there a specific sql command to accomplish this?
This is my current SQL:
CREATE TABLE `mydatabase`.`master-domain`
(
`domain` VARCHAR( 50 ) NOT NULL COMMENT 'Domain Name',
PRIMARY KEY ( `domain` )
)
PS. I have godaddy and it includes phpMyAdmin, in addition to MySQL databases.
source to share
You can make the primary key of a table a field of type ENUM
. For example:
CREATE TABLE test (
id enum('1','2') NOT NULL,
domain varchar(50) NOT NULL,
primary key (id));
When you update it, you must explicitly set the id to "," 1 "or" 2 ". * It cannot be null and there can only be one record with each ID. The domain is still stored in the field domain
, so hopefully which no matter the external system queries this database, there will be no problem getting the desired results.
If you want to replicate the current constraint so that domain names are unique, you can also add unique key (domain)
.
* note that an empty string is allowed (and not the same as NULL), because an enum is actually a string type. Thus, you must provide two valid ID values to have three total sums.
Alternatively: what are you trying to achieve / prevent here? Is there some kind of automatic process that can add records to the table? Are you trying to make sure you didn't accidentally do this, or that someone who hijacks your account can't?
If a process that can insert records is running on your user, you can put your three records into the table and then take away the INSERT privileges from yourself. You will still be able to modify existing entries, but you will no longer be able to add them unless you explicitly provide the option.
source to share
You can take a look here in the MAX_ROWS parameter . However, I believe this is commonly used to make the table size larger than the disk size, and I don't think you will get the limit you are looking for using it. Alternatively, you can simply select the top 3 rows.
I would question the rationale behind using a database to only store 3 rows - that seems like a complete waste.
source to share
I think there are no built-in MySQL functions. One solution is to create a trigger.
CREATE TRIGGER your_trigger_name
BEFORE INSERT ON master-domain
FOR EACH ROW
BEGIN
DECLARE cnt INT;
SELECT count(*) INTO cnt FROM master-domain;
IF cnt = 10 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You can store only 3 records.';
END IF;
END;
Try a trigger on your desk. Hope this helps you.
source to share