The primary key in a table is a specific column that uniquely identifies a row. Each row must have a primary key, and that key must be unique to that row. Most tables will probably have a column labeled ‘id’, but it is not limited to that name. In fact, a primary key can be made up of two or more columns that combined are unique (Composite Key). Keep in mind that this style can raise complications in database design and will create what are called partial dependencies which lower the tables normal form and decrease the tables efficiency in larger scale data warehousing. So with that in mind, the primary identifier should be limited to a single column.
You can explicitly declare a column in a table as a primary key during the creating process. For most database engines, this is simply done by adding the constraint PRIMARY KEY to the column.
CREATE TABLE (
<column1> <datatype>(<length>) PRIMARY KEY
);
In Oracle, Constraints act as their own objects instead of attributes of a column. As such, each constraint has its own name and association to a column. The most common way to declare constraints is as an “Out-Of-Line” declaration, meaning after all of the other columns have been specified.
CREATE TABLE (
<column1> <datatype>(<length>),
CONSTRAINT <column1>_pk PRIMARY KEY (<column1>)
);
As a shortcut, you can also specify the constraint “In-Line”, meaning in the same line as a column declaration (before its concluding comma).
CREATE TABLE (
<column1> <datatype>(<length>) CONSTRAINT <column1>_pk PRIMARY KEY
);
If you do not specify the CONSTRAINT keyword and exempt the name, the database will assign a generic constraint name in the format of “SYS_Cn”.
CREATE TABLE (
<column1> <datatype>(<length>) PRIMARY KEY
);
Constraints are attributes that you can add to a column to force the data to behave a certain way. As and example the UNIQUE constraint forces all data within that column to not contain duplicates. Another example is NOT NULL which will make the table refuse to accept new data unless that column has some type of value in it. Constraints can also be used to mark a column as a specific identifier. If the keyword pair PRIMARY KEY was added at the end of a column, the database would know to treat that column as the primary identifier for the table.
| Attribute | Description |
|---|---|
| PRIMARY KEY | This defines the column as the primary key for the table. |
| UNIQUE | This forces the table to refuse inserted or modified rows if their value matchs an existing value in the column. |
| NOT NULL | This forces the table to refuse inserted or modified rows if the column does not contain a value. |
| AUTO INCREMENT | For whole number numerical data types (such as INT, SMALLINT, BIGINT, etc.), this will automatically add a value to the column upon inserting one or more rows as (the last inserted value + 1). |
| BINARY | For CHAR, VARCHAR and TEXT types, this converts the character collation into binary collation, meaning that the database will store and reference the values as a binary string instead of a character string. Essentially it converts them into BINARY, VARBINARY and BLOB. |
| UNSIGNED | For Numerical data types (such as INT,DECIMAL,FLOAT,DOUBLE,etc.) this will shift negative values into positive ones. Essentially it restricts the input of negative numbers. This will in turn increase the range of accepted positive values. |
| UNSIGNED ZEROFILL | For Numerical data types, this will follow the same rules as the UNSIGNED attribute, but it will also pad the displayed value with leading zeros according to the length defined. The zeros have no effect on the data stored, only how the data is displayed. |
| ON UPDATE CURRENT_TIMESTAMP | For TIMESTAMP and DATETIME types, whenever a table row is updated (without an explicit timestamp), the CURRENT_TIMESTAMP is updated as well. So the new data will be updated with the new time. |
You can specify a default when creating a column. The default value is inserted automatically when creating new rows. The DEFAULT value must be a constant value and cannot be represented as a function or an expression. If the column accepts NULL (meaning NOT NULL is not defined), then NULL can be assigned as DEFAULT, otherwise (unless explicitly defined) the DEFAULT value will be assigned according to the type.
| Default Value | Description |
|---|---|
| As Defined | The DEFAULT value can be of any data type so long as it matches the type of the column. |
| NULL | By default, new rows will have the value of NULL unless otherwise specified. |
| CURRENT_TIMESTAMP | By default, new rows will have the value of the current date and time. |