Create Table
Create a new table.
grammar
Create Table [Database_name. [Owner]. | {
| [{Primary Key | Unique} [, ... n]) [ON {fileGroup | default}] [textiMage_on {filegroup | default}]
Database_name
Is the database name to create a table in it. Database_name must be the name of the existing database. If the database is not specified, Database_name defaults to the current database. The currently connected login must have an existing user ID in the database specified by Database_name, and the user ID must have permissions to create a table.
Owner
Is the user ID name of the new table owner, Owner must be the existing user ID in the database specified by Database_name, Owner defaults to the user ID associated with the current connection in the database specified by Database_name. If the CREATE TABLE statement is executed by the DB_DBOWNER or DB_DDLADMIN fixed database role member in the database specified by the sysadmin fixed server role or Database_name, Owner can specify other user IDs other than the user ID associated with the currently connected login. If the user ID associated with the login to execute the CREATE TABLE statement has only the permissions of the creation table, the OWNer must specify the user ID associated with the current login. Sysadmin fixed server role members or aliasing logins for DBO users are associated with user ID DBO; therefore, the default owner of the table created by these users is DBO. Table owners created by the above two roles defaults to the user ID associated with the login.
Table_name
Is the name of the new table. The table name must meet the identifier rules. The owner.table_name combination in the database must be unique. Table_name can contain up to 128 characters, but the table name of the local temporary table (a number in front of the name is in front #) can only contain 116 characters.
COLUMN_NAME
It is the column name in the table. The column name must meet the identifier rules and unique in the table. Column_name can be omitted by the column created with TimeStamp data type. If you do not specify a column_name, the name of the TimeStAMP column defaults to TimeStamp.
Computed_column_expression
Is the expression that defines the calculation column value. The calculation column is a virtual column that is physically not stored in the table. The calculation column is calculated by expressing other columns in the same table. For example, the compute column can be defined like this: COST AS Price * Qty. The expression can be a column name, constant, function, and variable, or any combination of the above elements connected to one or more operators. Expressions cannot be queried for children.
Calculating columns can be used to select a list, where clause, order by clause, or any other location that can use regular expressions, but the following cases cannot be used as a default or foreign key constraint definition, and cannot be defined with the Not Null constraint use together. However, if the computational column is defined by an expression of a certainty, and the data type of the calculated result is allowed in the index column, the column can be used as a key column in the index, or as part of the Primary Key or UNIQUE constraint. For example, if you contain integer columns A and B, you can create an index on the calculation column A B. However, you cannot create an index on computational column A DATEPART (DD, getDate (), because in future calls, its value may change. The calculation column cannot be used as the object of the INSERT or UPDATE statement.
The column values used in the table in the table varies depending on the line, so the calculated column values per row may be different.
Calculating the column is empty, which is automatically determined by SQL Server according to the expression used. Even if there is only a column that cannot be empty, most expressions are also considered to be empty because possible underflow or overflows will also generate null results. Use the ColumnProperty function (Allowsnull property) to view any computational columns in the table for empty. By specifying isnull (check_expression, constant), where constant is a non-NULL value replacing any NULL results, the empty expression expr can convert to an unmoiled expression.
ON {filegroup | default}
Specify the file group of the storage table. If FileGroup is specified, the table will be stored in the specified file group. This file group must be exist in the database. If the default is specified, or the ON parameter is not specified at all, the table is stored in the default file group.
ON {fileGroup | default} can also be specified in the Primary Key constraint or Unique constraint. These constraints create an index. If FileGroup is specified, the index will be stored in the specified file group. If default is specified, the index will be stored in the default file group. If there is no file group in the constraint, the index will be stored in the same file group. If the primary key constraint or UNIQUE constraint creates a gathering index, the data page of the table will be stored in the same file group with the index.
Description In the context of On {fileGroup | default} and textimage_on {filegroup | default}, default is not a keyword. Default is the identifier of the default file group and needs to be triggered, such as On "Default", ON [Default] and TextImage_on "default" or textimage_on [default].
TEXTIMAGE_ON
It is a keyword that represents text, ntext, and image columns stored in the specified file group. If there is no Text, NText, or Image column in the table, you cannot use textImage. If you do not specify TextImage_ON, Text, Ntext, and Image columns will be stored in the same file group.
DATA_TYPE
Specifies the data type of the column. Can be a system data type or a user-defined data type. The user-defined data type must be created with sp_addtype before you can use it in the table definition.
In the CREATE TABLE statement, the NULL / NOT NULL assignment of the user-defined data type can be replaced. But the length standard cannot be changed; the length of the user-defined data type cannot be specified in the CREATE TABLE statement.
DEFAULT
If the value is not explicitly available during the insertion, specify the value provided as the column. The default definition can be applied to any column other than the column defined as TimeStamp or columns with Identity properties. When the table is removed, the default definition will be deleted. Only constant values (such as strings), system functions (such as system_user ()) or NULL can be used as default. To maintain an earlier version of SQL Server, you can assign a constraint name to the default. Constant_expression
It is a constant, NULL, or system function used as the default value of the column.
Identity
Indicates the new column is the identity column. When adding new rows to the table, Microsoft® SQL ServerTM will provide a unique, incremented value for the identity column. Identification columns are usually used as a unique line identifier for the table with the Primary Key constraint. The Identity property can be assigned to Tinyint, Smallint, Int, Bigint, Decimal (P, 0), or NuMERIC (P, 0) column. You can only create one identity column for each table. The binding default value and default constraint cannot be used for the identity column. The seeds and increments must be specified at the same time, or both are not specified. If both are not specified, the default value (1, 1) is taken.
SEED
It is the value used by the first row of load tables.
increment
It is an incremental value added to the identification value of the previous line.
NOT for Replication
Indicates that when copying the login (such as SQLRepl), does not force the identity property when inserting data into the table. The replicated row must retain the key value given in the published database; the NOT for Replication clause ensures that the new identity value is not intended to the row inserted into the replication process. Other logins inserted still has a new identifier value created in a usual manner. It is recommended to use the Check constraint with NOT for Replication to ensure that the identification value given is within the current database.
RowGuidcol
Indicates that the new column is a global unique identifier column. A UniqueIdentifier column can only be assigned to each table as the RowGuidCol column. The ROWGUIDCOL attribute can only be assigned to the UNIQueIdentifier column. If the database compatibility level is less than or equal to 65, the ROWGUIDCOL keyword is invalid. For more information, see sp_dbcmptlevel.
The ROWGUIDCOL attribute does not enhance the uniqueness of the value stored in the column. This attribute does not automatically generate values for the new row inserted into the table. To generate a unique value for each column, or use the NewID function in the INSERT statement, or specify the NewID function as the default value of the column.
COLLATION_NAME
Specify the column sort rules. The sorting rule name can be either a Windows sort rule name or a SQL sorting rule name. Collation_name is only available for columns for data types of char, varchar, text, nchar, nvarchar, and ntext. If this parameter is not specified, if the data type of the column is user-defined, the column of the column is the sorting rule of the user-defined data type, otherwise the default sorting rule of the database.
For more information on Windows and SQL Sort Rules Names, see Collate.
Constraint
It is an optional keyword, which represents the start of Primary Key, NOT NULL, UNIQUE, Foreign Key, or Check Constrained. Constraints are special attributes for enforcing data integrity and can create indexes and their columns.
Constrain_name
Is the name of the constraint. The constraint must be unique in the database.
NULL | NOT NULL
Is the keyword that determines if a null value is allowed. In terms of strict sense, NULL is not constrained, but it can be specified using the same method as specifying NOT NULL.
PRIMARY Key
It is a constraint that is a given column or multi-column forced entity integrity by a unique index. You can only create a primary key constraint for each table. Unique
It is a constraint that provides entity integrity by a unique index for a given column or multiple columns. A table can have multiple UNIQUE constraints.
Clustered | nonclustered
It is a keyword that is expressed as a PRIMARY key or a Unique constraint to create a gathering or non-aggregation index. Primary Key Binding defaults to Clustered, UNIQUE constraint defaults to Nonclustered.
CLUSTERED can only be specified for one constraint in the CREATE TABLE statement. If the primary key constraint is specified while specifying clustered, the primary key will default to Nonclustered.
[With FillFactor = FillFactor]
Specifies the degree of fullness of each index page when SQL Server stores index data. The FillFactor specified by the user ranges from 1 to 100. If no FillFactor is specified, it is default to zero. When you create an index, the lower the value of FillFactor, the more space you can use by the new cable item without allocating the new space.
Foreign Key ... References
It is a constraint that provides reference integrity in the column. Each value in the Foreign Key Constraint Requirements exists in the reference column corresponding to the referenced table. The Foreign Key constraint can only be referenced in the column of the Primary Key or the Unique constraint or the columns within Unique Index in Unique Index in the reference table.
Ref_table
It is a table name referenced by the Foreign Key constraint.
(Ref_column [, ... n])
It is a column or multiple columns in the table referenced by the Foreign Key constraint.
On Delete {cascade | no action}
Specifies that the line taken by the row is deleted from the row in the table to be created and the row referenced by the row is deleted from the parent table. The default is set to No an action.
If you specify cascade, you will also remove the reference row from the reference table from the parent table. If no action is specified, SQL Server will generate an error and return the row of row in the roller.
For example, there is a reference relationship between the ORDERS table and the Customers table in the Northwind database. ORDERS.CUSTOMERID Foreign key references Customers.customerid primary key.
If you execute the DELETE statement for a certain row of the Customers table, specify the ORDERS.CUSTomerid action, SQL Server will check if there is a row or multi-line related to the deleted row. If there is a related line, the relevant rows in the Orders table will be deleted along with the referenced lines in the Customers table.
Conversely, if the no action is specified, if there is at least one line in the ORDERS table to delete the row to be deleted, SQL Server will generate an error and return the delete operation in the CUSTOMERS table.
On Update {cascade | no action}
Specifies the operation to be taken when the row is referenced in the table to be created, and updating the row referenced by the row in the parent table. The default is set to No an action.
If you specify Cascade, update the referenced line in the parent table, will also be updated in the reference table. If no an action is specified, SQL Server will generate an error and return the row update operation in the rollerfather table.
For example, there is a reference relationship between the ORDERS table and the Customers table in the Northwind database: ORDERS.CUSTOMERID Outgoing Customers.Customerid primary key. If you perform an UPDATE statement at a row of the Customers table, and for the ORDERS.CUSTomerid action, SQL Server will check if there is a row or multi-line associated with the updated line in the ORDERS table. If there is a relevant line, the relevant rows in the Orders table will be updated together with the reference lines in the Customers table.
Conversely, if the NO Action is specified, if there is at least one line in the ORDERS table to reference the Customers line, SQL Server will generate an error and roll back to the Update of the CUSTOMERS line.
CHECK
It is a constraint that can be entered into a column or multiple columns in a column or multiple columns.
NOT for Replication
It is a keyword for preventing CHECK constraints during the distribution process used in replication. When the table is a replication subscriber, please do not update the subscription table directly, and update the publishing table, then let the replication process send the data back to the subscription table. You can define the Check constraint on the subscription table to prevent users to modify the subscription list. However, if you do not use the NOT for Replication clause, the Check constraint also prevents the replication process from sending a modification from the publishing table to the subscription table. The NOT for Replication clause represents a modification of the user (not the replication process).
NOT for Replication CHECK constraints apply to the pre-recorded front image and postmark to prevent recording in the replication range or delete records from the replication range. All deletions and insertions will be checked; if the operation is within the replication range, the action will be refused.
If this constraint is used for the identifier column, the SQL Server will allow you to recalculate the seed value of the table identity column when copying the user update identity column.
Logical_expression
Is a logical expression that returns True or false.
Column
It is a column or multiple columns enclosed in parentheses, indicating that these columns are used in the constraint definition in the table constraint.
[ASC | DESC]
Specifies a column or multi-column sort order to add to the table constraint. The default is set to ASC.
n
It is a placeholder that represents the previous item to repeat N times.
Comment
Each database of SQL Server can store up to 2 billion tables, each table can have 1024 columns. The number of rows and total sizes are only limited by the available storage space. You can store up to 8,060 bytes per line. If you create a table with varchar, nvarchar or varbinary column, and the total number of bytes of the column exceeds 8,060 bytes, although this table can still be created, there is a warning message. If you try to insert more than 8,060 bytes of rows or to update the line to the total number of bytes, the error message will appear and the statement will fail.
The CREATE TABLE statement containing the SQL_VARIANT column can generate the following warning:
The Total Row Size (xx) for Table 'YY' Exceeds The Maximum Number of Bytes Per Row (8060). Rows That Exceed The Maximum Number of bytes Will NOTBE ADDED.
This warning appears because the maximum length of SQL_VARIANT can only be 8016 bytes. When a SQL_VARIANT column contains a value close to the maximum length, it can exceed the maximum size limit of the row.
Each table can have a maximum of 249 non-aggregated indexes and a gathering index. These include indexes generated by the Primary Key and UNIQUE constraints defined in the support table.
SQL Server does not force the default, Identity, RowGuidcol or column constraints in a specific order in the column definition.
Temporary tables
You can create a local and global temporary table. The local temporary table is only visible in the current session; global temporary table is visible in all sessions.
The name of the local temporary table has a number (#table_name), and the name of the global temporary table has two numbered characters (## Table_name).
SQL statement uses a CREATE TABLE statement to reference a temporary table for Table_name:
Create Table # MYTEMPTABLE (COLA INT Primary Key)
Insert Into # MYTEMPTABLE VALUES (1)
If the local temporary table is created by the stored procedure or by the application executed by multiple users, the SQL Server must be able to distinguish the table created by different users. To this end, SQL Server adds a digital suffix to the table name of each local temporary table. The temporary table stored in the SysObjects table of the Tempdb database consists of a full name consisting of a table name and system generated by the CREATE TABLE statement. In order to allow additional suffixes, the table name Table_name specified for the local temporary table cannot exceed 116 characters.
The temporary table will be automatically removed by the system when using the Drop Table statement explicitly remove the temporary table.
When the stored procedure is complete, the local temporary table created during the stored procedure is automatically removed. This table can be referenced by all nested stored procedures performed by the stored procedure of the creation table. But the process that calls the stored procedure created this table cannot reference this table. All other local temporary tables are automatically removed at the end of the current session. The global temporary table is automatically removed when the session of this table is created and other tasks stops. The association between tasks and tables is only held within the survival period of a single Transact-SQL statement. In other words, when the session of the global temporary table is created, the last reference will be automatically removed after the TRANSACT-SQL statement of this table is completed.
The local temporary table created in the stored procedure or trigger is different from the same name temporary table created before the stored procedure or trigger. If the query references the temporary table, there are two temporary tables of two as the same name, no define which table is resolved. The nested stored procedure can also create a temporary table that is the same as the presentation with the temporary table created by calling its stored procedure. All references to the table name during nested storage are interpreted as a table created by the nested process, for example:
Create Procedure Test2
AS
Create Table #t (X Int Primary Key)
INSERT INTO #T Values (2)
SELECT TEST2COL = x from #t
Go
CREATE Procedure Test1
AS
Create Table #t (X Int Primary Key)
INSERT INTO #T Values (1)
SELECT TEST1COL = x from #t
Exec test2
Go
Create Table #t (X Int Primary Key)
INSERT INTO #T Values (99)
Go
Exec Test1
Go
The following is the result set:
(1 row (s) affected)
TEST1COL
-----------
1
(1 row (s) affected)
Test2col
-----------
2
When creating a local or global temporary table, the CREATE TABLE syntax supports all other constraints other than the Foreign Key constraint. If the Foreign Key constraint is specified in the temporary table, the statement will return the warning message, indicating that this constraint has been ignored, and the table will still be created, but does not have a Foreign Key constraint. A temporary table cannot be referenced in the Foreign Key constraint.
Consider using table variables without using a temporary table. The temporary table is useful when you need to use a table value when you need to create an index in the temporary table, or when multiple stored procedures or functions need to be used. Typically, the table variable provides more efficient query processing. See Table for more information. PRIMARY Key Constraint
A table can only contain a primary key constraint. The index generated by the Primary Key constraint cannot make the non-aggregated index in the table more than 249, and the aggregation index exceeds 1. If you do not specify Clustered or NonClustered in the Primary Key Constraint, and not specify the aggregation index for the UNIQUE constraint, the CLUSTERED will be used to constrain the Primary Key. All columns defined in the primary key constraint must be defined as NOT NULL. If you are not specified as empty, all columns of the PRIMARY Key constraint will be set to NOT NULL.
UNIQUE constraint
If you do not specify a Clustered or NonClustered or Nonclustered in the UNIQUE constraint, the default is nonclustered. Each UNIQUE constraint generates an index. The index generated by the UNIQUE constraint cannot make the non-aggregated index in the table more than 249, and the aggregation index exceeds 1.
Foreign Key Constraint
If you enter a non NULL value in the column of the Foreign Key constraint, this value must be existing in the columns being referenced, otherwise the error message that violates foreign key constraints will be returned. The Foreign Key Constraint is applied in the previous columns unless the source column is specified. The Foreign Key constraint can only be referenced in the table in the same database on the same server. The reference integrity between the database must be implemented by the trigger. See Create Trigger for more information. Foreign Key can reference other columns in the same table (self-reference). The REFERENCES clause of the column Foreign Key constraint can only list a reference column, and the column must have the same data type as columns of defined constraints. The number of reference columns in the References clause of the table-level Foreign Key constraint must be the same as the columns in the constraint list. The data type of each reference column must also be the same as the corresponding column of columns in the list. If the column of the TimeStamp type is a portion of the foreign key or the reference key, Cascade cannot be specified. CASCADE and NO ACTION can be used in tables with reference relationships between each other. If SQL Server encounters no an action, the execution statement is terminated and the CASCADE operation is rolled back. When the DELETE statement causes the Cascade and NO Action combination operation, all CASCADE operations will be performed before SQL Server checks the No Action operation. A table can contain 253 Foreign Key constraints. For the temporary table, it does not force the Foreign Key constraint. Each table can reference up to 253 different tables in its Foreign Key constraint. The Foreign Key constraint can only reference the columns in the primary key or Unique constraints in the pRIMARY key or the reference table in the pRIMARY key or the columns in Unique INDEX.
Default definition
Each column can only have a default definition. Default definitions can contain constant values, functions, SQL-92 Niladic functions or null. The following table shows the NILADIC function and the default value returned when the INSERT statement is executed.
The value of the value returned by the SQL-92 niladic function. Current_timeStamp current date and time. Current_user executes the username of the insert operation. Session_user executes the username of the insert operation. System_user performs usernames inserted. USER performs usernames inserted. The constant_expression in the default definition cannot reference other columns in the table, or other tables, views, or stored procedures cannot be referenced. You cannot create a default definition on columns of data types of TimeStamp or columns with Identity properties. If the user defines the data type bind to the default object, you cannot create a default definition on the column of the user-defined data type. Check constraint
Columns can have any multiple check constraints, and multiple logical expressions that can be combined with both and or OR can be included in the constraint. The plurality of Check constraints on the column are verified in the creation order. The search criteria must be a Boolean expression and other tables cannot be referenced. The CHECK constraint can only be referenced by the constrained column, and the table-level Check constraint can only reference the columns in the same table. When the INSERT and DELETE statements are executed, check constraints and rules have the same data verification. When there is a rule and one or more Check constraints, all restrictions will be verified.
Other constraint information
The index created for constraints cannot be removed with a Drop Index statement; the constraint must be removed with the ALTER TABLE statement. You can use the DBCC DBREINDEX statement to reconstruct the index created by the constraint. The name of the constraint must meet the identifier rule, but its name is not a #. If you do not provide constraint_name, use the name of the system generated. The constraint name will appear in all error messages related to violation constraints. When INSERT, UPDATE, or DELETE statement violates constraints, the statement is terminated. However, the transaction will continue (if this statement is an integral part of the explicit transaction). You can use the Rollback Transaction statement in the transaction definition by checking the system function @@ Error.
If a table has a Foreign Key or Check Constraints and trigger, check the constraints before the trigger is executed.
To get reports about the table and its list, use sp_help or sp_helpconstraint. To rename the table, use sp_rename. To get reports related to table-related views and stored procedures, use sp_depends.
Typically, when the space is allocated for the table and index, each time a expanded panel is an increment unit. When a table or index is created, first assign a page from the mixed extended panel to it until it has enough pages full of unified expansion panels. When there is enough page to fill the unified expansion panel, once the currently assigned extension panel fills, another extended panel will be assigned. To obtain a report for the amount of space that is assigned and occupied by the table, perform sp_spaceused.
Air rules in the table definition
The column is to determine if the column is allowed to be used as its data in this column. NULL is not zero or blank: it indicates that no content is input, or provides an explicit NULL value, usually, the value is unknown or not applicable.
Database or session settings affect and may alternate the data type in the definition of the column definition when creating or changing the table with the Create Table or ALTER TABLE statement. It is recommended that the column is always defined as NULL or NOT NULL of non-columns. If the user-defined data type is used, it is recommended to allow the column to use this data type by default to air.
When there is no explicit designation, the column is empty, follow the rules:
If the column defines the data type:
SQL Server is specified for emptiness when creating data types. Use sp_help to get the default emptiness of the data type. If the column defines the data type provided by the system: If there is only one option provided by the system, you will use this option. The TimeSTAMP data type can only be defined as NOT NULL. If the setting of sp_dbcmptlevel is 65 or less, the column is not explicitly defined NULL or NOT NULL, then the Bit data type defaults to NOT NULL. For more information, see sp_dbcmptlevel. If there is any session set to ON (open with a set statement), then if ANSI_NULL_DFLT_ON is ON, null is assigned. If ANSI_NULL_DFLT_OFF is ON, NOT NULL is assigned. If any database settings are configured (change with sp_dboption), then if the ANSI NULL Default is true, null assigns NULL. If the ANSI NULL DEFAULT is False, NOT NULL is assigned. When the two ANSI_NULL_DFLT options are set, and the database is set to the default value (ANSI NULL DEFAULT is false), the default setting of SQL Server will assign NOT NULL. If the column is a computational column, it is always automatically determined by SQL Server. Use the ColumnProperty function (allowsnull property) to find emptyness of this column.
Note By default, both the SQL Server ODBC driver and the Microsoft OLE DB provider for SQL Server set ANSI_NULL_DFLT_ON to ON. ODBC and OLE DB users can configure this setting in the ODBC data source, or configure this setting through the connection feature or attribute set by the application.
Authority
CREATE TABLE Permissions The default grant DB_OWNER and DB_DDLADMIN fixed database role members. DB_OWNER Fixed Database Role Members and Sysadmin Fixed Server Role Members You can transfer Create Table permissions to other users.
Example
A. Constraints with Primary Key
The following example shows a column definition of the primary key constraint with the aggregated index in the Jobs table of the sample database PUBS; this example is provided by the system.
Job_id Smallint
PRIMARY Key Clustered
The following example shows how to provide names for the Primary Key constraint. This constraint is used in the EMP_ID column in the EMPLOYEE table. This list is based on the user-defined data type.
EMP_ID EMPID
ConsTRAINT PK_EMP_ID PRIMARY Key Nonclustered
B. Constraints with Foreign Key
The Foreign Key constraint is used to reference other tables. The Foreign Key can be a single column key or multiple columns. The following example shows the single-column Foreign Key constraint that references the JOBS table on the Employee table. For single-column Foreign Key constraints, only the References clause is required.
Job_ID Smallint Not NULL
DEFAULT 1
References jobs (JOB_ID)
You can also explicitly use the Foreign Key clause and review the column characteristics. Note that the column names in these two tables do not have to be the same.
Foreign Key (Job_ID) References Jobs (JOB_ID)
Multi-column key constraints are created as table constraints. In the PUBS database, the Sales table contains multiple columns Primary Key. The following example shows how to reference this button from other tables (optional explicit constraint name). ConsTRAINT FK_SALES_BACKORDER FOREIGN Key (STOR_ID, ORD_NUM, TITLE_ID)
References sales (stor_id, order_num, title_id)
C. Constraints with UNIQUE
Unique constraints for mandatory uniqueness of non-main key columns. The primary key constraint column automatically contains uniqueness restrictions; however, the UniQue constraint allows an empty value. The following example shows the column named Pseudonym in the table authors. This column is forbal that the author must be unique.
Pseudonym varchar (30) NULL
Unique nonclustered
The following example shows the unique constraint created in the Stor_Name column and the City column in the Stor_ID, and the store in the same city should not be the same as the store.
ConsTRAINT U_STORE UNIQUE NONCLUSTERED (STOR_NAME, CITY)
D. Definition using default
When using the INSERT and UPDATE statements, the default value will provide a value if the value is provided. In the PUBS database, many Default definitions are used to ensure valid data or placeholders.
On the Jobs table, when there is no explicit input of the actual description information, the default string will provide the description information (column job_desc).
Default 'New Position - Title Not Formalized Yet'
In the Employee table, employees can be employed in subsidiaries or parent. If no company information is explicitly supplied, you can enter a parent company (note that you can nested in the table definition, as shown below).
DEFAULT ('9952')
/ * By Default The Parent Company Publisher Is The Company
TO whom Each Employee Reports. * /
In addition to constants, the default definition can also contain functions. Use the following example to get the current date of the input:
Default (GetDate ())
The NILADIC function can also improve the integrity of the data. To track users inserted, use the NILADIC function USER (Niladic function is not using parentheses):
Default User
E. Constraints with Check
The following example shows the limitations of the value of the Min_LVL column and the Max_LVL column input to the JOBS table. These two constraints are not named:
Check (min_lvl> = 10)
versus
Check (MAX_LVL <= 250)
The following example shows a naming constraint that has a mode restricted character data in the EMP_ID column input to the EMPLOYEE table.
ConsTRAINT CK_EMP_ID CHECK (EMP_ID LIKE
'[A-Z] [A-Z] [1-9] [0-9] [0-9] [FM]' OR
EMP_ID LIKE '[A-Z] - [A-Z] [1-9] [0-9] [0-9] [0-9] [fm]')
The following example specifies that the PUB_ID must be in a specific list or in accordance with a given mode. This constraint is used in the PUB_ID column in the Publishers table.
Check (Pub_ID in ('1389,' 0736 ',' 0877 ',' 1622 ',' 1756 ') or Pub_id Like '99 [0-9] [0-9]')
F. Complete table definition
The following example shows a full table definition of the three tables (JOBS, Employee and Publishers) created in the PUBS database, which contains all constraint definitions.
/ * ************************************************* ****** * /
CREATE TABLE JOBS
(
Job_id Smallint
Identity (1, 1)
PRIMARY Key Clustered,
Job_Desc varchar (50) Not null
Default 'New Position - Title Not Formalized Yet',
Min_lvl tinyint not null
Check (min_lvl> = 10),
Max_lvl tinyint not null
Check (MAX_LVL <= 250)
)
/ * ******************************************************************** **** * /
CREATE TABLE EMPLOYEE
(
EMP_ID EMPID
ConsTRAINT PK_EMP_ID PRIMARY Key Nonclustered
ConsTRAINT CK_EMP_ID CHECK (EMP_ID LIKE
'[A-Z] [A-Z] [1-9] [0-9] [0-9] [FM]' OR
EMP_ID LIKE '[A-Z] - [A-Z] [1-9] [0-9] [0-9] [fm]'),
/ * Each Employee ID CONSISTS OF Three Characters That
REPRESENT The Employee's Initials, Followed by a Five
Digit Number Ranging from 10000 THROUGH 99999 and THEN THE
Employee's Gnder (M or F). A (HYPHEN) - IS ACCEPTABLE
For the middle initial. * /
FName Varchar (20) Not Null,
Minit char (1) NULL,
Lname varchar (30) Not null,
Job_ID Smallint Not NULL
DEFAULT 1
/ * Entry Job_id for new hires. * /
References jobs (JOB_ID),
Job_lvl Tinyint
DEFAULT 10,
/ * Entry Job_LVL for new hires. * /
Pub_id char (4) Not null
DEFAULT ('9952')
References Publishers (Pub_ID),
/ * By Default, THE Parent Company Publisher Is The Company
TO whom Each Employee Reports. * /
HIRE_DATE DATETIME NOT NULL
Default (GetDate ())
/ * By default, the current system date is entered. * /
/ * **************** Publishers Table ******************* * /
Create Table Publishers
(
Pub_id char (4) Not null
ConsTRAINT UPKCL_PUBIND Primary Key Clustered
Check (Pub_ID in ('1389', '0736', '0877', '1622', '1756')
Or Pub_id Like '99 [0-9] [0-9] '),
Pub_name varchar (40) NULL,
City varchar (20) NULL,
State Char (2) null,
Country varchar (30) null
Default ('USA')
)
G. Use the UNIQUEIDENTIFIER data type in the column
The next example creates a table containing the UNIQueIdentifier column. This table uses the Primary Key constraint to ensure that the user does not insert a duplicate value in the table, and the NewID () function is used in the DEFAULT constraint for the new row.
CREATE TABLE GLOBALLY_UNIQUE_DATA
(GUID UNIQUEIDENTIFIER
Constraint Guid_Default
DEFAULT newid (),
EMPLOYEE_NAME VARCHAR (60),
ConsTRAINT GUID_PK PRIMARY Key (GUID)
)
H. Use expressions to compute columns
The following example shows how to calculate the Myavg calculation column using an expression ((Low High) / 2).
Create Table MyTable
(
Low int,
HIGH INT,
Myavg As (Low High) / 2
)
I. Use the user_name function to the compute column
The following example uses the user_name function in myUser_name column.
Create Table MyLogintable
(
Date_in datetime,
User_id Int,
myuser_name as user_name ()
)
J. Using NOT for Replication
The following example shows how to use the Identity property in the subscribed table. This table contains Check constraints to ensure that the SaleID value generated by this system will not grow into the scope of the replication publisher assignment.
Create Table Sales
(Saleid Int Id Idness (100000, 1) NOT for Replication,
Check not for replication (SaleId <= 19999),
SalesRegion char (2),
ConsTRAINT ID_PK PRIMARY Key (SaleID)
)
