Working with databases is directly related to changing tables and the data contained in them. But before starting an action, tables must be created. To automate this process, there is a special SQL function - "CREATE TABLE".
First thing!
Before you understand the process of creating tables using the MS SQL command "CREATE TABLE", you should dwell on what you need to know before you start using the function.
First of all, you need to come up with the name of the table - it must be unique, in comparison with others in the database, and follow several rules. The name must begin with the letter (az), after which any letters, numbers and underscore can follow, and the resulting phrase should not be a reserved word. The table name must not exceed 18 characters.
Having decided on the name, you should develop a structure: come up with the names of the columns, think over the data type used in them, and which fields must be filled out. Here you should immediately determine the fields of foreign and primary keys, as well as possible restrictions on data values.
The rest of the nuances of the table can be quite easily corrected, so at the stage of creating the table, they can not be thought out to the end.
Syntax
Having developed the structure of the table, you can proceed to its creation. To do this is quite simple using the SQL function "CREATE TABLE". In it, the user is required to indicate the table name and the list of columns invented earlier, indicating the type and name for each of them. The syntax of the function is as follows:
CREATE TABLE table_name
({column_name datatype [DEFAULT expression] [column_constraint] ... | table_constraint}
[, {column_name datatype [DEFAULT expression] [column_constraint] ... | table_constraint}] ...)
Arguments used in the construction of a function mean the following:
- table_name - table name
- column_name - column name
- datatype - data type used in this field
- DEFAULT is the expression used in the default column.
It is also possible to use two more function arguments:
- colum_constraint - column parameters
- table_constraint - table parameters
In them, the user can specify the restrictions or conditions for filling the table required for work.
Features of creating tables
When writing a query with the SQL function "CREATE TABLE", sometimes it is necessary to establish rules for filling in the fields. To do this, you need to add special function attributes that define a particular set of conditions.
In order to determine whether a cell can contain an empty value, after specifying the name and type of the column, one of the keywords should be written: NULL (there may be empty values) or NOT NULL (the field must be filled).
When creating a table, in most cases it is required to unify each record in order to avoid two identical ones. For this, line numbering is most often used. And in order not to require the user to know the last number in the table, in the "CREATE TABLE" function it is enough to specify the primary key column by writing the keyword "Primary key" after the corresponding field. Most often, it is on the primary key that tables are joined together.
To ensure coupling with the Primary key, the FOREIGN KEY foreign key property is used. By specifying this property for a column, you can ensure that this field contains a value that matches one of those in the primary key column of the same or another table. In this way, data consistency can be ensured.
To ensure that it matches a given set or definition, use the CHECK attribute. It is registered last in the list of function arguments and has a certain logical expression as a personal parameter. With it, you can limit the list of possible values, for example, using only the letters "M" and "F" in the field of the table "Gender".
In addition to those presented, the SQL function "CREATE TABLE" has many more specific attributes, but they are used much less often in practice.
Examples
To fully understand the principle of the function, it is worth considering in practice how CREATE TABLE (SQL) works. The example below creates the table shown in the figure:
CREATE TABLE Custom
(ID CHAR (10) NOT NULL Primary key,
Custom_name CHAR (20),
Custom_address CHAR (30),
Custom_city CHAR (20),
Custom_Country CHAR (20),
ArcDate CHAR (20))
As you can see, the parameter of the possible absence of a value in the cell (NULL) can be omitted, since it is used by default.