Introduction
Primary Key
One of the limitations display function for identify values in the table — Primary Key. That represent abstract termin unified list of rules, that help to build relation with different tables by use foreign key. Values should be unique and not NULL. For example, imagine that we have online store and we need to numerate our customers, where may be the same name, date of birth and another personal data. In that case we will use that limitation to identify needed customer:
identificator INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(30) NOT NULL,
PRIMARY KEY (identifier, first_name)
);
We create table client with column identificator and first_name, then below we write PRIMARY KEY parameter and identificate columns for that. Test how that work, our manager or system add data with two clients:
And second client by the manually command:
Let's see what we get in the result of that manipulation:
Now every record in that columns are identifier and unique, if our manager will try to post new data with the same name or id, they will recieve error message:
It is important restriction in the modern system and application, where we need to save personal data, informations about banking payments and other sensitive information, system should work properly! If you want to remove PRIMARY KEY you need to write:
That command delete key from your table!
UNIQUE & NOT NULL
Next restriction, have one distinguish with method described above. UNIQUE guarantee all record in the column will have unique value! Already we have created table and now we need to modify they, by add new parameter:
Check table format using early described command:
First UNIQUE key will transform into PRIMARY KEY, if they hasn't exist already, due to necessary identify each rows . In that case you need add primary as we described above or create another one column with UNIQUE key:
And show format into the table:
As you can see above in the first table key is PRIVATE, but also we add another column and key value was set to UNIQUE. Have a look for our example, manager again try to add the same column email as they write:
And the second duplicate row:
That help to save integrity, avoid system issues and increase performance of database.
In last two methods we haven't talked about another one limitation — NOT NULL. The cell in the database should have determine type or value, but there is situation when we identify values in the process of work system.
Important to now! NULL doesn't represent 0 value, it's literally emptiness. In the comparison you can imagine stopper, which have own properties, but isn't value!
DEFAULT & CHECK
Go ahead and look at the nevertheless values limitations DEFAULT and CHECK. They execute function which also assigned with their name. DEFAULT intend for write early prepared value in the situation when cell in the DB doesn't get data, that help to avoid issues and set default value. CHECK represent list of rules for filtration entering data, if their fit into the rules, they go into, in a different way denied. In our store we can't sell our product to child and teenager, then we need to add filtering rule and by default value will eighteen:
For check applied filtering rules we can enter data and we will see the result:
And show entering data by the command:
In the table appears entity with id four and indicated default age, that help to prevent the same problem as we described above empty field, which may impact to the system. But if we doesn't match to parameters, system can't add row to the table:
If we build information system or program, then we can identify problem by the id or code of error.
Conclusion
Enforcing data limitations within a database management system is a crucial practice for maintaining data quality, system security, and overall system reliability. By implementing these limitations, you can build robust and secure applications that effectively manage and protect data.