Defining constraints in `CREATE TABLE` statements

Question

Recently I have been using a Database Abstraction Layer built by a Python web-framework called web2py (click for their DAL syntax). They include the option to include your constraints within the CREATE TABLE statement.

Whilst taking Stanford's "Introduction to Databases" MOOC, the SQL Standard was mentioned as supporting any query within the CREATE TABLE statement as constraints (essentially replacing a major use-case for triggers).

What is best practice?

Below is a simple example of including constraints in CREATE TABLE statements; rather than through ALERT TABLE and/or CREATE TRIGGER statements:

CREATE TABLE Place (
    address VARCHAR2(40),
    CONSTRAINT place_pk
        PRIMARY KEY (address)
);

CREATE TABLE Company (
    c_name VARCHAR2(40),
    CONSTRAINT company_pk
        PRIMARY KEY (c_name)
);

CREATE TABLE Employee (
    e_name VARCHAR2(40),
    tax_no NUMBER,
    salary NUMBER(19,4),
    sex CHAR,
    birthdate DATE,
    address VARCHAR2(40),
    CONSTRAINT employee_pk
        PRIMARY KEY (tax_no),
    CONSTRAINT address_fk
        FOREIGN KEY (address) REFERENCES Place(address),
    CHECK (address IS NOT NULL)
);

CREATE TABLE CompanyEmployee (
    employee_id NUMBER,
    company_id VARCHAR2(40),
    CONSTRAINT unique_employee_id
        UNIQUE(employee_id),
    CONSTRAINT employee_id_fk
        FOREIGN KEY (employee_id) REFERENCES Employee(tax_no),
    CONSTRAINT company_id_fk
        FOREIGN KEY (company_id) REFERENCES Company(c_name),
    CONSTRAINT company_employees_pk
        PRIMARY KEY (employee_id, company_id)
);

BTW: You'll note that I'm using CAPS for keywords, upper CamelCase for table names and lower under_score for attribute and trigger names. Is this good practice? - Feel free to critique my indentation and whitespace usage styles also :)

Answer 1

In an enterprise-level database, you can make a good case for splitting up your constraints in ways that make it easier to reload lots of data. I've seen

• bare create table statements
• candidate keys (including primary keys),
• foreign keys,
• check constraints,
• etc.

Each of those would be in one or more files. All those files would be under version control.

The rationale is that it's faster to load lots of data and then apply the constraints than it is to load lots of data after you've declared the constraints and indexes. With a little text manipulation in a makefile, you can still generate a "full" CREATE TABLE statement with all the constraints included.

Answer 2

I personally think it's a matter of taste.

The scripts where the constraints are defined through an ALTER statement are a bit more flexible, as you don't need to care about the order of creation (first create all tables, then all PKs, then all FKs).

The scripts with embedded constraints are more "self-contained" however. You don't need to look for other places in the script to find the constraint definitions.

Using camel-case doesn't make any difference as everything will be converted to uppercase anyway (because you did not use " to quote your names - which is a good thing).

Regarding indention and whitespace: that again is completely personal taste. Do it the way you think you can still understand this in 6 months. The most important thing is to be consistent (and maybe document your style somewhere in your project documentation, so that new members can understand and use the same formatting)