11 KiB
DDL: Table
CREATE TABLE
Creates a new table within the specified or default database.
<table>
s are the source of indexed persistent <table-sets>
s in Obelisk.
<create-table> ::=
CREATE TABLE
[ <db-qualifer> ]<table>
( <column> <aura>
[ ,... n ] )
PRIMARY KEY ( <column> [ ,... n ] )
[ { FOREIGN KEY <foreign-key> ( <column> [ ASC | DESC ] [ ,... n ] )
REFERENCES [ <namespace>. ] <table> ( <column> [ ,... n ] )
[ ON DELETE { NO ACTION | CASCADE | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET DEFAULT } ] }
[ ,... n ] ]
[ <as-of-time> ]
API
+$ create-table
$:
%create-table
table=qualified-object
columns=(list column)
pri-indx=(list ordered-column)
foreign-keys=(list foreign-key)
as-of=(unit as-of)
==
Arguments
Note: All names must adhere to the hoon term naming standard.
<table>
This is a user-defined name for the new table.
If not explicitly qualified, it defaults to the Obelisk agent's current database and the 'dbo' namespace.
<column> <aura>
The list of user-defined column names and associated auras.
For more details on auras, refer to 01-preliminaries
foreign keys supported in urQL parser, not yet supported in Obelisk
<foreign-key> ( <column> [ ASC | DESC ] [ ,... n ]
This is a user-defined name for <foreign-key>
.
This list comprises column names in the table for association with a foreign table along with sort ordering. Default is ASC
(ascending).
Note: The Obelisk engine does not yet implement foreign keys.
<table> ( <column> [ ,... n ]
Referenced foreign <table>
and columns. Count and associated column auras must match the specified columns from the new <table>
and comprise a UNIQUE
index on the referenced foreign <table>
.
ON DELETE { NO ACTION | CASCADE | SET DEFAULT }
This argument specifies the action to be taken on the rows in the table that have a referential relationship when the referenced row is deleted from the foreign table.
-
NO ACTION
(default) The Obelisk agent raises an error and the delete action on the row in the parent foreign table is aborted. -
CASCADE
Corresponding rows are deleted from the referencing table when that row is deleted from the parent foreign table. -
SET DEFAULT
All the values that make up the foreign key in the referencing row(s) are set to their bunt (default) values when the corresponding row in the parent foreign table is deleted.
The Obelisk agent raises an error if the parent foreign table has no entry with bunt values.
ON UPDATE { NO ACTION | CASCADE | SET DEFAULT }
This argument specifies the action to be taken on the rows in the table that have a referential relationship when the referenced row is updated in the foreign table.
-
NO ACTION
(default) The Database Engine raises an error and the update action on the row in the parent table is aborted. -
CASCADE
Corresponding rows are updated in the referencing table when that row is updated in the parent table. -
SET DEFAULT
All the values that make up the foreign key in the referencing row(s) are set to their bunt (default) values when the corresponding row in the parent foreign table is updated.
The Obelisk agent raises an error if the parent foreign table has no entry with bunt values.
<as-of-time>
Timestamp of table creation. Defaults to NOW
(current time). When specified, the timestamp must be greater than both the latest database schema and content timestamps.
Remarks
This command mutates the state of the Obelisk agent.
PRIMARY KEY
must be unique.
FOREIGN KEY
constraints ensure data integrity for the data contained in the column or columns. They necessitate that each value in the column exists in the corresponding referenced column or columns in the referenced table. FOREIGN KEY
constraints can only reference columns that are subject to a PRIMARY KEY
or UNIQUE INDEX
constraint in the referenced table.
Produced Metadata
Schema timestamp
Content timestamp
Exceptions
table must be created by local agent
database <database>
does not exist
table <table>
as-of schema time out of order
table <table>
as-of data time out of order
namespace <namespace>
does not exist
duplicate column names <columns>
duplicate column names in key <columns>
key column not in column definitions <pri-indx>
<table>
exists in <namespace>
<table>
referenced by FOREIGN KEY
does not exist
<table-column>
column referenced by FOREIGN KEY
does not exist
aura mis-match in FOREIGN KEY
state change after query in script
Example
CREATE TABLE order-detail
(invoice-nbr @ud, line-item @ud, product-id @ud, special-offer-id @ud, message @t)
PRIMARY KEY (invoice-nbr, line-item)
FOREIGN KEY fk-special-offer-order-detail (product-id, specialoffer-id)
REFERENCES special-offer (product-id, special-offer-id)
ALTER TABLE
supported in urQL parser, not yet supported in Obelisk
Modify the columns and/or <foreign-key>
s of an existing <table>
.
<alter-> ::=
ALTER TABLE [ <db-qualifer> ]{ <table> }
{ ADD COLUMN ( <column> <aura> [ ,... n ] )
| ALTER COLUMN ( <column> <aura> [ ,... n ] )
| DROP COLUMN ( <column> [ ,... n ] )
| ADD FOREIGN KEY <foreign-key> (<column> [ ,... n ])
REFERENCES [<namespace>.]<table> (<column> [ ,... n ])
[ ON DELETE { NO ACTION | CASCADE } ]
[ ON UPDATE { NO ACTION | CASCADE } ]
[ ,... n ]
| DROP FOREIGN KEY ( <foreign-key> [ ,... n ] } )
[ <as-of-time> ]
Example:
ALTER TABLE my-table
DROP FOREIGN KEY fk-1, fk-2
API
+$ alter-table
$:
%alter-table
table=qualified-object
alter-columns=(list column)
add-columns=(list column)
drop-columns=(list @tas)
add-foreign-keys=(list foreign-key)
drop-foreign-keys=(list @tas)
as-of=(unit as-of)
==
Arguments
Note: All names must adhere to the hoon term naming standard.
<table>
Name of <table>
to alter.
ADD | ALTER COLUMN ( <column> <aura> [ ,... n ] )
Denotes a list of user-defined column names and associated auras. ALTER
is used to change the aura of an existing column.
DROP COLUMN ( <column> [ ,... n ] )
Denotes a list of existing column names to delete from the <table>
structure.
ADD | DROP
The action is to add or drop a foreign key.
<foreign-key> ( <column> [ ASC | DESC ] [ ,... n ]
This is a user-defined name for <foreign-key>
.
This list comprises column names in the table for association with a foreign table along with sort ordering. Default is ASC
(ascending).
<table> ( <column> [ ,... n ]
Referenced foreign <table>
and columns. Count and associated column auras must match the specified columns from the new <table>
and comprise a UNIQUE
index on the referenced foreign <table>
.
ON DELETE { NO ACTION | CASCADE | SET DEFAULT }
This argument specifies the action to be taken on the rows in the table that have a referential relationship when the referenced row is deleted from the foreign table.
-
NO ACTION
(default) The Obelisk agent raises an error and the delete action on the row in the parent foreign table is aborted. -
CASCADE
Corresponding rows are deleted from the referencing table when that row is deleted from the parent foreign table. -
SET DEFAULT
All the values that make up the foreign key in the referencing row(s) are set to their bunt (default) values when the corresponding row in the parent foreign table is deleted.
The Obelisk agent raises an error if the parent foreign table has no entry with bunt values.
ON UPDATE { NO ACTION | CASCADE | SET DEFAULT }
This argument specifies the action to be taken on the rows in the table that have a referential relationship when the referenced row is updated in the foreign table.
-
NO ACTION
(default) The Database Engine raises an error and the update action on the row in the parent table is aborted. -
CASCADE
Corresponding rows are updated in the referencing table when that row is updated in the parent table. -
SET DEFAULT
All the values that make up the foreign key in the referencing row(s) are set to their bunt (default) values when the corresponding row in the parent foreign table is updated.
The Obelisk agent raises an error if the parent foreign table has no entry with bunt values.
<as-of-time>
Timestamp of table alteration. Defaults to NOW
(current time). When specified, the timestamp must be greater than both the latest database schema and content timestamps.
Remarks
This command mutates the state of the Obelisk agent.
FOREIGN KEY
constraints ensure data integrity for the data contained in the column or columns. They necessitate that each value in the column exists in the corresponding referenced column or columns in the referenced table. FOREIGN KEY
constraints can only reference columns that are subject to a PRIMARY KEY
or UNIQUE INDEX
constraint in the referenced table.
Produced Metadata
Schema timestamp
Exceptions
table must be altered by local agent
database <database>
does not exist
<table>
does not exists in <namespace>
alter a column that does not exist
add a column that does exist
drop a column that does not exist
<table>
referenced by FOREIGN KEY
does not exist
<table-column>
column referenced by FOREIGN KEY
does not exist
aura mis-match in FOREIGN KEY
alter table <table>
as-of schema time out of order
alter table <table>
as-of data time out of order
alter table state change after query in script
DROP TABLE
Deletes a <table>
and all associated objects.
<drop-table> ::=
DROP TABLE [ FORCE ] [ <db-qualifer> ]{ <table> }
[ <as-of-time> ]
API
+$ drop-table
$:
%drop-table
table=qualified-object
force=?
as-of=(unit as-of)
==
Arguments
FORCE
Optionally force deletion of table when table is populated, used in a view, or used in a foreign key.
<table>
Name of <table>
to delete.
<as-of-time>
Timestamp of table deletion. Defaults to NOW
(current time). When specified, the timestamp must be greater than both the latest database schema and content timestamps.
Remarks
This command mutates the state of the Obelisk agent.
Cannot drop if used in a view or foreign key, unless FORCE
is specified, resulting in cascading object drops. Affected views and foreign keys dropped.
Cannot drop when the <table>
is populated unless FORCE
is specified.
Produced Metadata
Schema timestamp
Content timestamp, if the table was populated
Row count (when table was populated)
Exceptions
table must be dropped by local agent
database <database>
does not exist
table <table>
as-of schema time out of order
<table>
as-of data time out of order
namespace <namespace>
does not exist
<table>
does not exist in <namespace>
<table>
has data, use FORCE
to DROP
<table>
used in <view>
, use FORCE
to DROP
<table>
used in <foreign-key>
, use FORCE
to DROP
state change after query in script
GRANT
permission on <table>
violated