This is the 16th day of my participation in September Challenge.

Chapter 17 SQL > CREATE TABLE

Unique field constraint

Unique field constraint Imposes a unique value constraint on a combination of values of multiple fields. It has the following syntax:

CONSTRAINT uname UNIQUE (f1,f2)
Copy the code

This constraint specifies that the combination of values for fields F1 and F2 must always be unique, even though the values of the two fields themselves may not be unique. You can specify one, two, or more fields for this constraint.

All fields specified in this constraint must be defined in the field definition. If the field specified in this constraint does not appear in the field definition, an SQLCODE-86 error is generated. The specified field should be defined as non-empty. Any specified field should not be defined as unique, as this would make it meaningless to specify this constraint.

Fields can be specified in any order. Field order Specifies the field order defined by the corresponding index. Duplicate field names are allowed. Although you can specify a single field name in a unique field constraint, this is functionally the same as specifying a unique data constraint for that field. The single-field constraint does provide the constraint name for future use.

Multiple unique field constraint statements can be specified in the table definition. Constraint statements can be specified anywhere in the field definition; By convention, they are usually placed at the end of the list of defined fields.

The name of the constraint

The Constraint keyword and unique field Constraint names are optional. The following are functionally equivalent:

CONSTRAINT myuniquefields UNIQUE (name,dateofbirth)
UNIQUE (name,dateofbirth)
Copy the code

The constraint name uniquely identifies the constraint and is also used to derive the corresponding index name. It is recommended to specify the constraint name. This constraint name is required when a constraint is removed from a TABLE definition using the ALTER TABLE command. The constraint name can be any valid identifier; If specified as a delimiter, the constraint name can contain “.”, “^”, “,”, “->” characters.

ALTER TABLE cannot delete columns listed in constraint UNIQUE. Attempting to do so generates a SQLCODE-322 error.

RowID record identifier

In SQL, each record is identified by a unique integer value, called RowID. In SQL, you do not need to specify the RowID field. When you create a table and specify the required data fields, the RowID field is automatically created. This RowID is used internally, but is not mapped to class attributes. By default, its presence is visible only when a class is projected onto an SQL table. In the projected SQL table, an additional RowID field appears. By default, this field is named “ID” and assigned to column 1.

%PUBLICROWID

By default, RowID is hidden and private. Specify the %PUBLICROWID keyword so that RowID is not hidden and exposed. If the %PUBLICROWID keyword is specified, the class corresponding to the table is defined using “not SqlRowIdPrivate”. This optional keyword can be specified anywhere in the comma-separated list of table elements. ALTER TABLE cannot be used to specify %PUBLICROWID.

If RowID is public:

• RowIDValue throughSELECT *Display.
• RowIDCan be used as a foreign key reference.
• If no primary key is defined,RowIDWill be treated as having a constraint nameRowIDField_As_PKeyImplicit primary key constraint of
• If you do not specify a field name to copy, you cannot use the table to copy data to a duplicate table.

Bitmap extended index

When a TABLE is created using CREATE TABLE, by default, IRIS automatically defines the bitmap range index for the corresponding class. SQL MapName = %%DDLBEIndex;

Index DDLBEIndex [ Extent, SqlName = "%%DDLBEIndex", Type = bitmap ];
Copy the code

This bitmap range index is not created under any of the following circumstances:

• This table is defined as a temporary table.
• This table defines an explicitIDKEYThe index.
• This table contains a defined identity field that does notMINVAL=1.
• $SYSTEM.SQL.Util.SetOption()methodsDDLDefineBitmapExtentThe option is set to 0 to override the system-wide defaults. To determine the current Settings, call$SYSTEM.SQL.CurrentSettings()Method, which displaysa Do classes created by a DDL CREATE TABLE statement define a bitmap extent index.

If the CREATE BITMAPEXTENT INDEX is called on the table that automatically defines the BITMAPEXTENT INDEX after the bitmap INDEX is created, the previously defined BITMAPEXTENT INDEX is renamed to the name specified in the CREATE BITMAPEXTENT INDEX statement.

For DDL operations that automatically delete existing bitmap range indexes, see ALTER TABLE.

IDENTITY IDENTITY field

SQL automatically creates a RowID field for each table that contains a system-generated integer as the unique record ID. The optional IDENTITY keyword allows you to define a named field with the same attributes as the RowID record ID field. The IDENTITY field acts as a single-field IDKEY index and its value is a unique integer generated by the system.

Defining an identity field prevents primary keys from being defined as IDkeys.

Like any system-generated ID field, the IDENTITY field has the following characteristics:

• Only one field per table can be defined asIDENTITYField.

Attempting to define multiple IDENTITY fields for a table generates a SQLCODE -308 error.

• IDENTITYThe data type of the field must be an integer data type.

If no data type is specified, the data type is automatically defined as BIGINT. You can specify any integer data type, such as INTEGER or SMALLINT; It is recommended to use BIGINT to match the RowID data type. Accepts any specified field constraints, such as NOT NULL or UNIQUE, but ignores them.

• Data values are generated by the system.

They consist of unique non-zero positive integers.

• By default,IDENTITYField data values cannot be specified by users.

By default, INSERT statements do not and cannot specify an IDENTITY field value. Attempting to do so produces a SQLCODE -111 error. To determine whether or not you can specify the IDENTITY field value, call $SYSTEM. SQL. Util. GetOption (” IdentityInsert “) approach. The default value is 0. To change the current process of this setting, please call $SYSTEM. SQL. Util. SetOption () method, as shown in the following: set the status = $SYSTEM. SQL. Util. SetOption (” IdentityInsert “, 1,. Oldval). You can also specify %CLASSPARAMETER ALLOWIDENTITYINSERT=1 in the table definition. Specifying ALLOWIDENTITYINSERT=1 overrides any Settings applied using SetOption(” IdentityInsert “).

• Not in theUPDATEStatement to modify the identity field data value. Attempting to do so generatesSQLCODE-107Error.
• The primary key on the identity field is automatically projected toODBCandJDBC. ifCREATE TABLEorALTER TABLEIf a statement defines a primary key or unique constraint on an identifying field or a set of columns containing an identifying field, the constraint definition is ignored and no corresponding primary key or unique index definition is created.
• SELECT*Statement does return the identity field of the table.

After an INSERT, UPDATE, or DELETE operation, you can use the LAST_IDENTITY function to return the value of the identity field of the recently modified record. If the identity field is not defined, LAST_IDENTITY returns the RowID value of the recently modified record.

The following embedded SQL program creates a table with identity fields and then inserts a record into the table to generate identity field values:

/// d ##class(PHA.TEST.SQLCommand).CreateTable5()
ClassMethod CreateTable5(a)
{
	d $SYSTEM.Security.Login("_SYSTEM"."SYS")
	&sql(
		CREATE TABLE Employee 
		(
			EmpNum INT NOT NULL,
			MyID   IDENTITY NOT NULL,
			Name   CHAR(30) NOT NULL,
			CONSTRAINT EMPLOYEEPK PRIMARY KEY (EmpNum)))if SQLCODE '= 0{ w ! .Table creation error:,SQLCODE 
	} else{ w ! ."Table created" 
	}
	&sql(
		INSERT INTO Employee 
		( EmpNum,Name ) 
		SELECT ID,Name FROM SQLUser.Person WHERE Age >= '25'
	)
	if SQLCODE '= 0 { w ! ," Error is: ",SQLCODE} else {w! ," Insert record into table "}}Copy the code

In this case, the primary key (EmpNum) is taken from the ID field of another table. Therefore, EmpNum values are unique integers, but (because of the WHERE clause) they may contain Spaces in their sequence. The identity field myID assigns each record a unique sequential integer visible to the user.

ROWERSION, SERIAL, and AUTO_INCREMENT fields

SQL provides three types of system-generated integer counter fields. All three data types are subclasses of the class that extends % Library.bigInt data type.

The following CREATE TABLE example defines these fields:

CREATE TABLE MyStudents (
   Name VARCHAR(16) NOT NULL,
   DOB DATE,
   AutoInc BIGINT AUTO_INCREMENT,
   Counter SERIAL,
   RowVer ROWVERSION
   )
Copy the code

Specify ROWVERSION and SERIAL keywords instead of explicit data types. So the following is a valid field definition syntax :MySerial SERIAL or MyRowVer ROWVERSION.

The AUTO_INCREMENT keyword is specified after the explicit data type. You can also define an AUTO_INCREMENT field using the % library. AutoIncrement data type. Therefore, the following is a valid field definition syntax: MyAutoInc %AutoIncrement, MyAutoInc %AutoIncrement AUTO_INCREMENT, or MyAutoInc INTEGER AUTO_INCREMENT.

Define a primary key

Defining a primary key is optional. When you define a table, IRIS automatically creates a generated Field, the RowID Field(default name “ID”), that acts as a unique row identifier. As each record is added to the table, IRIS assigns a unique, unmodifiable positive integer to the RECORD’s RowID field. Optionally, you can define a primary key that also serves as a unique row identifier. Primary keys allow the user to define row identifiers that are meaningful to the application. For example, the primary key could be an employee ID field, a Social Security number, a patient record ID field, or an inventory inventory number.

You can use the PRIMARY KEY clause to explicitly define a field (or group of fields) as a PRIMARY record identifier. There are three syntactic forms for defining a primary key:

CREATE TABLE MyTable (Field1 INT PRIMARY KEY, Field2 INT)

CREATE TABLE MyTable (Field1 INT, Field2 INT.PRIMARY KEY (Field1))

CREATE TABLE MyTable (Field1 INT, Field2 INT.CONSTRAINT MyTablePK PRIMARY KEY (Field1))
Copy the code

The first syntax defines a field as a primary key; By specifying it as the primary key, the field is, by definition, unique and not empty. The second and third grammars can be used for single-field primary keys, but allow primary keys containing multiple fields. For example, primary keys (Field1, Field2). If you specify a single field, it is, by definition, unique and not empty. If you specify a comma-separated list of fields, each field is defined as non-null, but can contain duplicate values as long as the combination of field values is unique. The third syntax allows the primary key to be explicitly named; The first two syntactic forms generate a primary key name, as follows: Table name “PKEY” constraint COUNT INTEGER.

Primary keys only accept unique values, not NULL. Primary key index attributes are not automatically defined on demand; However, it is actually required because you cannot archive or save null values for primary key fields. . The collation type for the primary key is specified in the definition of the field itself.

Serves as the primary key of IDKEY

By default, the primary key is not the only IDKEY index. In many cases, this is preferable because it enables you to update the primary key value, set the collation type for the primary key, and so on. In some cases, it is best to define the primary key as an IDKEY index. Note that this imposes IDKEY restrictions on future use of primary keys.

If you add a primary key constraint to an existing field, that field may also be automatically defined as an IDKEY index. This depends on whether the data exists and the configuration Settings set up in one of the following ways:

• SQL SET OPTION PKEY_IS_IDKEYStatements.
• System-wide$SYSTEM.SQL.Util.SetOption()Method configuration optionsDDLPKeyNotIDKey. To determine the current Settings, call$SYSTEM.SQL.CurrentSettings(), it shows through the DDL instead ofIDThe primary key created by the key; The default value is 1.
• Go to the UMP and choose System Management, Configuration, SQL and Object Settings, SQL.

View the current Settings for defining primary keys as ID keys for tables created through DDL.

• If the check box is not selected (by default)Primary KeyDoes not become an IDKEY index in the class definition.

The efficiency of accessing records with non-IDkey primary keys is significantly reduced; However, the primary key value of this type can be modified.

• If the check box is selected, when passDDLThe specifiedPrimary KeyConstraint, it automatically becomes part of the class definitionIDKEYThe index.

With this option, data access is more efficient, but the primary key value, once set, can never be changed.

However, if the IDENTITY field is defined in the table, the primary key cannot be defined as IDKEY, even if one of these configuration Settings is used to establish the primary key as IDKEY.

IRIS enables attributes (fields) that are part of the IDKEY index to become SqlComputed. For example, a parent reference field. Property must be a triggered computed field. The IDKEY attribute, defined as SqlComputed, is computed only when a new Object or INSERT operation is saved for the first time. UPDATE calculations are not supported because fields that are part of the IDKEY index cannot be updated.

There is no primary key

In most cases, the primary key should be explicitly defined. However, if the primary key is not specified, IRIS will try to use another field as the primary key for the ODBC/JDBC projection based on the following rules:

1. If there is on a single fieldIDKEYIndex, willIDKEYThe field report isSQLPrimaryKeyField.
2. Otherwise, if usedSqlRowIdPrivate=0(Default) defines the class, then willRowIDThe field report isSQLPrimaryKeyField.
3. Otherwise, if there isIDKEYIndex, willIDKEYThe field report isSQLPrimaryKeyField.
4. Otherwise, do not reportSQLPrimaryKey.

More than one primary key

Only one primary key can be defined. By default, IRIS rejects attempts to define a primary key when it already exists, or rejects defining the same primary key twice and issues a SQLCODE-307 error. Even if the second definition of the primary key is the same as the first, an SQLCODE-307 error is issued. To determine the current configuration, please call $SYSTEM. SQL. CurrentSettings (), the function display when there is key allows through DDL to create primary key Settings. The default is 0(no), which is the recommended configuration setting. If this option is set to 1(yes), IRIS will remove the existing primary key constraint and establish the last specified primary key as the primary key of the table.

This option (and other similar create, change, and delete options) can be set system-wide by selecting the ignore redundant DDL statement check box in the administrative portal, system administration, configuration, SQL, and object Settings.

For example, the following CREATE TABLE statement:

CREATE TABLE MyTable (f1 VARCHAR(16), 
CONSTRAINT MyTablePK PRIMARY KEY (f1))
Copy the code

Create a primary key if one does not exist. ALTER TABLE ALTER TABLE

ALTER TABLE MyTable ADD CONSTRAINT MyTablePK PRIMARY KEY (f1)
Copy the code

SQLCODE-307 error generated.