Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 :)

share|improve this question
1  
What you should be careful is that constraints have to have unique names - across the schema. So the PK constraints are fine in your conventions but the FK names may collide. You may decide to have the CONSTRAINT address_fk named CONSTRAINT Place_Employee_fk or even CONSTRAINT address_Place_Employee_fk to take care of multiple constraint from and to the same tables. It is also handy to know which tables take part in the FK when an insert/update is restricted by the FK (I think the constraint name is included in the error message.) –  ypercube Apr 6 '13 at 12:23
    
You can also name the CHECK constraints: CONSTRAINT address_should_not_be_null_ck CHECK (address IS NOT NULL) –  ypercube Apr 6 '13 at 12:24
add comment

2 Answers

up vote 5 down vote accepted

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.

share|improve this answer
    
That's the way we do it as well. –  a_horse_with_no_name Mar 18 '13 at 13:18
    
Wow, that's a very useful use-case that I hadn't considered! +1 –  A T Mar 18 '13 at 15:05
add comment

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)

share|improve this answer
    
Yeah, it's all personal preference. I, for example, prefer putting foreign key cons in alter table statements. That way you can script all of your create table statements first, then fire off the alters afterwards - means you don't have to script your create table statements in a specific order to deal with dependencies. –  FreshPhilOfSO Mar 18 '13 at 12:07
    
@Phil: yes, that's what I meant with my first paragraph. –  a_horse_with_no_name Mar 18 '13 at 12:21
2  
+1 @AT To answer another part of your question, never use a trigger when a constraint will do. –  Leigh Riffel Mar 18 '13 at 12:41
    
+1 for indention instead of indentation. (Kidding. About the reason, not about the +1.) –  Mike Sherrill 'Cat Recall' Mar 18 '13 at 13:08
    
Phil/a_horse_with_no_name [BTW: Clint Eastwood parody? =P]: So it seems that there are some advantages with keeping things self contained, and other advantages (see answer by @MikeSherrill'Catcall') for keeping them separate. Maybe I just need to flex my Python knuckles and build a two-way script? :) - Leigh: Guessing that constraints incur less overhead? - Mike:\t\t\t\tIndentation FTW! –  A T Mar 18 '13 at 15:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.