Zen

Source string Source string

185/10000 · 185
English Actions
Is a NULL in SQL the same thing as a nil in Lua? No, but it is close enough that there will be confusion. When nil means "unknown" or "inapplicable", yes. But when nil means "nonexistent" or "type is nil", no. NULL is a value, it has a data type because it is inside a column which is defined with that data type.
Creating an index
This is how to create indexes for the modules table:
CREATE INDEX size ON modules (size);
CREATE UNIQUE INDEX purpose ON modules (purpose);
There is no need to create an index on the name column, because Tarantool creates an index automatically when it sees a PRIMARY KEY clause in the CREATE TABLE statement. In fact there is no need to create indexes on the size or purpose columns either -- if indexes don't exist, then it is still possible to use the columns for searches. Typically people create non-primary indexes, also called secondary indexes, when it becomes clear that the table will grow large and searches will be frequent, because searching with an index is generally much faster than searching without an index.
Another use for indexes is to enforce uniqueness. When an index is created with CREATE UNIQUE INDEX for the purpose column, it is not possible to have duplicate values in that column.
Data change
Putting data into a table is called "inserting". Changing data is called "updating". Removing data is called "deleting". Together, the three SQL statements INSERT plus UPDATE plus DELETE are the three main "data-change" statements.
This is how to insert, update, and delete a row in the modules table:
INSERT INTO modules VALUES ('json', 14, 'format functions for JSON');
UPDATE modules SET size = 15 WHERE name = 'json';
DELETE FROM modules WHERE name = 'json';
The corresponding non-SQL Tarantool requests would be:
box.space.MODULES:insert{'json', 14, 'format functions for JSON'}
box.space.MODULES:update('json', {{'=', 2, 15}})
box.space.MODULES:delete{'json'}
This is how one would populate the table with the values that was shown earlier:
INSERT INTO modules VALUES ('box', 1432, 'Database Management');
INSERT INTO modules VALUES ('clock', 188, 'Seconds');
INSERT INTO modules VALUES ('crypto', 4, 'Cryptography');
Constraints
Some data-change statements are illegal due to something in the table's definition. This is called "constraining what can be done". Some types of constraints have already been shown ...
NOT NULL -- if a column is defined with a NOT NULL clause, it is illegal to put NULL into it. A primary-key column is automatically NOT NULL.
UNIQUE -- if a column has a UNIQUE index, it is illegal to put a duplicate into it. A primary-key column automatically has a UNIQUE index.
data domain -- if a column is defined as having data type INTEGER, it is illegal to put a non-number into it. More generally, if a value doesn't correspond to the data type of the definition, it is illegal. Some database management systems (DBMSs) are very forgiving and will try to make allowances for bad values rather than reject them; Tarantool is a bit more strict than those DBMSs.
Now, here are other types of constraints ...
CHECK -- a table description can have a clause "CHECK (conditional expression)". For example, if the CREATE TABLE modules statement looked like this:
CREATE TABLE modules (name STRING,
size INTEGER,
purpose STRING,
PRIMARY KEY (name),
CHECK (size > 0));
then this INSERT statement would be illegal: |br| ``INSERT INTO modules VALUES ('box', 0, 'The Database Kernel');`` |br| because there is a CHECK constraint saying that the second column, the size column, cannot contain a value which is less than or equal to zero. Try this instead: |br| ``INSERT INTO modules VALUES ('box', 1, 'The Database Kernel');``
FOREIGN KEY -- a table description can have a clause "FOREIGN KEY (column-list) REFERENCES table (column-list)". For example, if there is a new table "submodules" which in a way depends on the modules table, it can be defined like this:
CREATE TABLE submodules (name STRING,
module_name STRING,
size INTEGER,
purpose STRING,
PRIMARY KEY (name),
FOREIGN KEY (module_name) REFERENCES
modules (name));
Now try to insert a new row into this submodules table:
INSERT INTO submodules VALUES
('space', 'Box', 10000, 'insert etc.');
The insert will fail because the second column (module_name) refers to the name column in the modules table, and the name column in the modules table does not contain 'Box'. However, it does contain 'box'. By default searches in Tarantool's SQL use a binary collation. This will work:
INSERT INTO submodules
VALUES ('space', 'box', 10000, 'insert etc.');
Now try to delete the corresponding row from the modules table:
DELETE FROM modules WHERE name = 'box';

Loading…

User avatar None

New source string

testing_project / Documentation: how-to/sql/sql_beginners_guide — English

Some data-change statements are illegal due to something in the table's definition. This is called "constraining what can be done". Some types of constraints have already been shown ...
a year ago
Browse all string changes

Things to check

Ellipsis

The string uses three dots (...) instead of an ellipsis character (…)

Reset

Glossary

English English
No related strings found in the glossary.

String information

Flags
read-only
String age
a year ago
Last updated
a year ago
Source string age
a year ago
Translation file
string 40