A primary key has the following features:

> One and only one primary key must be specified. It uses the reserved words PRIMARY KEY in the CREATE TABLE statement.

> A simple key (one column) can be defined as a column constraint or a table constraint. As a simple key column constraint, PRIMARY KEY is placed in the column definition.

> A composite key (multiple columns) can only be defines as a table constraint. As a table constraint, the PRIMARY KEY definition has the column names specified.

> The primary key columns can have NOT NULL specified on the column. If NOT NULL is not specified on primary key columns, most RDBMS systems set the columns to NOT NULL by default.
DB2 requires that NOT NULL be specified for primary key columns.

> Primary key values can not be changed. If you want to change the value of a primary key, you must DELETE the row and then INSERT the same row with new key values.

Primary keys were discussed in Relational Database Basics. Let us review:Tables and columns have unique names, but rows are unnamed. Rows can not be identified by their position within the table, and the relational model looks at a table as an unordered set. Therefore, each table is required to have one or more columns as a unique primary key. It is traditional to place the primary key columns in the leftmost positions.

> A table must have one and only one primary key.

> A primary key with only one column is called a simple key.

> A primary key with two or more columns is called a composite key.

> No two rows in a table can have the same primary key.

> A primary key only includes the columns necessary for uniqueness.

> A primary key can not have an empty value, called a null, in any column.

> If referential integrity is enforced, a primary key can not be deleted unless all foreign keys are also deleted.

 

> DEFAULT is a column constraint in a CREATE TABLE statement and can not be a table constraint.

> When inserting a row, if no value is specified and the DEFAULT column constraint is specified, a default value will be inserted.

> The default value must be the same data type and fit in the length of the column definition.

> If no value is specified after the DEFAULT and NOT NULL in the table definition, numeric columns will default to zeroes and character columns will default to spaces.

> If DEFAULT is not defined and NOT NULL is defined and you insert a row with no data in this column, some RDBMS systems will refuse to insert the row, while other RDBMS systems will give a default value that matches the data type.

          CREATE TABLE SAMPLE_TBL
                (ID       CHAR(12)     NOT NULL PRIMARY KEY,
                 TYPE     CHAR(4)      NOT NULL DEFAULT,
                 NUMB     INTEGER          NULL,
                 NAME     VARCHAR(200)          DEFAULT 'unknown',
                 SIZE     SMALLINT     NOT NULL DEFAULT 7,
                 PRICE    DECIMAL(5,2) NOT NULL DEFAULT 0.00,
                 DATE     DATE                  DEFAULT CURRENT_DATE,
                 TIME     TIME                  DEFAULT CURRENT_TIME,
                 QTY      DECIMAL(4,0)          DEFAULT (5+3)*2);

With an INSERT ROW and no value given:

> ID must have a valid value.

> TYPE will default to spaces.

> NUMB will default to null. The NULL definition is optional by default.

> All other columns will default to the specified default value.

> Access, DB2, and MySQL do not allow arithmetic expressions.

> MySQL only allows a literal as a default. You can not use CURRENT_DATE or CURRENT_TIME.

> Oracle does not have CURRENT_DATE. It has SYSDATE and DEFAULT, which must be before constraints.

> SQL Server does not have DATE or TIME. It only has DATETIME.

> NOT NULL is a column constraint in a CREATE TABLE statement and can not be a table constraint.

> NULL is the default column constraint and is implied if it is not specified.

> It is best if you do not allow nulls by using NOT NULL.

> If a column does not have NOT NULL specified, the column accepts nulls by default.

> If you prevent nulls with NOT NULL, you force a value to be entered and you have better data integrity.

> Nulls can not be used in a PRIMARY KEY because every primary key must be unique.

> Nulls can be in a FOREIGN KEY, but it creates referential integrity problems.

> If a column is not associated with a PRIMARY KEY and it does not have NOT NULL, and no value is given, a null will be set for that column.

> If a column is not associated with a PRIMARY KEY and it does not have NOT NULL, a null can be explicitly set for this column regardless of the data type.

> Nulls complicate the results of SELECT, INSERT, and UPDATE statements.

See IS NULL (Unknown Values).

       CREATE TABLE          PAYROLL_TBL
             (EMPL_ID        CHAR(4)      NOT NULL,
              STOR_ID        CHAR(4)      NOT NULL,
              DUE_DATE       DATE         NOT NULL,
              HRSTHIS_PAYPRD DECIMAL(4,1) NOT NULL DEFAULT,
              GROSS          DECIMAL(8,2) NOT NULL DEFAULT,
              TAX            DECIMAL(8,2) NOT NULL DEFAULT,
              CONSTRAINT     PAYROLL_PK   PRIMARY KEY (EMPL_ID, STOR_ID, DUE_DATE),
              CONSTRAINT     PYRL_EMPL_FK FOREIGN KEY (EMPL_ID)
                                          REFERENCES EMPLOYEE_TBL(EMPL_ID),
              CONSTRAINT     PYRL_STOR_FK FOREIGN KEY (STOR_ID)
                                          REFERENCES STORE_TBL(STOR_ID));

Let us review:NULL is a marker used when
         the value does not exist,
         the value exists, but we don’t know what the value is,
         the value is out of range, or
         the value is not appropriate for this row.

> Nulls are shown as hyphens in the result of the SELECT statement.

> If a null is returned, it means that there is no value.

> A null is not the same as a zero (0), a blank, or an empty string (”).

> When testing for a specific value in the field, the result can be: TRUE (equal), FALSE (unequal), or UNKNOWN (null).

> If someone talks about a “null value”, that is wrong because null means there is no value.

> In SQL, the keyword NULL represents a null. When setting a column to NULL, do not specify ‘NULL’ between quotes, or the value will be the literal ‘NULL’.

> Nulls don’t belong to any data type and can be inserted into any column that allows nulls.

> The capability of not allowing nulls is established by using the keywords NOT NULL in a CREATE TABLE column definition. This will be covered in the Data Definition Language topic.

> If you don’t specify a NOT NULL constraint, the column will accept nulls by default.

> One extra byte is used in the SQL file for each field that can have a null, whether the field is NULL or not.

Constraints allow you to write rules for the allowable values in columns and enforce the integrity of the data. As you can see, there are two types of constraints in the format of a CREATE TABLE statement:

          CREATE TABLE table_name
                (column_name1 data_type1 [col_constraint1],
                 column_name2 data_type2 [col_constraint2],
                 ...
                [CONSTRAINT constraint_name1 table_constraint1,]
                [CONSTRAINT constraint_name2 table_constraint2,]
                 ...);

A column constraint is in the column definition and sets conditions only on the values in this column.
A column constraint does not have an explicit constraint name and can not apply to more than one column.
(The DBMS system assigns a column constraint name consisting of random characters and numbers.)

A table constraint is defined separately from the column definitions and can set conditions on one or many columns in the table.

Only table constraints have constraint names, but a column constraint (one column) can be stated as a table constraint and given a name. This allows you to ALTER or DROP the constraint by name.
Many company standards only allow named table constraints, not column constraints, so that the constraint can be identified in warning and error messages.
The exception is NOT NULL, which is nearly always an unnamed column constraint.

A primary key can be defined as a column constraint (if it is one column) or a table constraint.

Here is a sample CREATE TABLE statement.

      CREATE TABLE          PAYROLL_TBL
            (EMPL_ID        CHAR(4)      NOT NULL,
             STOR_ID        CHAR(4)      NOT NULL,
             DUE_DATE       DATE         NOT NULL,
             HRSTHIS_PAYPRD DECIMAL(4,1) NOT NULL DEFAULT,
             GROSS          DECIMAL(8,2) NOT NULL DEFAULT,
             TAX            DECIMAL(8,2) NOT NULL DEFAULT,
             CONSTRAINT     PAYROLL_PK   PRIMARY KEY (EMPL_ID, STOR_ID, DUE_DATE),
             CONSTRAINT     PYRL_EMPL_FK FOREIGN KEY (EMPL_ID)
                                         REFERENCES EMPLOYEE_TBL(EMPL_ID),
             CONSTRAINT     PYRL_STOR_FK FOREIGN KEY (STOR_ID)
                                         REFERENCES STORE_TBL(STOR_ID));

As you can see, it contains a table name with many columns.

Each column has a column name, a data type, and several column constraints.
The column constraints include NOT NULL and DEFAULT.

Several named table constraints are followed by one PRIMARY KEY and two FOREIGN KEYs.

Many people use SQL to retrieve and update data in an existing database for their company. When you start doing database design, you find that a lot of time is spent laying out and normalizing the tables as well as defining indices, relationships, and constraints. This is normally done on paper and goes through a number of revisions before the SQL code is written. The study of database design is extensive and goes beyond the content of this course.Object maintenance is done using the SQL subset called Data Definition Language, or DDL, which includes the commands CREATE, ALTER, and DROP. See SQL is DML, DDL, & DCL.

For now we will concentrate on table creation. When we create a table, we need to define the:

The table and column names must conform to the naming standards of your company and SQL.
The data types are for specifying the type (character, numeric, etc.) and the length of the data.
The default value is for what values will be used if no value is given.
The constraints give limits to the values, such as “Is this a key?,” “Can it be null?,” ” Must this value exist in another table?,” etc.

Here is another sample naming standard:This naming standard uses shorter object names appropriate for a small database on a PC computer. We are using the template SSSEEEOM where:

SSS identifies the application system. Here CUS is for CUSTOMER.

EEE shows the entity within the application such as MST for MASTER, ORD for ORDERS, etc.

O reflects the type of object, such as:
D = Database
S = Schema
T = Table
V = View
X = Index
C = Constraint

M is for multiple occurences.

The CUStomer ORDers Table is CUSORDT.

The CUStomer ORDers inDeXes are CUSORDX1 and CUSORDX2.

Here is a sample naming standard:This naming standard uses the long object names of the SQL standard.

Prefixes:
D_ = Database
C_ = Catalog
S_ = Schema
No prefixes on lower higherarchy objects.

Suffixes:
_TBL = Table
_VIEW = View
_IDX1 = Indexes
_ID = Column definition of a key
_PK = Primary key
_FK1 = Foreign keys
_CK1 = Constraints
_UNQ = UNIQUE
_CHK = CHECK
…