Add Constraint SQL Language

This language, used to retrieve and manage databases in RDMS, is a place to store relational data that has multiple rows and columns. SQL allows you to retrieve and process tables in RDMS.

In SQL, you can create procedural programs with iterations and conditions. You can access the database by placing the commands in the same way as in PHP, or using visual software such as phpMyAdmin running on the server or locally using XAMPP, as well as many other local server programs. Almost all modern database management systems are based on this language.

SQL Historical Background

The acronym SQL stands for "Structured Search Language", which is understood by almost every DBMS. It was developed by IBM in the seventies with the initial name SEQUEL, and its real name dates from the early eighties. SQL has been standardized by the ISO International Organization. Here are the basic steps in this process:

• ANSI standard in 1986;
• ISO (SQL1) in 1987, revised in 1989;
• ISO (SQL2) in 1992;
• SQL3, created in 1999 by ANSI and ISO.

The 2006 ISO SQL standard is used to manage XML files, such as importing XML data into a database or exporting content to XML and constraint SQL.

Despite the standardization of ISO, the actual use of SQL by various DBMS editors has differences regarding:

• syntax details;
• writing orders;
• exact work of governing bodies;
• introduction of new data types (images, animations, videos, hypertext links, etc.).

Regardless of the MySQL (or MariaDB) software used, PostgreSQL, Oracle, NoSQL, like Cassandra - the same syntax with slight differences. In short, there is only one SQL language, but each DBMS editor implements its own dialect. A “dictionary” that allows you to switch from one dialect to another is called ODBC (Open Data Base Connectivity). It was created by Microsoft in 1993 for constraint SQL.

Programming language features

SQL is the most popular computer language associated with database management systems. The most promising areas are structured queries. The subject of several international standardizations, the first of which relates to 1986 and is still developing.

The implementation corresponds to relational algebra, but often deviates from traditional calculations. In implementations, differences between upper and lower case are usually ignored, with the exception of strings. Ms SQL constraint allows you to:

• Perform relational algebra operations
• edit instances;
• edit relationships in the database.

Expression of Integrity Constraints

This is a sentence that limits the table modification performed by user queries, so that the data entered matches the expected results. They are imposed when creating:

1. Constraint SQL.
2. DEFAULT.
3. NOT NULL.
4. SINGLE.
5. CHECK.

SQL defines the default value when the database field is not reported using the DEFAULT clause. This simplifies table generation and ensures that the field is nonempty. In the DEFAULT clause, a value must be assigned.

It can be one of the following types:

• digital constant;
• alphanumeric constant (string);
• USER keyword (username);
• NULL keyword
• keyword CURRENT_DATE (input date);
• keyword CURRENT_TIME (input time);
• keyword CURRENT_TIMESTAMP (input date and time).

Database Data Types

In the syntax, for example, type_field1, field_type2, etc., indicate the data type that the corresponding field will contain. Basically, this rule is used to send them to the system so that it allocates the appropriate memory space, depending on the size of the data. The system will not allocate the same space for a string as for an integer. On phpMyAdmin, you can see a list of accepted types in the drop-down list when creating the table.

Here is an example: CREATE TABLE table_name (column1 Type_champ1, column2 Type_champ2, colonne3 Type_champ3 ...).

SQL constraint attributes

To be able to create relationships, you need to know integrity constraints. The following restrictions apply to an attribute:

• default val— val is the default value for this attribute;
• not null - empty or unknown (zero) value is prohibited for this parameter;
• null - an empty or unknown (zero) value is allowed for this attribute (default behavior);
• unique - all values ​​of this attribute must be unique;
• check (te) - the te test must be true for this parameter;
• pimary key - this attribute is the primary key (this SQL unique constraint may appear once) and implies not null unique;
• references ta [(at)] - this parameter has the existing value of the at attribute, which is required unique, of the table ta, if at is not specified, this is the primary key from ta;
• references ta [(at)] on delete cascade - identical to the previous restriction, in addition, if ta is deleted, then the instance that refers to it in this table is automatically deleted as well.

The following add constraint SQL restrictions apply to one or more attributes or to a table:

• unique (at1, ..., atN) - all values ​​in this set of attributes must be unique;
• check (te) - The te test must be true for this set of parameters.

Relationship building

In SQL, a relation is also called a table. Creating a relationship is expressed in SQL through the create table clause.

The created table is empty: it contains attributes (columns) defined during its creation, but not an occurrence (row). It is possible and appropriate to specify integrity constraints for attributes.

And you can also call a restriction to more easily identify it. Otherwise, it gets the name automatically provided by the DBMS.

Table creation

The NOT NULL keyword allows you to indicate that you must enter a field, that is, the DBMS will refuse to insert rows that do not specify a field that includes the NOT NULL clause. You can check a field with CHECK (), which has a logical condition for a value between parentheses. If this value is other than NULL, the DBMS will perform a check using a logical condition with SELECT statements.

The UNIQUE clause allows you to check the fields, and guarantees the values ​​of the columns are different. The CONSTRAINT keyword assigns a name to the constraint so that it appears when the specified clause is not validated. If the CONSTRAINT clause is not specified, the name will be provided arbitrarily by the DBMS. However, it is unlikely that it was understandable, and it is unlikely that it can be recognized at all when there is an integrity error.

SQL data example alter table constraint

To illustrate the following orders, consider the “product” table:

The add constraint SQL statement can join several conditions in a query. Keeping the same table as before, in order to filter only computer products that are almost not in stock (less than 20 items), they run the following query:

SELECT * FROM produitWHERE categorie = 'informatique' AND stock <20

This query returns the following results:

To filter data and have only information about the products “computer” or “keyboard”, you must perform the following search:

SELECT * FROM produitWHERE nom = 'ordinateur' OR nom = 'clavier'

This simple query returns the following results:

Keep in mind that operators can be combined to perform powerful searches. You can filter “computer” products with a stock of less than 20, and “deliver” products with a stock of less than 200 with the following SQL alter table add constraint search:

SELECT * FROM produitWHERE (categorie = 'informatique' AND stock <20) OR (categorie = 'fourniture' AND stock <200)

This returns the following 3 results:

Key Definition

Thanks to SQL constraint foreign key, you can define keys, that is, specify columns whose knowledge allows you to assign exactly one of them and one row. The set of columns that are part of the current table is called the primary key and is determined by the PRIMARY KEY clause, followed by a list of columns separated by commas, in brackets.

They no longer accept NULL and must be such that two rows cannot simultaneously have the same combination of values ​​for these columns. PRIMARY KEY (colonne1, colonne2, ...).

When the column list of the executable table allows you to determine the primary key, it uses the SQL add constraint foreign key clause, followed by a comma-separated list of columns in the current table in parentheses. Then in brackets follows the REFERENCES clause, followed by the name of the external table and a list of the corresponding columns, separated by commas.

FOREIGN KEY (colonne1, colonne2, ...) REFERENCES Nom_de_la_table_etrangere (colonne1, colonne2, ...)

Primary keys

The interests of the DBMS beyond storage are to organize this storage and to allow certain rules to be established on it. Thus, for each of the tables, a primary key is usually defined. It will uniquely identify the search by string when presented. Therefore, when declaring a column as a key, the DBMS automatically creates an index with a restriction. It can be performed:

The name of the constraint is controlled by the DBMS when you need to name this primary key. A PC can also be created a second time through an alter order in the table. This primary key will be the absolute identifier for the application. A DBMS can do without it thanks to rowid, so the need for a primary key does not technically exist, but it does not make much sense to the application. This primary key also in many cases sets integrity constraints, that is, the management of certain data in one table compared to other indicators belonging to other tables.

For example, an invoice without a single order is prohibited in the customer management database. Create a foreign key in the table of accounts, which will be limited by the presence of the key.

Foreign key constraint

For a PC, a foreign key (FK) restriction is determined by creating a table, thanks to the key configuration mechanism and the associated indexes that will be created for FK, so connection requests are easier to connect. In addition to the primary or foreign key restrictions, there are other restrictions:

For example, a uniqueness constraint other than that corresponding to the primary key. You need to create a unique index:

create unique index num_sécu on personne.

Another limitation is checking validation, which usually allows you to specify a range or format for the value. A positive value for consumption is an indicator of politeness choice (equivalent to ENUM for some other DBMSs) and is stored in upper case:

Thus, the DBMS allows you to create integrity constraints of all kinds, however, as soon as they are applied, it is not possible to manage the concept actively / inactive. An inactive restriction does not affect the operation of the database (insert, delete, ...). However, the time taken to reactivate the voltage can be lengthy depending on the procedures that must be performed to verify this SQL alter constraint. But it is usually interesting to disable restrictions.

The restriction is disabled as follows:

ALTER TABLE ma_table DISABLE CONSTRAINT ma_constraint;

And activated:

ALTER TABLE ma_table ENABLE CONSTRAINT ma_constraint;

Of course, it should be noted that any (activated) restriction applied to the database requires additional processing during insertions, modifications, or deletions. These processes, as a rule, slow down the use of the database, so they should be used sparingly.

The ALTER TBLE command changes the structure of the table. If used by the current request, the ALTER command waits for it to complete.

Usage of command: Editing column value. The default values ​​apply to the following INSERT commands, not rows.

Renaming columns or tables without changing the type, in this command you can ignore the column of keywords.

The language does not allow changing restrictions. Instead, they should remove the constraint or create one.

Change the length of the varchar column. If the table refers to a stored procedure, you cannot add or remove a column. You can delete the stored procedure before executing the ALTER TABLE command, and then recreate it after editing the table.

Trigger: referential integrity guarantee

Foreign keys are used to determine the columns of a table, which guarantees the validity of another table. Thus, there are elements called triggers to guarantee all these restrictions, which are denoted by the term referential integrity (s) and make sure that the row used from another table actually exists.

These triggers are in the Delete and Update status:

1. ON DELETE - accompanied by arguments in curly brackets, allowing you to specify the action to be taken if the line is deleted.
2. CASCADE - indicates cascading deletion of rows whose keys correspond to their primary elimination.
3. RESTRICT - indicates an error while deleting the value corresponding to the key.
4. SET NULL - puts NULL on a string if the value is deleted.
5. SET DEFAULT - puts the default value (which follows this parameter) in the line, if the value is deleted.

The “UPDATE” field is followed by the arguments in curly brackets, allowing you to specify the action that is performed when the table row that is part of the key is changed:

1. CASCADE - indicates the cascading modification of the rows of the external table whose keys correspond to the keys of the modified rows.
2. RESTRICT - indicates an error of the corresponding key.
3. SET NULL - puts NULL in a string if the value of the corresponding key changes.
4. SET DEFAULT - places the default value that follows this parameter in the line if the value corresponding to the key is changed.
5. Statements are expressions that must be executed when editing the data so that they can be executed, thus guaranteeing the integrity of the data.

Their syntax is as follows: CREATE ASSERTION Nom_de_la_contrainte CHECK (expression_conditionnelle) A condition that must be met can be made (and usually) using the SELECT clause.