SQL

Examples: Databases, schemas and tables in SQL

164 Views
Oct 3, 2021
Examples: Databases, schemas and tables in SQL

The notions of databases and schemas are unfortunately confused by editors, who have given them different applications.

In Oracle, a server instance manages a database, divided into schemas and tablespaces. An Oracle user connects to the server, and the server places it in its default schema, usually shared with other users.

Under MySQL, a server instance manages as many databases as you want. The notion of a subspace of tables does not exist. A MySQL user connects to the server, which places it in its database, possibly shared with other users.

We see that in the first case, the workspace is called schema , and in the second database . This confusing difference does not actually change much for the non-administrator user: he works in a space, possibly with others, without worrying about the machinery that is outside his space. .

A table is a structure in which you can store data. A table is a set of rows, each row is a set of columns. Each column is associated with a data type: integer, character string, date or binary type. A table can have zero rows, but it must always have at least one column to exist.

2.1. Creating a table

The creation of a table is done by the call to the command create table. This command is complex, so we will split the arguments it can take into several parts.

The first argument this command takes is the name of the table, which must be unique within a workspace.

The second argument this command takes is made up of two parts:

  • a list of columns, which must have at least one column;
  • a list of constraints on this table, possibly empty.

Here is a simple example of a table creation, with a single column, and no constraints on the table.

Example 18. A table with one column without constraint

create  table (
   id  int
) ;


Now let’s take a look at how to declare columns, then we’ll come back to table constraints.

2.2. Creating a column

The declaration of a column is itself a complex declaration. It is made up of three elements:

  • the name of the column, which must be unique within its table;
  • the type of the column;
  • the constraints on this column.

2.2.1. Column name

A column name is a classic character string. In theory, you can use accented characters to create columns, and mix uppercase / lowercase. In practice, the compatibility issues that this can pose, among others between Windows and Linux environments, make this kind of practice dangerous.

2.2.2. Type of a column

MySQL defines about thirty different types for a column, which can be grouped into four families:

  • character strings, of fixed or variable length;
  • numbers, the precision of which can be fixed;
  • the dates ;
  • binary types, of varying sizes.

These different elements will be detailed in the Data type chapter .

2.2.3. Constraints on a column

There are seven constraints on the columns available in SQL:

  • The primary key constraint primary key. Any table in a database must have a primary key, even if this is not mandatory. There can be only one column carrying the constraint primary key, although it is possible to group several columns in the declaration of a primary key. This constraint implies two things for the values ​​of this column: there cannot be null value, and there cannot be duplicates.
  • The foreign key constraint foreign key references. This constraint takes a type parameter table(colonne), which designates which column of which table is referenced by this column. This constraint is called a referential integrity constraint . Often the column referenced by the foreign key is itself the primary key in its table, but this is not a requirement.
  • The constraint nullor not null. This constraint imposes that the values ​​carried by this column can be zero, or on the contrary must not be zero. As by default the values ​​carried by a column can be null, it is quite rare to meet the constraint . On the other hand, the second constraint is very frequent. null
  • The constraint uniqueindicates that duplicates are not accepted in this column.
  • The constraint default. This constraint takes one parameter, which indicates the default value for cells in this column. This value is therefore used in all cases where the value of this cell is not specified when creating a row.
  • The constraint check. This constraint is very powerful and makes it possible to validate the inserted values.
  • The constraint collate. This constraint is used on columns that carry strings, and indicates how those strings should be compared with each other. The snack String represents the action of comparing or the sort. This problem is very simple when you limit yourself to lowercase unaccented letters, and can quickly become very complex when you take into account accents (also called diacritics) and lowercase / uppercase differences. The use of this constraint makes it possible to indicate which comparison methods will be used. We will not go further on this point.

2.3. Stresses on a table

There are four types of constraint on a table:

  • The constraint primary key. This constraint takes a list of columns as a parameter, and indicates that the combination of values ​​must constitute a primary key for the row considered.
  • The constraint foreign key: indicates that the group of columns in parameters references the group of column in parameter of referenceswhich must follow. The referenced columns or group of columns are not necessarily primary keys, the only constraint necessary on them is unique.
  • The constraint unique. This constraint takes a list of columns as a parameter, and indicates that the combination of the values ​​of these columns for a given row must be unique.
  • The constraint check: indicates a certain number of rules which must be verified on a group of columns.

2.4. Naming of constraints

Finally, note that it is possible to name all the constraints that we define. If one does not give an explicit name to these constraints, then they will bear names by default. These names are used by database systems in their error messages, so sometimes it is very useful to give explicit names to these constraints, so that they can be referenced quickly.

We will see several examples of constraint naming in the examples that follow.

2.5. Examples of creating tables

Now let’s see examples using all of these definitions.

Example 19. Table with primary key and unique name

create  table country (
   id     int   primary   key ,

   name  varchar(32)  unique  not  null
) ;


This first table is used to record countries. Each country has one ID, which is its primary key. It is described by a name, which must be unique, and not zero.

Example 20. Table with composite primary key, foreign, constraint check

create  table team (
   country_id  int  not  null  references country(id) , 
   id   you are   not   null ,
    
   name  varchar(32)  unique  not  null, 

    constraint pk_team  primary  key (id, country_id)
);


Our second table is used to store teams. Each team belongs to a country, represented by the column country_id, declared as foreign key on the column idof the table country.

It has a constraint declaration on a table: a composite primary key, on the idand columns country_id. A primary key declared on multiple columns is called a composite primary key .

Example 21. Table with checkcomplex constraint

create  table player (
   country_id           int  not  null  references country(id) , 
   id                    you are   not   null ,

   team_id              int  references team(id), 

   family_name  varchar(32)  not  null, 
   first_name   varchar(32)  not  null, 

   retired              int  not  null  default 0  check (retired = 1  or retired = 0),

   birth_year           int  not  null  check (birth_year > 0), 
   prof_year            int  not  null  check (prof_year > birth_year), 

    constraint pk_player  primary  key (id, country_id)
);


The same composite primary key and foreign key mechanism is used to create this table player. Note that a player can belong to a team, but this team is not part of the primary key. Indeed, an inter-contract player is not part of any team, so this field must be able to be zero, so he cannot participate in the primary key. Also, this field will certainly see its value vary, and it is never a good idea to vary the primary key of an entity.

Finally, note the declaration of the constraint on the value retired, which takes the value 0 by default, and can only take the values ​​1 or 0.

2.6. Modifying a table

It is possible to modify certain characteristics of a table once it has been created. The command that is used to do this is alter tablefollowed by the name of the table.

Finally, it should be noted that certain operations can be quite delicate. For example, adding a column to a table that contains rows will automatically fill that column with zero values. If this column is to ultimately carry the restriction not null, things need to be done in stages.

2.6.1. Adding a column

Adding a column uses the same syntax as creating a column in a table. Simply the actual creation command is placed after the alter table. In the following example we add a column capital_nameto our table country.

Example 22. Adding a column to the table country

alter  table country
    add  column capital_name  varchar(32) ;

2.6.2. Adding a Constraint

Adding a constraint also follows the same syntax as creating a constraint on a table. In the following example, we add a constraint making it possible to guarantee the uniqueness of the pair formed by the name of the country and the name of its capital.

Example 23. Adding a constraint

alter  table country
    add  constraint unique_name_capital  unique(name, capital_name) ;


A very frequently encountered example is the addition of a foreign key type constraint from one table to another. Indeed, such constraints require that the target table exists before the creation of this constraint. In the case of circular constraints, this condition cannot be fulfilled, which happens frequently. The approach conventionally used therefore consists in creating the tables first, then the foreign key type constraints once they have all been created. In particular, this is the approach used by all object / relational mapping tools.

Example 24. Adding a foreign key type constraint

alter  table player
    add  constraint FK_player_team  foreign  key(team_id) 
    references team(id) ;

2.6.3. Special case of not null

In MySQL, it is not possible to change the constraint not nullusing the command alter table ... add constraint. Adding this constraint is done using the following syntax.

Example 25. Adding a Constraint not null

alter   table country
   modify  column id  int  not  null ;


One of the consequences is that this constraint cannot be named. Note that it is necessary to specify again the type of data carried by this column.

2.6.4. Delete a column

Removing a column should be done with care. Indeed, a column can be referenced by many SQL elements: table constraints, foreign keys, indexes or views. In all these cases, it is necessary to analyze all these elements, and possibly to modify them before proceeding to this deletion.

The following example shows removing the column capital_namefrom our table country.

Example 26. Deleting a column

alter  table country
    drop  column capital_name ;


If we follow the examples in this chapter under MySQL, we will realize that the constraint unique_name_capitalstill exists, but that it no longer references the column that we have just deleted.

2.6.5. Removing a Constraint

The deletion of a constraint is done using the name of this constraint. If one took care to name explicitly all its constraints that will not pose a problem, if not it is necessary to interrogate the base in order to find the name allotted to the constraint which one wishes to erase.

The following example allows you to delete the constraint unique_name_capitalthat we created on the table country.

Example 27. Deleting a constraint

-- version SQL standard
 alter  table country
    drop  constraint unique_name_capital ;

 -- version MySQL
 alter  table country
    drop  index unique_name_capital ;


Note a difference in syntax between standard SQL and the MySQL command.

2.7. Clearing a table

The command to delete a table is drop. To erase the table, all playeryou have to do is type the following code.

Example 28. Clearing the table player

drop  table player ;

2.8. Notes on restrictand cascade

These two keywords, added to the command to create a foreign key, allow you to specify the behavior of the system when a row referenced by this key is deleted or updated. In mode restrict, erasure is prohibited, in mode cascade, it propagates.

In the case of our example of a key between the table Marinsand the table Communes, we cannot delete a referenced municipality if the foreign key has been declared in mode restrict, on the other hand, all the sailors born in this municipality will be deleted if the key has been declared. in mode cascade.

Example 29. Declaring a foreign key in mode restrict

alter   table sailors
     add   constraint   foreign   key FK_communes (id_commune) 
                    references communes (id)   on   delete   restrict ;

Leave a Reply

Your email address will not be published. Required fields are marked *