SQL Server – create table

The CREATE TABLE instruction in SQL Server creates a new database table with all relevant columns of the required data type. The following is the basic form of the CREATE TABLE manual:

CREATE TABLE tab_name (
      col_name1 type_1 [NOT NULL | NULL]
      [{, col_name2 type_2 [NOT NULL | NULL]} ...]
)

CREATE TABLE manual: basic form

table_name – the name of the base table that we create. The maximum number of tables that can contain one database is limited to the number of database objects that cannot exceed 2 billion, including tables, views, etc. In col_name_1, col_name_2, … – the table column names are specified as well as type_1, type_2, … –  are the data types of the respective columns.

The name of the database object can usually consist of four parts, in the form:

[server_name_id.[db_name_id.[schema_name_id.]]object_name_id

Here object_name is the name of the database object, schema_name is the name of the schema to which the object belongs, and server_name and db_name are the names of the server and database to which the object belongs. The names of tables, grouped with the schema name, must be unambiguous within the database. Similarly, the column names must be unambiguous within the table.

Now let’s consider the restriction connected with presence or absence of NULL values in a column. If for a column it is not specified that the values of NULL are allowed (NOT NULL), this column can not contain the value of NULL, and at attempt to insert such a value the system will return an error message.

As already mentioned, a database object (in this case a table) is always created in the database schema. The user can only create tables in a database schema for which he has the authority to execute an ALTER instruction. Any user with the role sysadmin, db_ddladmin or db_owner can create tables in any schema.

The table creator does not have to be the owner of the table. This means that one user can create tables that belong to other users. Similarly, a table created using the CREATE TABLE instruction does not have to belong to the current database if you specify a different (existing) database and scheme name in the table name prefix.

The schema to which the table belongs may have two possible default names. If a table is specified without an explicit schema name, the system searches the corresponding default schema for the table name. If the object name cannot be found in the default schema, the system searches the dbo schema. The names of the tables must always be specified together with the name of the corresponding scheme. This will avoid possible uncertainties.

The example below shows how to create all tables in a SampleDb database. (The SampleDb database must be installed as the current database.)

USE SampleDb_1;

CREATE TABLE Department (
    Number_1         CHAR (4)   NOT NULL,
    DepartName_1 NCHAR (40) NOT NULL,
    Location_1       NCHAR (40) NULL
);

CREATE TABLE [dbo_1].[Project] (
    [Number_1]      CHAR (4)   NOT NULL,
    [ProjectName_1] NCHAR (15) NOT NULL,
    [Budget_1]      FLOAT (53) NULL
);

CREATE TABLE dbo.Employee (
    Id_1                INT        NOT NULL,
    FName         NCHAR (20) NOT NULL,
    LName          NCHAR (20) NOT NULL,
    DepartNumber_1 CHAR (4)   NULL
);

CREATE TABLE dbo.Works_on (
    EmpId_1         INT        NOT NULL,
    ProjectNumber_1 CHAR (4)   NOT NULL,
    Job_1           NCHAR (15) NULL,
    EnterDate_1    DATE       NULL
);

Besides the data type and presence of NULL value, you can specify the following parameters in the column specification:

• DEFAULT offer;
• the IDENTITY property.

The DEFAULT offer in the column specification specifies the default value of the column, i.e. when a new row is inserted into a table, the cell of that column will contain the specified value that will remain in the cell unless another value is entered. The default value can be a constant, such as one of the system functions USER_1, CURRENT_USER_1, SESSION_USER_1, SYSTEM_USER_1, CURRENT_TIMESTAMP_1, and NULL.

The column of identifiers created by specifying the IDENTITY property can only have integer values, which are usually assigned implicitly by the system. Each next value inserted in such a column is calculated by increasing the last value inserted in that column. Therefore, the definition of a column with the IDENTITY property contains (explicitly or implicitly) the initial value and increment step (such a column is also called an auto-increment column).

An example of how to use these instructions is shown below:

USE SampleDb_1;

CREATE TABLE UserInfo_1 (
    -- Autoincrement will be used for Id column IDENTITY(10,5),
    -- i.e. when inserting data, the first element will be assigned to
    -- value 10, second 15, third 20, etc.
    Id INT NOT NULL PRIMARY KEY IDENTITY (10,5),
    Login VARCHAR(40) NOT NULL,

    -- The BirthDate field will have the default date
    -- (if this field is not set explicitly when inserting data).
    BirthDate DATETIME DEFAULT (
         -- Default is 30 years from the current date
         DATEADD(year, -30, GETDATE())   
    )
)

Instruction CREATE TABLE and restrictions on declarative integrity

One of the most important features that the DBMS must provide is the way to ensure data integrity. Restrictions that are used to verify data when modifying or inserting it are called integrity CONSTRAINTs. Data integrity assurance can be implemented by the user in an application program or by a database management system. The most important advantages of providing integrity restrictions by a database management system are the following:

• increases the reliability of the data;
• the programming time is reduced;
• easier maintenance.

Identifying integrity restrictions through the DBMS increases data reliability because it eliminates the possibility that the application programmer may forget to implement them. If the integrity restrictions are provided by the application programs, all the applications affected by these restrictions must contain the corresponding code. If the code is absent in at least one application, the data integrity will be called into question.

If the integrity restrictions are not provided by the database management system, they must be defined in each application that uses the data included in this restriction. In contrast, if the integrity restrictions are provided by a database management system, they only need to be defined once. In addition, the code for the restrictions provided by the applications is usually more complex than in the case of the same restrictions provided by the DBMS.

If the integrity restrictions are provided by the DBMS, in case the restrictions are altered, the corresponding code alterations must be implemented only once – in the database management system. And if the restrictions are provided by the applications, the modification for reflecting the restrictions alterations must be executed in each of these applications.

Database management systems provide two types of restrictions to ensure integrity:

• Declarative restrictions for integrity assurance;
• procedural limitations for integrity assurance, implemented through triggers.

Declarative constraints are defined using the DDL CREATE TABLE and ALTER TABLE instructions. These restrictions can be column level or table level. Column level restrictions are defined along with data type and other column properties in the column declaration, while table level restrictions are always defined at the end of the CREATE TABLE or ALTER TABLE instruction after all columns are defined.

There is only one difference between the column level restrictions and the table level restrictions: the column level restrictions can be applied to only one column, while the table level restrictions can cover more than one table column.

Each declarative restriction is given a name. This name can be assigned explicitly by using the CONSTRAINT option in the CREATE TABLE or ALTER TABLE instruction. If no CONSTRAINT option is specified, the name of the restriction is implicitly assigned by the Database Engine component. It is strongly recommended to use explicit names of limitations, as this can significantly improve the search of these limitations.

Declarative restrictions can be grouped into the following categories:

• DEFAULT offer;
• the UNIQUE sentence;
• the PRIMARY KEY offer;
• the CHECK proposal;
• reference integrity and the FOREIGN KEY offer.

The use of the DEFAULT offer to define the default restriction has been shown earlier. All other restrictions are discussed in the following sections.

UNIQUE proposal

Sometimes several columns or a group of columns in a table have unique values, which allows them to be used as a primary key. Columns or groups of columns that can be used as primary keys are called candidate keys. Each potential key is defined using the UNIQUE sentence in the CREATE TABLE or ALTER TABLE instruction. The syntax of the UNIQUE sentence is as follows:

[CONSTRAINT c_name_1]
	UNIQUE [CLUSTERED | NONCLUSTERED] ({ col_name1_1} ,...)

The CONSTRAINT option in the UNIQUE sentence assigns an explicit name to the potential key. The CLUSTERED or NONCLUSTERED option is related to the fact that the Database Engine component creates an index for each potential table key.

This index can be clustered when the physical order of rows is determined by the indexed order of column values. If the row order is not specified, the index is unclustered. By default, the NONCLUSTERED option is applied. The col_name1 parameter denotes the name of the column that creates the potential key. (The potential key can have up to 16 columns.)

The application of the UNIQUE clause is shown in the example below. (Before running this example, you need to delete the Projects table in the SampleDb database using the DROP TABLE Projects instruction.)

USE SampleDb_1; CREATE TABLE Projects ( Number_1 CHAR(4) DEFAULT 'p1', ProjectName_1 NCHAR (15) NOT NULL, Budget_1 FLOAT (53) NULL, CONSTRAINT unique_number_1 UNIQUE (Number_1) );

Each value in the Projects Number column is unique, including the NULL value. (As with any other UNIQUE-restricted value, if NULL values are allowed for the corresponding column, this column may contain no more than one row with the NULL value.)

An attempt to insert a value already present in a Number column will fail because the system will not accept it. The explicit name of the restriction defined in the example is unique_number.

PRIMARY KEY offer

The primary key of the table is a column or group of columns, the values of which are different in each row. Each primary key is defined using the PRIMARY KEY sentence in the CREATE TABLE or ALTER TABLE instruction. The syntax of the PRIMARY KEY sentence is as follows:

[CONSTRAINT c_name_1]
	PRIMARY KEY [CLUSTERED | NONCLUSTERED] ({col_name1_1} ,...)

All PRIMARY KEY offer parameters have the same values as the corresponding UNIQUE offer parameters of the same name. But unlike UNIQUE column, PRIMARY KEY column does not allow NULL values and has CLUSTERED default value.

The example below shows the declaration of the primary key for the Employee table in a SampleDb database. Before running this example, you need to remove the Employee table from the SampleDb database using the DROP TABLE Employee instruction.

USE SampleDb_1;

CREATE TABLE Employee (
    Id_1                INT        NOT NULL,
    FName         NCHAR (20) NOT NULL,
    LName          NCHAR (20) NOT NULL,
    DepartamentNumber_1 CHAR (4)   NULL,
    CONSTRAINT primary_id_1 PRIMARY KEY (Id)
);

As a result of running this code, the Employee table is created again, where the primary key is defined. The primary key of the table is defined by a declarative restriction to ensure integrity with the name primary_id. This integrity constraint is a table level restriction because it is specified after all Employee table columns have been defined.

The following example is equivalent to the previous one, except that the primary key of an Employee table is defined as a column level constraint.

USE SampleDb_1;
DROP TABLE Employee;

CREATE TABLE Employee (
    Id INT NOT NULL CONSTRAINT primary_id PRIMARY KEY,
    FName         NCHAR (20) NOT NULL,
    LName          NCHAR (20) NOT NULL,
    DepartamentNumber_1 CHAR (4)   NULL
);

In the example, the PRIMARY KEY offer belongs to the declaration of the corresponding column, along with the declaration of its data type and property to contain values NULL. For this reason, this restriction is called column level restriction.

The CHECK offer

The check limit (CHECK CONSTRAINT) defines the conditions for the data inserted in a column. Every row or value that is inserted in the table that updates the column value must meet these conditions. The check constraints are set by the CHECK offer defined in the CREATE TABLE or ALTER TABLE instruction.

The syntax of the CHECK sentence is as follows:

[CONSTRAINT c_name_1]
	CHECK [NOT FOR REPLICATION] expr

The expression parameter must have a logical value (true or false) and may refer to any column in the current table (or only to the current column if defined as a column level limit), but not to other tables.

The CHECK offer is not enforced for data replication if the NOT FOR REPLICATION parameter is present. (When replicating, the database, or part of it, is stored in multiple locations. With replication, you can increase the level of data availability.)

The example below shows the application of the CHECK proposal:

USE SampleDb_1;

CREATE TABLE Customer (
	CustomerId_1 INTEGER NOT NULL,
	CustomerRole_1 VARCHAR(100) NULL,
	CHECK (CustomerRole IN ('admin', 'moderator', 'user'))
);

The Customer table created in the example includes a CustomerRole column containing the corresponding check limit. When you insert a new value that differs from the value in the set (‘admin’, ‘moderator’, ‘user’), or when you try to change an existing value to a value that differs from these values, the database management system returns an error message.

FOREIGN KEY Offer

A foreign key is a column (or group of columns in a table) containing values that match the values of the primary key in the same or another table. The foreign key is defined using the FOREIGN KEY sentence in combination with the REFERENCES sentence. The syntax of the FOREIGN KEY sentence is as follows:

[CONSTRAINT c_name_1] [[FOREIGN KEY] ({col_name1_1} ,...)] REFERENCES table_name ({col_name2_1},...) [ON DELETE {NO ACTION_1 | CASCADE_1  | SET NULL_1  | SET DEFAULT_1 }] [ON UPDATE {NO ACTION_1  | CASCADE_1  | SET NULL_1  | SET DEFAULT_1 }]

The FOREIGN KEY offer clearly defines all columns included in the external key. The REFERENCES sentence specifies the name of the table containing the columns that create the corresponding primary key. The number of columns and their data type in the FOREIGN KEY offer must match the number of corresponding columns and their data type in the REFERENCES offer (and, of course, they must match the number of columns and data types in the primary key of the table to which they refer).

A table containing a foreign key is called a referencing table, and a table containing the corresponding primary key is called a referenced table or parent table. The example below shows the external key declaration for the Works_on table in a SampleDb database:

USE SampleDb_1;

CREATE TABLE Works_on (
    EmpId_1         INT        NOT NULL,
    ProjectNumber_1 CHAR (4)   NOT NULL,
    Job_1           NCHAR (15) NULL,
    EnterDate_1     DATE       NULL,
	CONSTRAINT primary_works PRIMARY KEY (EmpId_1, ProjectNumber_1),

    CONSTRAINT foreign_employee FOREIGN KEY (EmpId_1) 
	     REFERENCES Employee (Id_1),

    CONSTRAINT foreign_project FOREIGN KEY (ProjectNumber_1) 
	     REFERENCES Projects (Number_1)
);

The Works_on table in this example is defined with three declarative constraints to ensure integrity: primary_works, foreign_employee and foreign_project.

These constraints are a table level limit, where the first one specifies the primary key and the second and third one specifies the external key of the Works_on table. In addition, external keys define Employee and Projects tables as reference tables, and their Id and Number columns as the corresponding primary key of the column with the same name in the Works_on table.

The FOREIGN KEY offer can be omitted if the foreign key is defined as a table level limit, since the column to which the limit applies is an implicit “list” of the foreign key columns, and the REFERENCES keyword is sufficient to specify what type of limit that limit is. The table may contain at most 63 FOREIGN KEY restrictions.

The definition of external keys in database tables imposes another important restriction for integrity assurance: reference integrity.

Referential integrity ensures that rules are followed for inserting and updating tables containing the external key and the corresponding primary key restriction. The example above has two such limitations: foreign_employe and foreign_project. The REFERENCES clause in the example defines Employee and Projects tables as reference (parent) tables.

If reference integrity is specified for two tables, modification of values in the primary key and the corresponding external key will not always be possible. In the following sections, we will consider when it is possible and when it is not.

Modifying the external or primary key values can cause problems in four cases. All these cases will be demonstrated using the SampleDb database. The first two cases involve modifications to the reference table, and the last two cases involve modifications to the parent table.

Possible problems with referential integrity – case 1

Inserting a new row in the Works_on table with the employee number 11111. The corresponding Transact-SQL instruction looks like this:

USE SampleDb_1;

INSERT INTO Works_on VALUES (11111, 'p1', 'qwe', GETDATE())

When inserting a new row into the Works_on child table, the new EmpId employee number is used, for which there is no matching employee (and no number) in the Employee parent table. If reference integrity is defined for both tables as it was done earlier, the Database Engine component will not allow inserting a new row with this EmpId number.

Possible problems with referential integrity – case 2

Change the number of employee 9502 in all rows of the Works_on table to 11111. The corresponding Transact-SQL instruction looks like this:

USE SampleDb_1;

UPDATE Works_on
	SET EmpId = 11111 WHERE EmpId = 9502;

In this case, the existing external key value in the reference Works_on table is replaced by a new value for which there is no matching value in the parent Employee table. If reference integrity is defined for both tables, the database management system will not allow modification of the string with such number EmpId in the Works_on table.

Possible problems with referential integrity – case 3

Replacing the value 9502 of employee Id with 22222 in the Employee table. The corresponding Transact-SQL instruction will look like this:

USE SampleDb_1;

UPDATE Employee
	SET Id = 22222 WHERE Id = 9502;

In this case an attempt is made to replace the existing value of 9502 of employee Id with 22222 only in the Employee parent table without changing the corresponding Id values in the reference Works_on table. The system does not allow this operation to be performed.

Reference Integrity does not allow the existence of values in a reference table (a table for which the PRIMARY KEY clause defines a foreign key) for which the parent table (a table for which the PRIMARY KEY clause defines a primary key) does not have a corresponding value.

Otherwise, such rows in the reference table would be “orphans”. If the Employee table modification described above were allowed, then the rows in the Works_on table with Id value 9502 would be orphaned. Therefore, the system does not allow this modification.

Possible problems with referential integrity – case 4

Deleting a row in Employee table with Id value equal to 9502.

This case is similar to case 3. If this operation was performed, the row with Id value would be removed from the Employee table, for which there are matching values in the reference (child) Works_on table.

ON DELETE and ON UPDATE options

The Database Engine component may react differently to an attempt to delete and modify a primary key. If you try to update the external key values, all these updates will be inconsistent with the corresponding primary key, the database will refuse to perform these updates and will display an error message.

However, when trying to make updates to the primary key values that cause inconsistencies in the corresponding external key, the database system can react quite flexibly. In total, there are four options that determine how a database system can respond:

NO ACTION

Only those values in the parent table for which there are no corresponding values in the external key of the child (referring) table are modified (updated or deleted).

CASCADE

It is allowed to modify (update or delete) any values in the parent table. When updating the primary key value in the parent table or deleting the entire string containing that value, the child (reference) table updates (i.e. deletes) all rows with the corresponding external key values.

SET NULL

It is allowed to modify (update or delete) any values in the parent table. If an update of a value in the parent table causes inconsistency in the child table, the database system assigns the external key of all relevant rows in the child table the value NULL. The same happens when deleting a row in the parent table that causes inconsistency in the child table. Thus, all data inconsistencies are skipped.

SET DEFAULT

Similar to SET NULL options, but with one exception: all external keys corresponding to the primary key to be modified are assigned a default value. It goes without saying that after the modification the primary key of the parent table must still contain the default value.

The first two of these options are supported in the Transact-SQL language. Using ON DELETE and ON UPDATE options is shown in the example below:

USE SampleDb_1;

CREATE TABLE Works_on (
    EmpId_1         INT        NOT NULL,
    ProjectNumber_1 CHAR (4)   NOT NULL,
    Job_1           NCHAR (15) NULL,
    EnterDate_1     DATE       NULL,
	CONSTRAINT primary_works PRIMARY KEY (EmpId_1, ProjectNumber_1),

    CONSTRAINT foreign_employee FOREIGN KEY (EmpId_1) 
	     REFERENCES Employee (Id_1) ON DELETE CASCADE,

    CONSTRAINT foreign_project FOREIGN KEY (ProjectNumber_1) 
	     REFERENCES Projects (Number_1) ON UPDATE CASCADE
);

In this example, the Works_on table is created using ON DELETE CASCADE and ON UPDATE CASCADE options. If the Works_on table is loaded with values, each row deletion in the Employee table will cause cascading of all rows in the Works_on table that have values of the external key corresponding to the values of the primary key of rows that are deleted in the Employee table.

Similarly, each update of the Project Number column value will cause the same update of all corresponding ProjectNumber column values in the Works_on table.

How to create Table,Insert,Update,Delete in SQL Server very easy steps