Table
File extension:
*.table
Overview:
The *.table
files in the codbex platform are used to define and manage database tables. They provide a structured way to specify table properties, columns, and constraints.
Here's an explanation of the example PERSONS.table
file and its properties:
{
"name": "PERSONS",
"type": "TABLE",
"columns": [
{
"name": "PERSON_FIRST_NAME",
"type": "VARCHAR",
"length": "50",
"nullable": "false",
"primaryKey": "false",
"defaultValue": ""
},
{
"name": "PERSON_LAST_NAME",
"type": "VARCHAR",
"length": "50",
"nullable": "false",
"primaryKey": "false",
"defaultValue": ""
},
{
"name": "PERSON_ADDRESS_ID",
"type": "INTEGER",
"length": "0",
"nullable": "false",
"primaryKey": "false",
"defaultValue": ""
},
{
"name": "PERSON_SOCIAL_NUMBER",
"type": "REAL",
"length": "50",
"nullable": "true",
"primaryKey": "false",
"defaultValue": "",
"precision": "5",
"scale": "2"
},
{
"name": "PERSON_YEAR_OF_BIRTH",
"type": "INTEGER",
"length": "0",
"nullable": "true",
"primaryKey": "false"
}
],
"constraints": {
"name": "PERSONS_CONSTRAINTS",
"primaryKey": {
"name": "PERSONS_PK",
"columns": ["PERSON_FIRST_NAME", "PERSON_LAST_NAME"]
},
"foreignKeys": [
{
"name": "PERSONS_FK",
"columns": ["PERSON_ADDRESS_ID"],
"referencedTable": "ADDRESSES",
"referencedColumns": ["ADDRESS_ID"]
}
],
"uniqueIndices": [
{
"name": "PERSONS_UI",
"columns": ["PERSON_SOCIAL_NUMBER"]
}
],
"checks": [
{
"name": "PERSONS_CK",
"expression": "PERSON_YEAR_OF_BIRTH > 2000"
}
]
}
}
Properties:
name
(String): Specifies the name of the table. In this example, it is namedPERSONS
.type
(String): Indicates the type of the artifact, and for tables, it is set toTABLE
.columns
(Array of Objects): Defines an array of column objects, each specifying the properties of a table column.- Column Object Properties:
name
(String): Represents the name of the column.type
(String): Specifies the data type of the column, such asVARCHAR
,INTEGER
, orREAL
.length
(String): Specifies the length or size of the column.nullable
(String): Indicates whether the column allowsnull
values (true
orfalse
).primaryKey
(String): Specifies whether the column is part of the primary key (true
orfalse
).defaultValue
(String): Specifies the default value for the column.precision
andscale
(Strings): Applicable for numeric types, specifying the precision and scale of the column.
constraints
(Object): Defines an object containing various constraints for the table.- Constraints Object Properties:
name
(String): Specifies the name of the constraints object.primaryKey
(Object): Specifies the primary key constraint, including the name and columns.foreignKeys
(Array of Objects): Defines an array of foreign key constraints, specifying the name, columns, referenced table, and referenced columns.uniqueIndices
(Array of Objects): Defines an array of unique index constraints, specifying the name and columns.checks
(Array of Objects): Defines an array of check constraints, specifying the name and expression.
Example Usage:
The example PERSONS.table
file defines a table named PERSONS
with multiple columns of various data types. It includes constraints such as a primary key, foreign key, unique index, and check constraint.
Getting Started:
Create a Table File:
Create a new *.table
file or modify an existing one based on your specific table requirements.
Define Columns:
Add column objects within the columns array, specifying the properties of each table column.
Configure Constraints:
Define constraints within the constraints object, such as primary keys, foreign keys, unique indices, and checks.
Use the Table:
Once defined, the table can be used within your database schema to store and retrieve data.
Best Practices:
Clear Naming Conventions:
Follow clear and consistent naming conventions for tables, columns, and constraints.
Column Data Types:
Choose appropriate data types for columns based on the nature of the data they will store.
Constraint Validation:
Ensure that constraints are appropriate for maintaining data integrity and consistency.
Conclusion:
The *.table
files in the platform provide a structured way to define and manage database tables, allowing for the creation of robust and well-defined database schemas.