Data definition statements

DDL statements should be avoided in programs.

When using Data Definition Statements like CREATE TABLE, ALTER TABLE, DROP TABLE, only a limited SQL syntax works on all database servers. Most databases support NOT NULL, CHECK, PRIMARY KEY, UNIQUE, FOREIGN KEY constraints, but the syntax for naming constraints is different.

The following statement works with most database servers and creates a table with equivalent properties in all cases:

 CREATE TABLE customer  (
     cust_id INTEGER NOT NULL,
     cust_name CHAR(50) NOT NULL,
     cust_lastorder DATE NOT NULL,
     cust_group INTEGER,
       PRIMARY KEY (cust_id),
       UNIQUE (cust_name),
       FOREIGN KEY (cust_group) REFERENCES group (group_id)
 )

Some engines like SQL Server have a different default behavior for NULL columns when you create a table. You may need to set up database properties to make sure that a column allows nulls if the NOT NULL constraint is not specified.

When you want to create tables in programs using non-standard clauses (for example to define storage options), you must use dynamic SQL and adapt the statement to the target database server.