Creating SQL Database: Managing Other Database Objects

The CREATE, DROP, and ALTER verbs form the cornerstone of the SQL Data Definition Language. Statements based on these verbs are used in all SQL implementations to manipulate tables, indexes, and views (described in Chapter 14). Most of the popular SQL-based DBMS products also use these verbs to form additional DDL statements that create, destroy, and modify other database objects unique to that particular brand of DBMS.

The Sybase DBMS, for example, pioneered the use of triggers and stored procedures, which are treated as objects within a SQL database, along with its tables, assertions, indexes, and other structures. Sybase added the CREATE TRIGGER and CREATE PROCEDURE statements to its SQL dialect to define these new database structures, and the corresponding DROP statements to delete them when no longer needed. As these features became popular, other DBMS products added the capabilities, along with their own variants of the CREATE TRIGGER and CREATE PROCEDURE statements.

The common conventions across DBMS brands is (a) the use of the CREATE/ DROP/ALTER verbs, (b) the next word in the statement is the type of object being managed, and (c) the third word is the name of the object, which must obey SQL naming conventions. Beyond the first three words, the statements become very DBMS-specific and nonstandard. Nonetheless, this commonality gives a uniform feel to the various SQL dialects. At the very least, it tells you where to look in the reference manual for a description of a new capability. If you encounter a new SQL-based DBMS and know that it supports an object known as a BLOB, the odds are that it uses CREATE BLOB, DROP BLOB, and ALTER BLOB statements. Table 13-1 shows how some of the popular SQL products use the CREATE, DROP, and ALTER verbs in their expanded DDL. The SQL2 standard adopts this same convention to deal with the creation, destruction, and modification of all objects in a SQL2 database.

Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.

Leave a Reply

Your email address will not be published. Required fields are marked *