### Exposed 0.56.0 Help

# Working with tables

In this topic, we will explain how to define, configure, and create tables. All examples use the H2 database to generate SQL.

## Table Types

### Table

In Exposed, the `Table` class is the core abstraction for defining database tables. This class provides methods to define various column types, constraints, and other table-specific properties.

`Table` is located in the org.jetbrains.exposed.sql package of the exposed-core module.

### IdTable

Apart from the core `Table` class, Exposed provides the base `IdTable` class and its subclasses through the DAO API.

The `IdTable` class extends `Table` and is designed to simplify the definition of tables that use a standard `id` column as the primary key. These tables can be declared without explicitly including the `id` column, as IDs of the appropriate type are automatically generated when creating new table rows.

`IdTable` and its subclasses are located in the org.jetbrains.exposed.dao.id package of the exposed-core module.

For more information on `IdTable` types, see DAO Table Types.

## Defining tables

A database table is represented by an object inherited from a table class.

object Cities : Table() {}

Exposed supports a variety of column types, including `integer`, `varchar`, `bool`, and more. Each column is defined by calling the appropriate method on the `Table` object.

The following example defines a table with an auto-incrementing integer `id` column and a string `name` column:

import org.jetbrains.exposed.sql.Table object Cities : Table("cities") { val id = integer("id").autoIncrement() val name = varchar("name", 50) }
import org.jetbrains.exposed.dao.id.IntIdTable object Cities : IntIdTable("cities") { val name = varchar("name", 50) }

The `IntIdTable` class automatically generates an auto-incrementing integer `id` column, which serves as the primary key for the table. Therefore, there is no need to explicitly define the `id` column in the `Cities` object.

Creating the table with the above definition will result in the following SQL equivalent:

CREATE TABLE IF NOT EXISTS CITIES (ID INT AUTO_INCREMENT NOT NULL, "name" VARCHAR(50) NOT NULL)
CREATE TABLE IF NOT EXISTS CITIES (ID INT AUTO_INCREMENT PRIMARY KEY, "name" VARCHAR(50) NOT NULL)

### Configuring a custom table name

By default, Exposed generates the table name from the full class name.

If the object name contains a 'Table' suffix, Exposed will omit the suffix from the generated table name:

object CitiesTable : Table() { val id = integer("id") val name = varchar("name", 50) }
CREATE TABLE IF NOT EXISTS CITIES (ID INT NOT NULL, "name" VARCHAR(50) NOT NULL)

To configure a custom name for a table, which will be used in actual SQL queries, pass it to the `name` parameter of the `Table()` constructor.

object Cities : Table("all_cities") { val id = integer("id") val name = varchar("name", 50) }
CREATE TABLE IF NOT EXISTS ALL_CITIES (ID INT NOT NULL, "name" VARCHAR(50) NOT NULL)

Some databases, like H2, fold unquoted identifiers to upper case. To keep table name case-sensitivity, manually quote the provided argument:

object Cities : Table("\"all_cities\"") { val id = integer("id") val name = varchar("name", 50) }
CREATE TABLE IF NOT EXISTS "all_cities" (ID INT NOT NULL, "name" VARCHAR(50) NOT NULL)

Depending on what DBMS you use, the types of columns could be different in actual SQL queries.

## Constraints

### Nullable

The `NOT NULL` SQL constraint restricts the column to accept the `null` value. By default, Exposed applies this constraint to all the columns. To allow the column to be nullable, apply the `nullable()` method to a definition of an appropriate column.

For example, to make the population column `nullable`, use the following code:

// SQL: POPULATION INT NULL val population: Column<Int?> = integer("population").nullable()

### Default

The `DEFAULT` SQL constraint provides the default value for the column. Exposed supports three methods for configuring default values:

- `default(defaultValue: T)` accepts a value with a type of the column.

- `defaultExpression(defaultValue: Expression<T>)` accepts an expression.

- `clientDefault(defaultValue: () -> T)` accepts a function.

For example, to configure the default value for the `name` column, use the following code:

// SQL: "NAME" VARCHAR(50) DEFAULT 'Unknown' val name: Column<String> = varchar("name", 50).default("Unknown")

Exposed also supports marking a column as `databaseGenerated` if the default value of the column is not known at the time of table creation and/or if it depends on other columns. It makes it possible to omit setting a value for the column when inserting a new record, without getting an error. The value for the column can be set by creating a TRIGGER or with a DEFAULT clause, for example.

For example:

val name: Column<String> = varchar("name", 50).databaseGenerated()

### Index

The `INDEX` SQL constraint makes traversing through tables quicker. Exposed supports the `index()` method. It has six parameters, most of which are optional:

- `val customIndexName: String? = null` is a custom name for the index, which will be used in actual SQL queries.

- `val unique: Boolean` defines whether the index is unique or not.

- `val columns: List<Column<*>>` defines a column set.

- `val functions: List<ExpressionWithColumnType<*>>? = null` defines functional key parts.

- `val indexType: String? = null` is a custom type. Can be `"BTREE"` or `"HASH"`.

- `val filterCondition: (SqlExpressionBuilder.() -> Op<Boolean>)? = null` defines a condition used to create a partial index.

The simplest way to create an index is to use an extension function directly on a column. For example, to apply a non-unique `INDEX` constraint to the `name` column, use the following code:

val name = varchar("name", 50).index()

If the parameter `customIndexName` is not set, the name of the index is determined by the table and column names.

Also, Exposed supports complex indexes. If you have a frequent query for two columns, Exposed can perform it more efficiently. It creates a tree from the first column with the references to the second one. For example, to create a non-unique complex index on the `name` and `population` columns, paste the following code:

val indexName = index("indexName", false, *arrayOf(name, population)) // or inside an init block within the table object init { index("indexName", isUnique = false, name, population) }

Exposed also supports creating an index with a custom type. For example, to retrieve data from the `name` column faster with a hash function for traversing, use the following code:

val indexName = index("indexName", false, *arrayOf(name), indexType = "HASH")

Some databases support functional key parts that index expressions instead of columns directly:

init { index(functions = listOf(name.lowerCase(), address.substring(1, 5))) uniqueIndex( columns = arrayOf(name), functions = listOf(Coalesce(address, stringLiteral("*"))) ) }

Operator expressions, like `plus()`, are also accepted by the `functions` parameter.

Some databases support creating a partial index by defining a filter expression to improve querying performance. The created index will only contain entries for the table rows that match this predicate:

init { index(columns = arrayOf(name, flag)) { flag eq true } index( columns = arrayOf( name, population ) ) { (name like "A%") and (population greaterEq 10) } }

Once a table has been created, the list of its indices can be accessed using the property `Table.indices`. Table indices are represented by the data class `Index`, so its properties can be checked in the following manner, for example:

Table.indices.map { it.indexName to it.createStatement().first() }

An instance of the `Index` data class can be created directly using its public constructor, for the purpose of evaluating or using create/modify/drop statements, for example. Doing so will not add the instance to an existing table's list of indices in the way that using `index()` would. Also, if an instance is created with arguments provided to the `functions` parameter, a `functionsTable` argument must also be provided.

### Unique

The `UNIQUE` SQL constraint restricts duplicates within this column. Exposed supports the `uniqueIndex()` method which creates a unique index for the column. This method is the composition of `UNIQUE` and `INDEX` constraint, the quicker modification of `UNIQUE` constraint.

For example, to apply `UNIQUE` and `INDEX` constraint to the `name` column, use the following code:

val name = varchar("name", 50).uniqueIndex()

### Primary Key

The `PRIMARY KEY` SQL constraint applied to a column means each value in that column identifies the row. This constraint is the composition of `NOT NULL` and `UNIQUE` constraints. To change the column set, add columns, or change the primary key name to a custom one, override this field of the table class.

For example, to define the `name` column as the primary key, use the following code. The "Cities_name" string will be used as the constraint name in the actual SQL query, if provided; otherwise a name will be generated based on the table's name.

override val primaryKey = PrimaryKey(name, name = "Cities_name")
CONSTRAINT Cities_name PRIMARY KEY ("name")

It is also possible to define a primary key on a table using multiple columns:

override val primaryKey = PrimaryKey(id, name)
CONSTRAINT pk_Cities PRIMARY KEY (ID, "name")

Except for `CompositeIdTable`, each available class in Exposed that inherits from `IdTable` has the `primaryKey` field automatically defined. For example, the `IntIdTable` by default has an auto-incrementing integer column, `id`, which is defined as the primary key.

An `IdTable` that requires a primary key with multiple columns can be defined using `CompositeIdTable`. In this case, each column that is a component of the table's `id` should be identified by `entityId()`:

object Towns : CompositeIdTable("towns") { val areaCode = integer("area_code").autoIncrement().entityId() val latitude = decimal("latitude", 9, 6).entityId() val longitude = decimal("longitude", 9, 6).entityId() val name = varchar("name", 32) override val primaryKey = PrimaryKey(areaCode, latitude, longitude) }

If any of the key component columns have already been marked by `entityId()` in another table, they can still be identified using `addIdColumn()`. This might be useful for key columns that reference another `IdTable`:

object AreaCodes : IdTable<Int>("area_codes") { override val id = integer("code").entityId() override val primaryKey = PrimaryKey(id) } object Towns : CompositeIdTable("towns") { val areaCode = reference("area_code", AreaCodes) val latitude = decimal("latitude", 9, 6).entityId() val longitude = decimal("longitude", 9, 6).entityId() val name = varchar("name", 32) init { addIdColumn(areaCode) } override val primaryKey = PrimaryKey(areaCode, latitude, longitude) }

For more information on `CompositeIdTable` types, see DAO Table Types.

### Foreign Key

The `FOREIGN KEY` SQL constraint links two tables. A foreign key is a column from one table that refers to the primary key or columns with a unique index from another table. To configure a foreign key on a column, use `reference()` or `optReference()` methods. The latter lets the foreign key accept a `null` value. To configure a foreign key on multiple columns, use `foreignKey()` directly within an `init` block.

`reference()` and `optReference()` methods have several parameters:

`name: String`

  A name for the foreign key column, which will be used in actual SQL queries.

`ref: Column<T>`

  A target column from another parent table.

`onDelete: ReferenceOption? = null`

  An action for when a linked row from a parent table will be deleted.

`onUpdate: ReferenceOption? = null`

  An action for when a value in a referenced column will be changed.

`fkName: String? = null`

  A name for the foreign key constraint.

Enum class `ReferenceOption` has five values:

`RESTRICT`

  An option that restricts changes on a referenced column, and the default option for most dialects.

`NO_ACTION`

  The same as RESTRICT in some, but not all, databases, and the default option for Oracle and SQL Server dialects.

`CASCADE`

  An option that allows updating or deleting the referring rows.

`SET_NULL`

  An option that sets the referring column values to null.

`SET_DEFAULT`

  An option that sets the referring column values to the default value.

Consider the following `Citizens` table. This table has the `name` and `city` columns. If the `Cities` table has configured the `name` column as the primary key, the `Citizens` table can refer to it by its `city` column, which is a foreign key. To configure such reference and make it nullable, use the `optReference()` method:

object Citizens : IntIdTable() { val name = varchar("name", 50) val city = optReference("city", Cities.name, onDelete = ReferenceOption.CASCADE) }

If any `Cities` row will be deleted, the appropriate `Citizens` row will be deleted too.

If instead the `Cities` table has configured multiple columns as the primary key (for example, both `id` and `name` columns as in the above section), the `Citizens` table can refer to it by using a table-level foreign key constraint. In this case, the `Citizens` table must have defined matching columns to store each component value of the `Cities` table's primary key:

object Citizens : IntIdTable() { val name = varchar("name", 50) val cityId = integer("city_id") val cityName = varchar("city_name", 50) init { foreignKey(cityId, cityName, target = Cities.primaryKey) } }

In the above example, the order of the referencing columns in `foreignKey()` must match the order of columns defined in the target primary key. If this order is uncertain, the foreign key can be defined with explicit column associations instead:

init { foreignKey(cityId to Cities.id, cityName to Cities.name) }

### Check

The `CHECK` SQL constraint checks that all values in a column match some condition. Exposed supports the `check()` method. You apply this method to a column and pass the appropriate condition to it.

For example, to check that the `name` column contains strings that begin with a capital letter, use the following code:

// SQL: CONSTRAINT check_Cities_0 CHECK (REGEXP_LIKE("NAME", '^[A-Z].*', 'c'))) val name = varchar("name", 50).check { it regexp "^[A-Z].*" }

Some databases, like older MySQL versions, may not support `CHECK` constraints. For more information, consult the relevant documentation.

## Creating tables

To create a table within a database, you need to use the `SchemaUtils.create()` method within a transaction:

transaction { SchemaUtils.create(StarWarsFilms) //... }

This will generate the SQL necessary to create the table based on your definition.

Last modified: 30 October 2024