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:
CREATE TABLE client (
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:
INSERT INTO client (identificator, first_name) VALUES ('1', 'Loren');And second client by the manually command:
INSERT INTO client (identificator, first_name) VALUES ('2', 'Sara');Let's see what we get in the result of that manipulation:
SELECT * FROM client; 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:
ALTER TABLE client DROP PRIMARY KEY;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:
ALTER TABLE client ADD COLUMN email VARCHAR (31) NOT NULL UNIQUE;Check table format using early described command:
DESC client;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:
ALTER TABLE client ADD COLUMN email_reserv VARCHAR (31) NOT NULL UNIQUE;And show format into the table:
DESC client;
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:
INSERT INTO client (id, email, email_reserv) VALUES ('1', 'email@mail.com', 'reserv_email@mail.com');And the second duplicate row:
INSERT INTO client (id, email, email_reserv) VALUES ('2', 'email@mail.com', 'reserv_email@mail.com');
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:
ALTER TABLE client ADD COLUMN age INT CHECK (age BETWEEN 18 AND 65) DEFAULT 18;DESC client;
For check applied filtering rules we can enter data and we will see the result:
INSERT INTO client (id, email, email_reserv) VALUES ('4', 'mail.com', 'mail.gg'); DESC client; And show entering data by the command:
SELECT * FROM client
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:
INSERT INTO client (id, email, email_reserv) VALUES ('4', 'mail.com', 'mail.gg', '5')
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.
