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