Constraints are defined using the Data Designer or through the CREATE TABLE or ALTER TABLE commands.
Before defining primary and foreign key constraints, decide which column or columns and which tables are best suited for the primary key. The column or columns selected should be the ones that uniquely identify the row from other rows in the table, and the table should be the one where that value is first entered into the database and is unique. Linking columns are generally good candidates for primary, foreign key constraints. For example, in the Concomp sample database, the empid column is used to link data in the Employee table with data in the Transmaster table and data in the Salesbonus table. Since a record is first entered into the Employee table, and when data is entered into the Transmaster or Salesbonus tables a matching record must already exist in the Employee table, the empid column in Employee becomes the primary key. The empid columns in Transmaster and Salesbonus become foreign keys referencing the Employee table primary key.
The direct benefit gained from designating empid as a primary key in the Employee table and foreign keys in the Transmaster and Salesbonus tables is the protection for your data from inadvertent changes. The empid value in Employee cannot be changed when there is a matching row in the Transmaster or Salesbonus table. Rows cannot be deleted from either table. When adding data to Transmaster or Salesbonus you automatically require a matching value in Employee without having to define a rule.
The Data Designer
Launching the Data Designer to defined constraints can be performed directly within the Database Explorer window. Select the "Tables" option from the Group Bar to view the available options. Then, highlight a table, and select "Design Table...".
(Note: With an existing table selected, all of the above selections become enabled.)
The "Columns" option will display the defined columns for the table, where the "Not NULL" constraint can be enabled.
The "Key/Indexes" option allows users to create primary key, foreign key, unique key, and unique index table constraints. The "New Key" option allows users to create a new key/index
When creating a new key, the "Select Columns" dialog appears first allowing you to select the column(s) and sorting order (Ascending/Descending) before defining the key.
The "New Key/Index" dialog will display allowing for the creation of a primary key, foreign key, unique key, or index. If the Data Designer is launched for a table where a primary key is already defined, the option to specify a "Primary Key" type will be disabled. When adding a Primary Key, the options for "Not Null" and "Unique" are disabled by default and cannot be altered.
Custom constraint violation error messages can be specified for when an attempt is made to compromise the table's primary key referential integrity.
When adding a Unique Key, the options for "Not Null" and "Unique" are disabled by default and cannot be altered. Custom constraint violation error messages can be specified for when an attempt is made to compromise the table's unique key referential integrity.
When adding a Foreign Key, the options for to specify the referenced table and primary key is available. Along with a primary key, a unique key cal also be referenced by a foreign key. Custom constraint violation error messages can be specified for when an attempt is made to compromise the table's foreign key referential integrity.
When adding a Index, the options for "Not Null" and "Unique" are optional. By enabling the "Unique"setting, the index will be defined as a unique index. A custom constraint violation error message can be specified for when an attempt is made to enter a non-unique value.
Commands
Defining a primary key or unique key constraint with the CREATE TABLE or ALTER TABLE commands requires the column be explicitly defined as NOT NULL as well as the unique or primary key designation. For example,
CREATE TABLE tblname (colname datatype NOT NULL PRIMARY KEY, ...)
Below are examples of the ALTER TABLE command to create a primary key for the Employee table, and to create a foreign key for the Employee table that references the Titles table.
ALTER TABLE `Employee` ADD PRIMARY KEY +
(`EmpID` ) +
('There must be a unique id number per employee.',+
'You cannot delete an employee id that is being referenced in another table.',+
'Cannot change employee id value that is referenced in another table.')
ALTER TABLE `Employee` ADD FOREIGN KEY +
( `EmpTID` )+
REFERENCES `Titles` +
('You must enter an employee id number that exists in the Titles table.',+
'You cannot update the title id to this value, use a value that exists in +
the Titles table.')
The following example displays the CREATE INDEX command to create a unique index.
CREATE UNIQUE INDEX TUII ON `TInvoiceHeader` (`TTransID` ASC )
