When we create the tables for a database, we need to pay careful attention to the order in which they are created. This is because of referential integrity constraints, which dictate the order of creation. We cannot create a child table before we create a parent table (just like in real life, you become a parent when you have children).
Therefore any table that has a foreign key that references another table must be created after the table it references. For example, the INVOICE table has to be created after the CUSTOMER tables because the foreign key CustABN in INVOICE references the primary key CustABN in CUSTOMER.
Finally, to create the tables for the INVOICE database we have to finish off our CREATE TABLE statements. For example:
CREATE TABLE CUSTOMER(
CustABN VARCHAR2(11),
CustName VARCHAR2(35) NOT NULL,
CustAddress VARCHAR2(32) NOT NULL,
CustSuburb VARCHAR2(20) NOT NULL,
CustState VARCHAR2(3) NOT NULL,
CHECK (CustState IN('QLD', 'NSW', 'ACT', 'VIC', 'TAS', 'SA', 'WA', 'NT')),
CustPostcode VARCHAR2(4) NOT NULL,
CONSTRAINT pkCustomer PRIMARY KEY (CustABN)
);
Each statement starts with the CREATE TABLE command followed by the name of the table to be created. An opening bracket starts the body of the statement. Indentation is used to make the statement easier for humans to read.
Each definition and constraint in the statement ends with a comma except for the very last one. The statement is then completed with a closing bracket and a semicolon.