SQL Server create table

Preamble

With the CREATE TABLE instruction in SQL Server, a new database table is made with all the needed columns of the right type. The CREATE TABLE manual’s basic format is as follows:

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 number of database objects, which includes tables, views, etc., can’t be more than 2 billion. This means that a database can only have a maximum of 2 billion tables. The column names in the table are specified in col_name_1, col_name_2,…, as are the data types in type_1, type_2,…

Most of the time, the name of the database object is made up of four parts:

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

Here, the database object is called object_name, its schema is called schema_name, and its server and database are called server_name and db_name, respectively. When put together with the schema name, the names of the tables must be clear in the database. Similar to this, the table’s column names must be clear.

Now, let’s talk about the restriction that comes with the presence or absence of NULL values in a column. When trying to insert a NULL value into a column for which it has not been specified that such values are permitted (NOT NULL), an error message will be returned by the system.

A database object, in this case a table, is always created in the database schema, as was already mentioned. The user can only add tables to a database schema if he has permission to run an ALTER command for that schema. Tables can be created in any schema by any user who has the role sysadmin, db_ddladmin, or db_owner.

The owner of the table need not be the one who created it. This means that a user has the ability to make tables for other users to use. In the same way, a table created with the CREATE TABLE command doesn’t have to be part of the current database if the table name prefix specifies a different (existing) database and scheme name.

There are two potential default names for the schema to which the table belongs. If a table is specified without a schema name, the system looks for the table name in the default schema. The system searches the dbo schema if the object name cannot be found in the default schema. The names of the tables and the name of the corresponding scheme must always be specified together. This will help to eliminate any uncertainties.

A SampleDb database’s entire set of tables can be created using the example below. (The current database installation must be the SampleDb 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 );

In addition to the data type and whether or not there is a NULL value, the following parameters can be set in the column specification:

• DEFAULT offer;
• the IDENTITY property.

When a new row is added to a table, the specified value will be placed in the column’s cell, where it will stay until a new value is entered. This default value is specified by the DEFAULT offer in the column specification. 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.

When you use the IDENTITY property, you create a column of identifiers that can only have integer values, which are usually given by the system. When a new value is added to a column like this, the previous value is increased. So, the definition of a column with the IDENTITY property includes both the initial value and the increment step, either explicitly or implicitly. This kind of 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

The ability to guarantee data integrity is one of the most crucial features that the DBMS must offer. Restrictions that are used to verify data when modifying or inserting it are called integrity CONSTRAINTs. Users can set up data integrity assurance in an application program or a database management system can do it for them. The following are the most significant benefits of using a database management system to provide integrity constraints:

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

Using the DBMS to find integrity restrictions makes data more reliable because it eliminates the chance that the application programmer will forget to put them in place. If the integrity restrictions come from the application programs, then all the programs that are affected by these restrictions must have the code that goes with them. If the code is absent in at least one application, the data integrity will be called into question.

If the database management system doesn’t set the integrity restrictions, they must be set in each application that uses the data that is part of the restriction. If, on the other hand, a database management system handles the integrity restrictions, they only need to be set up once. Also, the code for the restrictions that applications provide is usually more complicated than the code for the same restrictions that the DBMS provides.

If the DBMS provides the integrity constraints, if the restrictions are changed, the corresponding code changes must be implemented only once—in the database management system. Additionally, if the applications themselves provide the restrictions, each of these applications must be modified to reflect the changes in the restrictions.

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 limitations may apply at the table or column level. Table level restrictions are always defined at the end of the CREATE TABLE or ALTER TABLE instruction after all columns have been defined, whereas column level restrictions are defined along with the data type and other column properties in the column declaration.

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. By using the CONSTRAINT option in the CREATE TABLE or ALTER TABLE instruction, this name can be explicitly assigned. If no CONSTRAINT option is given, the Database Engine component will choose a name for the restriction on its own. It is strongly advised to use explicit names for limitations, as this can improve the search for these limitations significantly.

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.

It has already been demonstrated how to define the default restriction using the DEFAULT offer. All other restrictions are covered in the sections that follow.

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. In the CREATE TABLE or ALTER TABLE instruction, the UNIQUE clause is used to define each possible key. The syntax of the UNIQUE sentence is as follows:

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

The potential key is given an explicit name by the CONSTRAINT option in the UNIQUE sentence. The Database Engine component creates an index for every potential table key, which is related to the CLUSTERED or NONCLUSTERED option.

When the physical order of rows is established by the indexed order of column values, this index can be clustered. The index is unclustered if the row order is not specified. The NONCLUSTERED option is used by default. 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, including NULL, is distinct. (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.)

The system will reject any attempt to insert a value that is already present in a Number column. 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. The PRIMARY KEY sentence in the CREATE TABLE or ALTER TABLE instruction defines each primary key. The following is the syntax of the PRIMARY KEY phrase:

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

All PRIMARY KEY offer parameters have the same values as the UNIQUE offer parameters with the same name. But unlike UNIQUE columns, PRIMARY KEY columns are CLUSTERED by default and do not permit NULL values.

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. To ensure integrity, the table’s primary key is defined by a declarative restriction with the name primary_id. Because it is specified after all the columns in the Employee table have been defined, this integrity constraint is a restriction at the table level.

The following example is similar to the previous one, except that an Employee table’s primary key 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 );

The PRIMARY KEY offer in the example is part of the declaration of the corresponding column, along with the declaration of its data type and property to contain NULL values. For this reason, this restriction is referred to as a column level restriction.

The CHECK offer

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

The CHECK sentence has the following syntax:

[CONSTRAINT c_name_1] 	CHECK [NOT FOR REPLICATION] expr

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

In the presence of the NOT FOR REPLICATION parameter, the CHECK offer is not enforced for data replication. (When replicating, the database or a portion of it is kept in different places. 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')) );

In the example, a Customer table is made with a CustomerRole column that has the check limit for that role. The database management system responds with an error message when you attempt to insert a new value that differs from the set of values (‘admin’,’moderator’, and ‘user’) or when you try to change an existing value to a value that does so.

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. When the FOREIGN KEY sentence is put together with the REFERENCES sentence, the foreign key is defined. 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 describes in detail all columns that comprise the external key. In the REFERENCES clause, the name of the table that holds the columns that make up the primary key is given. The number and type of columns in the FOREIGN KEY offer must match the number and type of columns in the REFERENCES offer. They must also match the number and type of columns in the primary key of the table to which they refer.

A table with a foreign key is called a “referencing table,” and a table with the primary key that goes with it is called a “referenced table” or “parent table.” 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 ones specify the external key of the Works_on table. In addition, external keys define the Employee and Projects tables as reference tables, and their Id and Number columns as the corresponding primary keys 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.

Reference integrity is another important restriction that comes from the way external keys are defined in database tables.

Referential integrity makes sure that rules are followed when tables with an external key and a primary key restriction are added or changed. The example above has two such limitations: foreign_employee and foreign_project. The REFERENCES clause in the example defines the Employee and Projects tables as reference (parent) tables.

If reference integrity is set for two tables, you won’t always be able to change the values in the primary key and the external key that goes with it. 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 with the employee number 11111 into the Works_on  table. 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 table Works_on is changed to a new value that has no match in the parent table Employee. 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

In the Employee table, replace the value 9502 of the employee ID with 22222.The corresponding Transact-SQL instruction will look like this:

USE SampleDb_1;  UPDATE Employee 	SET Id = 22222 WHERE Id = 9502;

In this instance, the reference Works_on table’s employee Id value of 9502 is being attempted to be replaced by the value 22222 while leaving the corresponding Id values in the Employee parent table alone. The system does not allow this operation to be performed.

Reference Integrity doesn’t let values exist in a reference table (a table for which the PRIMARY KEY clause defines a foreign key) that don’t have a corresponding value in the parent table (a table for which the PRIMARY KEY clause defines a primary key).

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

Deletes a row from the Employee table with the ID 9502.

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

ON DELETE and ON UPDATE options

The Database Engine component may react differently to an attempt to delete and modify a primary key. The database will refuse to perform any updates if you try to change the values of the external keys because they are all out of sync with the corresponding primary keys. It will also give you an error message.

But if you try to change the values of the primary key in a way that makes the external key inconsistent, the database system can respond in a variety of ways. In total, there are four options that determine how a database system can respond:

NO ACTION

Only those values in the parent table that don’t have a match in the external key of the child table are changed (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 updating a value in the parent table causes an inconsistency in the child table, the database system gives the external key of all relevant rows in the child table the value NULL. The same thing happens when a row in the parent table is deleted, causing inconsistency in the child table. Thus, all data inconsistencies are skipped.

SET DEFAULT

Similar to SET NULL options, but with one difference: a default value is assigned to each external key that corresponds to the primary key being modified. It goes without saying that the parent table’s primary key must continue to have the default value after the modification.

The first two of these options are supported in the Transact-SQL language. Following is an example of using the ON DELETE and ON UPDATE options:

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 );

The Works_on table is created in this example using the ON DELETE CASCADE and ON UPDATE CASCADE options. The cascading of all rows in the Works_on table whose values of the external key correspond to the values of the primary keys of deleted rows in the Employee table will occur if the Works_on table is loaded with values. This will happen if the Works_on table is loaded with values.

Similarly, any change to the Project Number column value will result in a change to all corresponding ProjectNumber column values in the Works_on table.

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

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It finds the root causes of complex database scalability and performance problems that affect business across a growing number of cloud, RDBMS, NoSQL, and machine learning database platforms.