SQL Table Management

Posted in Projectblog ∙ 17 mins read
SQL Table Management

I accidentally dropped a table. Not.


Share:          
  1. Making and Deleting Tables
    1. Create Table
    2. Drop Table
  2. Information Schema
    1. List all Tables
    2. List all Columns in a Table
    3. List Table Constraints
  3. Insert Into
    1. Insert a Row
    2. Insert a Column
  4. Update Table
  5. Delete Entries
  6. Alter Table
    1. Rename Column
    2. Add Column
    3. Drop Column
    4. Column Type
    5. Set Not Null, Drop Not Null
    6. Unique Constraint
    7. Primary Key Constraint
    8. Primary Key / Add Surrogate Key Column
    9. Foreign Key Constraint (1 to n)
    10. Foreign Key Constraint (n to m)
    11. Key Referential Integrity
  7. Triggers
    1. Data Manipulation: AFTER triggers
    2. Data Manipulation: INSTEAD OF triggers
    3. Data Definition: FOR triggers
    4. Listing all triggers and trigger events in the system
    5. Viewing a specific trigger
    6. Listing all server, DB, and table triggers with definitions
    7. Disable / Enable / Drop / Alter Triggers

Database tables are split up into sub-tables to avoid saving redundant records. This requires the maintenance of more complex systems, but it may be more efficient after all. However, while querying and joining we can combine these tables again. This post contains a number of SQL used for managing values, columns, tables, and databases.

Of course, the original tables and outputs are not available here, but the point is providing a lookup for structures and building blocks.

Making and Deleting Tables

Create Table

New tables can be created directly in SQL syntax.

CREATE TABLE table_name (
	column1 dtype,
	column2 dtype,
	column3 dtype
);

For example:

CREATE TABLE rocktypes (
	ID integer PRIMARY KEY,
	name text NOT NULL,
	type text UNIQUE,
	clevage_angle integer
);

Most common Data Types:

DataTypeDescription
textString of any length
varchar[ (x) ]Maximum length of n characters
char[ (x) ]String of set length n
booleanTRUE, FALSE, NULL
date, time, timestamptime formats
numericno precision defined
integer-2147483648 to 2147483647

PostgreSQL Documentation: DataType table

Drop Table

Delete the entire table.

DROP TABLE table_name;

Information Schema

List all Tables

SELECT table_name 
FROM information_schema.tables
WHERE table_schema = 'public';

List all Columns in a Table

SELECT column_name, data_type 
FROM information_schema.columns
WHERE table_name = 'employees' AND table_schema = 'public';

List Table Constraints

List all foreign Keys

SELECT constraint_name, table_name, constraint_type
FROM information_schema.table_constraints
WHERE constraint_type = 'FOREIGN KEY';

Insert Into

Insert a Row

INSERT INTO table_name (existing_column1, existing_column2)
VALUES (val1, val2);

For Example:

INSERT INTO employee (firstname, lastname, department_shortname)
VALUES (NULL, 'Miller', 'SLS');

Insert a Column

INSERT INTO table_name
SELECT column1
FROM old_table;

For Example:

Use DISTINCT to remove duplicate records.

INSERT INTO rocktypes
SELECT DISTINCT outcrop, handpiece
FROM samples;

/* double check */
SELECT *
FROM rocktypes;

Update Table

Similar as INSERT INTO column, but with additional conditoins.

UPDATE connections
SET connections.employee_id = employees.id
FROM employees
WHERE connections.firstname = employees.firstname AND connections.lastname = employees.lastname;

Delete Entries

Delete rows selectively where conditions are met.

DELETE FROM organizations
WHERE id = 'ABC';

Alter Table

Rename Column

Have you made a type when creating a table?

ALTER TABLE table_name
RENAME COLUMN old_colname TO new_colname;

Add Column

ALTER TABLE table_name
ADD COLUMN id varchar(128);

Drop Column

Delete a column in a table.

ALTER TABLE table_name
DROP COLUMN column_name;

Column Type

After creation, the datatype of a column can still be modified. Extension of a string lenghth is easy.

ALTER TABLE table_name
ALTER COLUMN column_name
TYPE varchar(256);

Shortening the length of a value requires a processing step first. For example, a float type gets rounded first and then cast to integer type.

ALTER TABLE table_name
ALTER COLUMN column_name
TYPE integer
USING ROUND(long_num_value);

For example, when shortening string entries,a ‘SUBSTRING’ has to be created first selecting the characters from 1 to x.

ALTER TABLE table_name
ALTER COLUMN column_name
TYPE varchar(16)
USING SUBSTRING(column_name FROM 1 FOR 16)

Set Not Null, Drop Not Null

By default, a colum can contain NULL values. A column’s ‘NOT NULL’ flag can be set after the table was created.

ALTER TABLE table_name
ALTER COLUMN column_name
SET NOT NULL;

Remove ‘NOT NULL’.

ALTER TABLE table_name
ALTER COLUMN column_name
DROP NOT NULL;

Unique Constraint

Note: The unique constraint will require a new column be created.

ALTER TABLE table_name
ADD CONSTRAINT new_col_name UNIQUE(column_name);

Primary Key Constraint

ALTER TABLE table_name
ADD CONSTRAINT new_col_name PRIMARY KEY(old_col_name);

Primary Key / Add Surrogate Key Column

Add a primary key serial type.

ALTER Table table_name
ADD COLUMN id serial PRIMARY KEY;

Foreign Key Constraint (1 to n)

Generate a foreign key column that references a primary key in another table.

Assuming a star pattern schema, the central table will get a foreign key added to an central_table_id that refernece the branch_id.

ALTER TABLE [central_table] 
ADD CONSTRAINT [my_name_for_FKey_constraint] FOREIGN KEY (central_table_id) REFERENCES [branch_table] (branch_id);

For example:

Inserting entries into the employees table that violate the foreign key table, e.g. non-existing company, will result in error.

ALTER TABLE employees 
ADD CONSTRAINT employees_fkey FOREIGN KEY (company_id) REFERENCES companies (id);

Drop / Delete a Foreign Key Column

ALTER TABLE connections
DROP CONSTRAINT connections_organization_id_fkey;

Foreign Key Constraint (n to m)

This is two foreign key columns referencing other tables in a many to many relationship.

CREATE TABLE connections (
    employee_id integer REFERENCES employees (id),
    organization_id varchar(256) REFERENCES organizations (id),
    function varchar(256)
	);

Key Referential Integrity

Foreign Keys referencing other tables can be used to prevent entering or deleting data that is not given/referred to in the other table.

We can determine what happens to the referenced table if an entry of our foreign key gets deleted.

Example: NO ACTION

If you delete an entry in table b, the system will raise an error.

CREATE TABLE a (
    id integer PRIMARY KEY,
    column_1 varchar(64),
    b_id integer REFERENCES b (id) ON DELETE NO ACTION
	);

Example: CASCADE

If you delete an entry in table b, all referencing entries in table a will be deleted, too.

CREATE TABLE a (
    id integer PRIMARY KEY,
    column_1 varchar(64),
    b_id integer REFERENCES b (id) ON DELETE CASCADE
    );

ALTER TABLE connections
ADD CONSTRAINT connections_organization_id_fkey FOREIGN KEY (organization_id) REFERENCES organizations (id) ON DELETE CASCADE;
ON DELETE…Description
NO ACTIONError
CASCADEDelete all referencing entries
RESTRICTError
SET NULLSet referencing entry to NULL
SET DEFAULTSet referencing entry to default value

Triggers

Data Manipulation: AFTER triggers

Use after INSERT, UPDATE, DELETE data manipulation.

The tables ‘deleted’ and ‘inserted’ are automatically created by SQL Server.

Example 1: AFTER DELETE

CREATE TRIGGER TrackDeletedProducts
ON ProductsTable
AFTER DELETE
AS
    INSERT INTO DeletedProducts (Product, Price)
    SELECT Product, Price
    FROM deleted;

Example 2: AFTER INSERT, UPDATE

The ‘inserted’ is used for both ‘INSERT’ and ‘UPDATE’.

CREATE TRIGGER TrackClientHistory
ON ClientTable
AFTER INSERT, UPDATE
AS
    INSERT INTO ClientHistoryTable (Client, ClientID, Address, PhoneNumber)
    SELECT Client, ClientID, Address, PhoneNumber, GETDATE()
    FROM inserted;

Example 3: After INSERT, execute e-mail sending script.

CREATE TRIGGER NewClientPushEmail
ON ClientTable
AFTER INSERT
AS
    EXECUTE SendNotification @RecipientEmail = '[email protected]'
                            ,@EmailSubject = 'New Client Signup'
                            ,@EmailBody = 'A new client just signed up with us.';

Example 4: Differentiate UPDATE and Insert with CASE

Write changes into a ClientAuditTable

CREATE TRIGGER ClientAudit
ON Orders
AFTER INSERT, UPDATE, DELETE
AS
    DECLARE @Insert BIT = 0;
    DECLARE @Delete BIT = 0;
    IF EXISTS (SELECT * FROM inserted) SET @Insert = 1;
    IF EXISTS (SELECT * FROM deleted) SET @Delete = 1;
    INSERT INTO ClientAuditTable (TableName, EventType, UserAccount, EventDate)
    SELECT 'Orders' AS TableName
           ,CASE WHEN @Insert = 1 AND @Delete = 0 THEN 'INSERT'
                 WHEN @Insert = 1 AND @Delete = 1 THEN 'UPDATE'
                 WHEN @Insert = 0 AND @Delete = 1 THEN 'DELETE'
                 END AS Event
           ,ORIGINAL_LOGIN() AS UserAccount
           ,GETDATE() AS EventDate;

Data Manipulation: INSTEAD OF triggers

Use instead of INSERT, UPDATE, DELETE data manipulation.

Example 1: Instead of updating, raise an error message and send a push email.

CREATE TRIGGER PreventProductsUpdate
ON Products
INSTEAD OF UPDATE
AS
    DECLARE @EmailBodyText NVARCHAR(50) = 
                        (SELECT 'User ' + ORIGINAL_LOGIN() + ' tried to update the Products table.');
						
    RAISERROR ('Updates on "Products" table are not permitted.', 16, 1);
	
    EXECUTE SendNotification @RecipientEmail = '[email protected]'
                            ,@EmailSubject = 'User tried to update Products'
                            ,@EmailBody = @EmailBodyText;

Example 2: Instead of inserting, check first if condition is met, then resume insert

CREATE TRIGGER ConfirmInventory
ON OrderTable
INSTEAD OF INSERT
AS
    IF EXISTS (SELECT * FROM inventory AS iv
               INNER JOIN  inserted  AS is ON  is.Product = iv.Product
               WHERE iv.Quantity  < is.Quantity )
    RAISEERROR ('You cant order higher quantities than there is in the inventory.', 16, 1);
	
    ELSE
        INSERT INTO dbo.OrderTable (Client, Product, Quantity, Date, Total)
        SELECT Client, Product, Date, Total FROM inserted;

Data Definition: FOR triggers

FOR triggers can only be run after, not instead of. That means ‘FOR’ is a synonym for ‘AFTER’.

It is impossible to prevent server or database events with ‘INSTEAD OF’, however they can be rolled back with ‘FOR’

The ‘DATABASE’ level includes all tables in the database.

CREATE TRIGGER TrackTableChanges
ON DATABASE
FOR CREATE_TABLE,
    ALTER_TABLE,
    DROP_TABLE
AS
    INSERT INTO TablesLog (EventData, User)
    VALUES (EVENTDATA(), USER);

Roll back a database-level event with ‘FOR’.

CREATE TRIGGER PreventTableDrop
ON DATABASE
FOR DROP_TABLE
AS
    RAISERROR ('DROP is not allowed.', 16, 1);
    ROLLBACK;

Roll back a server-level event with ‘FOR’.

CREATE TRIGGER PreventDatabaseDrop
ON ALL SERVER
FOR DROP_DATABASE
AS
   PRINT 'You are not permitted to drop databases.';
   ROLLBACK;

Listing all triggers and trigger events in the system

Server-level triggers:

SELECT * FROM sys.server_triggers;

SELECT * FROM sys.server_trigger_events;

Database + Table-level triggers (see column ‘parent_class_desc’):

SELECT * FROM sys.triggers;

SELECT * FROM sys.trigger_events;

Example: Show all disabled triggers

SELECT name,
	   object_id,
	   parent_class_desc
FROM sys.triggers
WHERE is_disabled = 1;

Viewing a specific trigger

The name of a specific trigger has to be known and then can be entered as object_id.

Method 1:

SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID ('PreventTableDrop');

Method 2:

SELECT OBJECT_DEFINITION (OBJECT_ID ('PreventTableDrop'));

Listing all server, DB, and table triggers with definitions

SELECT name AS TriggerName,
       parent_class_desc AS TriggerType,
       create_date AS CreateDate,
       modify_date AS LastModifiedDate,
       is_disabled AS Disabled,
       is_instead_of_trigger AS InsteadOfTrigger,
       OBJECT_DEFINITION  (object_id)
FROM sys.triggers
UNION ALL
SELECT name AS TriggerName,
       parent_class_desc AS TriggerType,
       create_date AS CreateDate,
       modify_date AS LastModifiedDate,
       is_disabled AS Disabled,
       0 AS InsteadOfTrigger,
       OBJECT_DEFINITION  (object_id)
FROM sys.server_triggers
ORDER BY TriggerName;

Disable / Enable / Drop / Alter Triggers

On table level:

DISABLE TRIGGER PreventProductsUpdate;

ENABLE TRIGGER PreventProductsUpdate;

DROP TRIGGER PreventProductsUpdate;

On database level:

DISABLE TRIGGER PreventTableDrop
ON DATABASE;

ENABLE TRIGGER PreventTableDrop
ON DATABASE;

DROP TRIGGER PreventTableDrop
ON DATABASE;

On Server level:

DISABLE TRIGGER PreventDatabaseDrop
ON ALL SERVER;

ENABLE TRIGGER PreventDatabaseDrop
ON ALL SERVER;

DROP TRIGGER PreventDatabaseDrop
ON ALL SERVER;

‘ALTER TRIGGER’

Altering triggers works with the same syntax as ‘CREATE TRIGGER’, but combines the dropping and creating of a trigger in one step.

ALTER TRIGGER PreventDatabaseDrop
ON ALL SERVER
FOR DROP_DATABASE
AS
   PRINT 'You are ABSOLUTELY NOT permitted to drop databases.';
   ROLLBACK;


© 2023. All rights reserved. Hosted on GitHub, made with https://hydejack.com/