[Most of this is lifted directly from Oracle documentation.] column_name data_type [DEFAULT value] [constraints] ----------------------------------------------------------------------- column_name is 1-30 letters, digits, _. Must start with letter. must be unique in table. ----------------------------------------------------------------------- Data types: CHAR(n) fixed-length character string of length n. padded with blanks. Max n is 2000. Use for known fixed-length string data: state abbrev, zip code, SSN, sex, status, area code, phone number ... VARCHAR2(n) variable-length string, up to n characters. not padded. Max n is 4000. Use for text whose length cannot be determined beforehand: names, description NCHAR and NVARCHAR2 datatypes store Unicode character data. character data is delimited by single quotes: 'TN' 'hello world' '96367' numeric-looking data that is not going to have arithmetic done with it should be one of the above character types. For nearly all cases where you need to store numeric data, use the NUMBER datatype. NUMBER integer and real up to 40 digits NUMBER(p) integer up to p (precision) digits NUMBER(p,s) real up to p digits with s (scale) of them to right of decimal point. Number(p)=Number(p,0) It is good practice to specify the scale and precision of a fixed-point number column for extra integrity checking on input. Specifying scale and precision does not force all values to a fixed length. If a value exceeds the precision, then Oracle Database XE returns an error. If a value exceeds the scale, then Oracle Database XE rounds it. BINARY_FLOAT and BINARY_DOUBLE datatypes use binary precision (IEEE 754 format). This enables faster arithmetic calculations and usually reduces storage requirements. DATE day-monthAbbrev-year: delimited by single quotes: '23-dec-2005' '1-JAN-1999' DATE columns are automatically formatted by Oracle Database XE to include a date and time component. The DATE datatype stores the century, year, month, day, hours, minutes, and seconds. The valid date range is from January 1, 4712 BC to December 31, 9999 AD. Although both the date and time are stored in a date column, by default, the date portion is automatically displayed for you, when retrieving date data. TIMESTAMP datatype to store values that are precise to fractional seconds. An application that must decide which of two events occurred first might use TIMESTAMP. Large Objects (LOBs) are a set of datatypes that are designed to hold large amounts of data. A LOB can hold up to a maximum size ranging from 8 terabytes to 128 terabytes depending on how your database is configured. Storing data in LOBs enables you to access and manipulate the data efficiently in your application. The BLOB, CLOB, and NCLOB datatypes are internal LOB datatypes and are stored in the database. The BFILE datatype is the only external LOB datatype and is stored in an operating system file, outside the database. Not a type or a value: NULL indicates absence of value: missing, unknown, not applicable, deferred. indeterminate. empty. zero and space are not null. Oracle: inserting empty string results in Null. Can not be used in calculation or comparisons (not even with itself, i.e. Null!=Null, must use IS NULL). all nulls form one group in GROUP BY, DISTINCT, ORDER BY ----------------------------------------------------------------------- DEFAULT value optional. specifies the default value for the column when a row is inserted without a value for this column. You can define default values that are values that are automatically stored in the column whenever a new row is inserted without a value being provided for the column. When you define a column with a default value, any new rows inserted into the table store the default value unless the row contains an alternate value for the column. Assign default values to columns that contain a typical value. For example, in the employees table, if most employees work in the sales department, then the default value for the department_id column can be set to the ID of the sales department. Depending on your business rules, you might use default values to represent zero or FALSE, or leave the default values as NULL to signify an unknown value. Default values can be defined using any literal, or almost any expression including SYSDATE, which is a SQL function that returns the current date. dependents NUMBER(2,0) DEFAULT 0 activation DATE DEFAULT Sysdate ----------------------------------------------------------------------- Constraints. rules that define which data values are valid during Insert, Update, and Delete operations. You can define integrity constraints to enforce business rules on data in your tables to preserve the integrity of the data. Business rules specify conditions and relationships that must always be true, or must always be false.For example, in a table containing employee data, the employee e-mail column must be unique. Similarly, in this table you cannot have two employees with the same employee ID. When an integrity constraint applies to a table, all data in the table must conform to the corresponding rule. When you issue a SQL statement that inserts or modifies data in the table, Oracle Database XE ensures that the new data satisfies the integrity constraint, without the need to do any checking within your program. Any attempt to insert, update, or remove a row that violates a constraint results in an error, and the statement is rolled back. Likewise, any attempt to apply a new constraint to a populated table also results in an error if any existing row violates the new constraint. Constraints can be created and, in most cases, modified with a number of different status values. The options include enabled or disabled, which determine if the constraint is checked when rows are added, modified, or removed; and deferred or immediate, which cause constraint validation to occur at the end of a transaction or at the end of a statement, respectively. You can enforce rules by defining integrity constraints more reliably than by adding logic to your application. Oracle Database XE can check that all the data in a table obeys an integrity constraint faster than an application can. Constraints can be defined at the column level or at the table level: Column-level constraints are syntactically defined where the column to which the constraint applies is defined. These constraints determine what values are valid in the column. When creating a table with Object Browser, the only constraint defined at the column level is the NOT NULL constraint, which requires that a value is included in this column for every row in the table. Table-level constraints are syntactically defined at the end of the table definition and apply to the entire table. With Object Browser, you can create primary key, foreign key, unique, and check constraints. Column constraints: at table creation, or added or dropped later, can be disabled and enabled. [CONSTRAINT [constraint_name]] NOT NULL this column can not be Null. attempts to Insert or Update Null is error. The NOT NULL constraint is a column-level constraint that requires that the column must contain a value whenever a row is inserted or updated. The NOT NULL constraint must be defined as part of the column definition. Use a NOT NULL constraint when the data is required for the integrity of the database. For example, if all employees must belong to a specific department, then the column that contains the department identifier should be defined with a NOT NULL constraint. On the other hand, do not define a column as NOT NULL if the data might be unknown or might not exist when rows are added or changed, for example, the second, optional line in a mailing address. A primary key constraint automatically adds a NOT NULL constraint to the columns included in the primary key, in addition to enforcing uniqueness among the values. CHECK (condition) impose further restriction on column values. A check constraint requires that a column (or combination of columns) satisfies a condition for every row in the table. A check constraint must be a Boolean expression that is evaluated using the column value about to be inserted or updated to the row. Use check constraints when you need to enforce integrity rules based on logical expressions, such as comparisons. Never use CHECK constraints when any of the other types of integrity constraints can provide the necessary checking. price Number(6,2) CHECK(price >= 0) dependents Integer CHECK(dependents Between 0 AND 12) date_hired Date CHECK(date_hired>='1-Jan-2000') ename Varchar2(20) CHECK(ename=upper(ename)) --uppercase only salary Number(6,2) CHECK(salary Is Not Null And salary>=10000) --else could be null sex Char(1) CHECK(sex IN ('M','F')) sex Char(1) CHECK(sex IN ('M','F') AND NOT NULL) ?? UNIQUE every value in this column is unique in the table i.e. no duplicates in this column. implies Not Null. A unique constraint requires that every value in a column be unique. That is, no two rows can have duplicate values in a specified column or combination of columns. Choose columns for unique constraints carefully. The purpose of these constraints is different from that of primary keys. Unique key constraints are appropriate for any column where duplicate values are not allowed. Primary keys identify each row of the table uniquely, and typically contain values that have no significance other than being unique. In the employees table, the email column has a unique key constraint because it is important that the e-mail address for each employee is unique. Note that the email column has a NOT NULL constraint. Some examples of good unique keys include: An employee social security number, where the primary key might be the employee number A truck license plate number, where the primary key might be the truck number A customer phone number, consisting of the two columns area_code and local_phone, where the primary key might be the customer number A department name and location, where the primary key might be the department number PRIMARY KEY every value in this column is unique in the table i.e. no duplicates in this column. implies Not Null and Unique. use to uniquely identify each row (i.e. each entity): entity integrity. Unique and Primary Key cause an Index to be made on the column, speeding searches. A primary key requires that a column (or combination of columns) be the unique identifier of the row and ensures that no duplicate rows exist. A primary key column cannot contain NULL values. Each table can have only one primary key. Use the following guidelines when selecting a primary key: * Whenever practical, create a sequence number generator to generate unique numeric values for your primary key values. * Choose a column whose data values are unique, because the purpose of a primary key is to uniquely identify each row of the table. * Choose a column whose data values are never changed. A primary key value is only used to identify a row in the table, and its data should never be used for any other purpose. Therefore, primary key values should rarely or never be changed. * Choose a column that does not contain any null values. A PRIMARY KEY constraint, by definition, does not allow any row to contain a null value in any column that is part of the primary key. * Choose a column that is short and numeric. Short primary keys are easy to type. * Minimize your use of composite primary keys. A composite primary key constraint applies to more than one column. Although composite primary keys are allowed, they do not satisfy all of the other recommendations. For example, composite primary key values are long and cannot be assigned by sequence numbers. Whenever two tables contain one or more common columns, you can enforce the relationship between the tables through a referential integrity constraint with a foreign key. A foreign key requires that all column values in the child table exist in the parent table. The table that includes the foreign key is called the dependent or child table. The table that is referenced is called the parent table. An example of a foreign key constraint is when the department column of the employees table (child) must contain a department ID that exists in the departments table (parent). Foreign keys can be made up of multiple columns. Such a composite foreign key must reference a composite primary or unique key of the exact same structure, with the same number of columns and the same datatypes. Because composite primary and unique keys are limited to 32 columns, a composite foreign key is also limited to 32 columns. You must use the same datatype for corresponding columns in the parent and child tables. The column names do not need to match. For performance purposes, you might want to add an index to the columns you define in a child table when adding a foreign key constraint. Oracle Database XE does not do this for you automatically. When you create a foreign key constraint on a table, you can specify the action to take when rows are deleted in the referenced (parent) table. These actions include: Disallow Delete - Blocks the delete of rows from the referenced table when there are dependent rows in the table. Cascade Delete - Deletes the dependent rows from the table when the corresponding parent table row is deleted from the referenced table. Null on Delete - Sets the foreign key column values in the table to null values when the corresponding table row is deleted from the referenced table. REFERENCES parenttablename [ON DELETE CASCADE] REFERENCES parenttablename [(unique_column_name)] [ON DELETE CASCADE] this column is a foreign key referring either to the primary key of the named table or to a named unique key of the named table. On Delete Cascade means delete rows of this table if the referenced key in the named table is deleted (default is NO ACTION, i.e. disallow). Referenced parent table and column must already be created. Common practice for Unique, Primary Key, and foreign key constraints to be expressed as table constraints instead of column constraints. Required to be if multiple columns is the constraint. See below. Specifying a constraint_name will be more readable than the automatic system-generated name. ----------------------------------------------------------------------- ----------------------------------------------------------------------- Table constraints: after all the column definitions. [CONSTRAINT constraint_name] CHECK (condition) Check(end>start) --relation between two columns UNIQUE (column_name {, column_name}) one column or combination of columns is unique in table PRIMARY KEY (column_name {, column_name}) one column or combination of columns is the primary key. Primary key MAY NOT be Null. FOREIGN KEY (column_name) REFERENCES parenttablename this column references the primary key of the parent table FOREIGN KEY (column_name {, column_name}) REFERENCES parenttablename this combination of columns references the composite primary key of the parent table FOREIGN KEY (column_name) REFERENCES parenttablename (parenttablecolumn) this column references a unique key of the parent table FOREIGN KEY (column_name {, column_name}) REFERENCES parenttablename (parenttablecolumn {,parenttablecolumn}) this combination of columns references a unique key combo of the parent table Foreign keys must have same data type as the parent table's referenced columns. A single record in the parent table can be referred to by ANY number of records in the child table. The One of the parent can have Many childs. A foreign key MAY NOT reference a non-existent record in the parent table: referential integrity. Not possible for child table record to refer to a non-existent parent table record. May be Null though. ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------------------------------------------