Cinchy DDL statements
DDL is used to create a database schema and define constraints. The Cinchy DDL functions covered on this page are:
Create Table
The CREATE TABLE statement is used to create a new table in a database.
The column parameters specify the names of the columns of the table. The datatype parameter specifies the data type the column can hold (such as varchar, char, int, date).
Syntax
CREATE TABLE [Domain].[Table_Name] (
[Column1] [datatype],
[Column2] [datatype],
[Column3] [datatype]
)
Example The below example creates a table called [Employees] in the [HR] domain. It includes the following columns and their respective data types:
- Employee ID; INT
- Last Name; VARCHAR(255)
- First Name; VARCHAR(255)
- Start Date; DATE
CREATE TABLE [HR].[Employees] (
[Employee_ID] INT,
[LastName] VARCHAR(255),
[FirstName] VARCHAR(255),
[Start Date] DATE
)
Alter Table
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
The ALTER TABLE statement is also used to add constraints on an existing table. Constraints can be used to limit the type of data that can go into a table. Once added, Cinchy does not store the name of your constraint. In order to modify it in any way you must remember the name.
Syntax
ALTER TABLE [Domain].[Table_Name]
ADD [column_name] datatype
Examples The below example adds a column named "Email", with the data type VARCHAR(255) to the [Employees] table.
ALTER TABLE [HR].[Employees]
ADD [Email] VARCHAR(255)
The following example adds a constraint on the table where the [Amount] must be equal to or greater than 0.
ALTER TABLE [Production].[Customer]
ADD CONSTRAINT CK_Amount CHECK ([Amount] >= 0)
Drop Table
The DROP TABLE statement is used to drop an existing table in a database.
Syntax
DROP TABLE [Domain].[Table Name]
Example The below example drops the [Employees] table from the [HR] domain.
DROP TABLE [HR].[Employees]
Create View
Creates a Saved Query.
Despite the name, CREATE VIEW creates Saved Queries, not table views. You can only create a view via the Manage Data UI on a table.
Syntax
CREATE VIEW [Domain].[Saved Query Name]
AS <Your Query>
Example
CREATE VIEW [Sales].[Fetch ARR] AS
SELECT [Client Name].[Full Name], [Client Name].[Email Address], [Advisor].[Full Name], [Q1], [Q2]
FROM [Sales].[ARR]
WHERE [Q1] BETWEEN 0 AND 6
Alter View
Modifies Saved Queries. ALTER VIEW doesn't affect dependent stored procedures or triggers and doesn't change permissions.
Despite the name, ALTERS VIEW updates Saved Queries, not table views. You can only alter a view via the Manage Data UI on a table.
Syntax
ALTER VIEW [Domain].[Saved Query Name]
AS <Your Modified Query>
Example
ALTER VIEW [Sales].[Fetch ARR]
AS
SELECT [Client Name].[Full Name], [Client Name].[Email Address], [Advisor].[Full Name], [Q1], [Q2]
FROM [Sales].[ARR]
WHERE [Q1] BETWEEN 0 AND 6
Drop View
Use the DROP VIEW command to delete a Saved Query.
Despite the name, DROP VIEW deletes Saved Queries, not table views. You can only delete a view via the Manage Data UI on a table.
Syntax
DROP VIEW [Domain].[Saved Query Name]
Example The below example deletes the "Fetch ARR" query from the [Sales] domain.
DROP VIEW [Sales].[Fetch ARR]
Create Index
The CREATE INDEX statement is used to createindexes in tables.
Syntax
CREATE INDEX [index_name]
ON [Domain].[Table_Name] ([Column1],[Column2], ...)
Example The below example creates an index called "idx_lastname" on the "Last Name" column from the [Employees] table in the [HR] domain.
CREATE INDEX [idx_lastname]
ON [HR].[Employees] ([LastName])
Drop Index
The DROP INDEX statement is used to delete an index on a table.
Syntax
DROP INDEX [index_name] ON [Domain].[Table_Name]
Example The below example drops the "idx_lastname" index from the [HR] table in the [Employees] domain.
DROP INDEX [idx_lastname] ON [HR].[Employees]
Truncate Table
The TRUNCATE TABLE statement removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on, remain.
When using TRUNCATE, pay in mind the following considerations:
- Records are fully deleted from the table, i.e. they will not go into the Recycle Bin.
- TRUNCATE does not work on tables that include link columns. In those cases, it is recommend to use a DELETE statement instead.
Syntax
TRUNCATE TABLE [Domain].[Table]
Example
The below example removes all the rows from the [Employees] table in the [HR] domain.
TRUNCATE TABLE [HR].[Employees]