Defining Constraints

Home | Previous Page | Next Page The Table Option > Creating or Altering a Table >

Defining Constraints

You can use the DB–Access Schema Editor to define constraints for columns in a specified table. You can define primary-key, foreign-key, column-level and table-level check, and unique constraints, as well as add and modify column default values.

If you select the Constraints option from the CREATE TABLE menu, the CONSTRAINTS menu appears, as Figure 75 shows.

Figure 75. The CONSTRAINTS Menu

CONSTRAINTS - mytab:    Primary  Foreign Check Unique Defaults Exit
Define a primary key constraint.

-----------------mydata@mydbserv------a-------- Press CTRL-W for Help --------

The CONSTRAINTS menu has the following options.

Option: Purpose
Primary: Lists the columns that make up the table so that you can choose the column or columns that make up the primary key
Foreign: Asserts a foreign-key relationship for a column
Check: Enables you to specify valid values for a column and forces the validation of data entry in that column
Unique: Declares that a column must contain a unique value
Defaults: Enables you to set a default value for a column
Exit: Returns you to the CREATE TABLE menu

Defining Primary-Key Constraints

To add, modify, or delete primary-key constraints for the current table, select the Primary option on the CONSTRAINTS menu. The PRIMARY KEY menu appears, as Figure 76 shows.

Figure 76. The PRIMARY KEY Menu

PRIMARY KEY mytab:   Add  Modify Drop Screen Exit
Add a constraint name or column name.

-------Page 1 of 1 --------mydata@mydbserv------- Press CTRL-W for Help --------

Constraint Name   Column Name

constraint1          column1

When you enter a primary constraint, DB–Access validates your entry by verifying the following information:

The column name is not repeated.
No more than 16 column names appear in one constraint.
The column is not assigned the BYTE or TEXT data type.

Use the menu options as follows:

Use the Screen option to scroll to any additional constraint descriptions so that you can select one to modify or delete.
If you select the Add option to add a primary-key referential constraint, the Schema Editor inserts and highlights a new line at the top of the constraint list. If you move your cursor to an existing constraint line and select the Modify option, you can change the contents of the line.
1. At the ADD (or MODIFY) CONSTRAINT NAME prompt, enter a constraint name.
  If you press RETURN in this field without typing a constraint name, the database server assigns a temporary constraint name, such as unassigned1, unassigned2, and so on. This temporary constraint name exists until you modify it or the table is built or discarded. The database server assigns a permanent constraint name at the time that you create the table.
2. At the ADD (or MODIFY) COLUMN NAME prompt, enter a column name.
Select Drop to eliminate the constraint that the highlight indicates. If the highlighted field is Constraint Name, then all columns associated with that constraint are deleted. The lines are removed and any gaps are closed in the display.

Defining Foreign-Key Constraints

To create, modify, or delete foreign-key constraints for the current table, select the Foreign option on the CONSTRAINTS menu. The FOREIGN KEY menu appears, as Figure 77 shows.

Figure 77. The FOREIGN KEY Menu

FOREIGN KEY mytab:   Add  Modify Drop Screen Exit
Add a constraint name or referencing/referenced column pair.

------Page 1 of 1 -------mydata@mydbserv------ Press CTRL-W for Help --------

Constraint    Referencing Column   Referenced Table  Referenced Column  CD

unassigned0   column1              table2             column1            Y

    column2   column2

    column3   column3

Use the menu options as follows:

Use the Screen option to scroll any additional constraint descriptions onto the screen so that you can select one to modify or delete.
Select the Add option to specify the constraint name, referenced table, and cascading deletes, or to add one or more referencing and referenced column pairs to the constraint.
1. At the ADD CONSTRAINT NAME prompt, enter a constraint name. If you press RETURN in this field without typing a constraint name, DB–Access assigns a temporary constraint name. The database server assigns a permanent name to the constraint when the constraint is created.
2. At the ADD COLUMN NAME prompt, enter the name of the referencing column.
3. At the ADD REFERENCED TABLE prompt, enter the name of the referenced table.
4. At the second ADD COLUMN NAME prompt, enter the name of the referenced column.
5. When the CD (Cascading Deletes) field is the current field, the ADD ENABLE CASCADING DELETES menu appears, as Figure 78 shows.
  Figure 78. The ADD ENABLE CASCADING DELETES Menu
```
ADD ENABLE CASCADING DELETES mytab3:   No  Yes
No, I do not want cascading deletes.

-------- Page 1 of 1 --------mydata@mydbserv------ Press CTRL-W for Help -----

Constraint  Referencing Column   Referenced Table  Referenced Column  CD

cons1         col1                   yourtab              col6          N
```
  Select Yes to enable cascading deletes. When you delete a referenced (parent) record, you also delete all corresponding referencing (child) records. (Option Yes is equivalent to the ON DELETE CASCADE option of the REFERENCES clause in the CREATE TABLE statement.)
  
  Select No to prevent cascading deletes. A referenced (parent) column cannot be deleted if referencing (child) records exist.
  
  For a detailed description of referential integrity and cascading deletes, see the CREATE TABLE statement in the IBM Informix: Guide to SQL Syntax.
When you complete the Cascading Deletes entry, the cursor returns to the Constraint field. Enter another constraint or press the Interrupt key to return to the top line of the FOREIGN KEY menu.
Move your cursor to an existing constraint line and select the Modify option to change the contents of the line. You can modify Constraint, Referencing Column, and Referenced Table information.
Change the entry for the field and press RETURN to modify it, or press the Interrupt key if you do not want to modify a foreign constraint.
The Drop option displays a list of foreign-key constraints and prompts you to select one to delete.
If the current (highlighted) field is Constraint, then the entire constraint is deleted. If any other field is highlighted, then only that referenced and referencing pair is deleted.

The default is Yes. Press RETURN to delete the highlighted constraint. Move the cursor to highlight No if you do not want to delete that constraint. You return to the FOREIGN KEY menu.

Defining Check Constraints

The CHECK CONSTRAINTS menu lets you add, modify, or delete a check constraint for the current table. Select the Check option on the CONSTRAINTS menu to access the CHECK CONSTRAINTS menu, as Figure 79 shows.

Figure 79. The CHECK CONSTRAINTS Menu

CHECK CONSTRAINTS mytab:   Add  Modify Drop Screen Exit
Add a check constraint.

-----------------mydata@mydbserv------------- Press CTRL-W for Help ---------

Constraint Name     Value

cons2              (column1 > (c ...

cons3              column2 < col ...

cons4              column3 > 100

The CHECK CONSTRAINTS menu displays any previously added check constraints. The first 36 characters of the check value appear on the CHECK CONSTRAINTS menu. Use the CHECK CONSTRAINTS menu options as follows:

The Drop option lets you delete the current check constraint.
The default is Yes. Press RETURN to delete the highlighted constraint. Move the cursor to highlight No and then press RETURN if you do not want to delete that constraint. You return to the CHECK CONSTRAINTS menu.
Select the Add option to add a check constraint for the table and enter the name and value of the constraint.
1. At the ADD (or MODIFY) CONSTRAINT NAME prompt, enter a name. If you press RETURN in this field without specifying a value, a temporary constraint name is assigned, and it exists until the table is built or discarded.
2. From the ADD (or MODIFY) CHECK VALUE menu, as Figure 80 shows, select the SQL editor or specify another editor to enter the check-constraint value.
  Figure 80. The ADD CHECK VALUE Menu
```
ADD CHECK VALUE cons99:   New  Modify Use-editor Exit
Enter a new check value using the SQL editor.

----------------mydata@mydbserv------------- Press CTRL-W for Help --------
```

The ADD CHECK VALUE menu has the following options.

Option: Purpose
New: Displays the blank SQL editor screen so that you can enter a new check value
Modify: Displays the current check value on the SQL editor screen so that you can modify the value
Use-editor: Displays the current check value in the system editor so that you can modify the value
Exit: Returns to the CHECK CONSTRAINTS menu

If you break from the ADD CHECK VALUE menu or exit without defining the check value, you return to the CHECK CONSTRAINTS menu. If you defined the check value, you remain in add mode, a new line is inserted, the Constraint Name is the current field, and the ADD CONSTRAINT NAME prompt appears.

Defining Unique Constraints

The UNIQUE CONSTRAINTS menu lets you add, modify, or delete a unique constraint for the current table. To access the UNIQUE CONSTRAINTS menu, select the Unique option on the CONSTRAINTS menu, as Figure 81 shows.

Figure 81. The UNIQUE CONSTRAINTS Menu

UNIQUE CONSTRAINTS mytab:   Add  Modify Drop Screen Exit
Add a unique constraint.

------------------mydata@mydbserv------------ Press CTRL-W for Help ---------

Constraint Name  Column Name

cons2            column1
                 column2
                 column3
cons3            column4

Use the UNIQUE CONSTRAINTS menu options as follows:

Select Screen to display the next screen of unique constraints.
If you select the Add option, the Schema Editor adds a new line.
1. At the ADD CONSTRAINT NAME prompt, enter a constraint name. If you press RETURN in this field without typing a name, a temporary constraint name is assigned, which exists until the table is built or discarded.
2. At the ADD COLUMN NAME prompt, enter the name of the column that should have a unique value. A new line is added, and the Column Name is still the current field.
The Modify option lets you modify either the Constraint Name or Column Name field. If you modify the Constraint Name field, the MODIFY CONSTRAINT NAME prompt appears. If you modify the Column Name field, the MODIFY COLUMN NAME prompt appears.
Select the Drop option to delete the constraint or column name where the highlight is located.
If you delete a constraint name, all column names associated with that constraint name are also deleted.

Important:

You cannot modify unique constraints after you create them. To identify the unique constraints listed on the UNIQUE CONSTRAINTS menu, use an asterisk (*) before the constraint name. If you try to modify a unique constraint using the Modify option in the UNIQUE CONSTRAINTS menu, an error message appears.

Data Validation

When you enter a unique constraint, DB–Access validates your entry by verifying the following information:

The column name exists.
The column name is not repeated.
No more than 16 column names are present in one constraint.
The column is not assigned the BYTE or TEXT data type.

Defining Default Values

Use the DEFAULTS menu to define default values for columns in a table, as Figure 82 shows. To access the DEFAULTS menu, select the Defaults option on the CONSTRAINTS menu.

Figure 82. The DEFAULTS Menu

DEFAULTS mytab:   Add  Modify Drop Screen Exit
Add a column default.

-------Page 1 of 1 --------mydata@mydbserv----- Press CTRL-W for Help -------

Column Name  Type   Value

column1     User

column3     Null

column5     Today

column6     Current (Fraction to Fraction (5))

column7     Literal

column8     Literal   1200

The DEFAULTS menu has the following options.

Option: Purpose
Add: Adds a column default value of the appropriate data type
Modify: Lists an existing default name, data type, and value so that you can change the default attributes
Drop: Deletes a column default
Screen: Displays the next screen of defaults
Exit: Returns to the CONSTRAINTS menu

You can see the first 28 characters of the value.

To add a column default

Select the Add option.
The Schema Editor inserts a new line at the top of the list and makes the Column Name the current field.
At the ADD COLUMN NAME prompt, enter a value for the column name.
From the ADD DEFAULT TYPE menu that Figure 83 shows, define the default value of a column in the current table.

Figure 83. The ADD DEFAULT TYPE Menu

ADD DEFAULT TYPE mytab:   Literal  User Current Null Today Db-server-name Site-name
Assign a literal value using either the SQL editor or a system editor.

---------------mydata@mydbserv---------------- Press CTRL-W for Help ---------

The ADD DEFAULT TYPE menu has the following options that let you assign default values to the column.

Option: Default Value Assigned
Literal: A literal default value entered either in the SQL editor or a user-specified system editor
User: The login name of the current user
Current: The current system clock time of day
Null: Null
Today: The current system date
Db-server-name: The current database server name
Site-name: The current site name

If you select User, Null, Today, Db-server-name, or Site-name, the system assigns that value to the default type and returns you to the DEFAULTS menu in add mode.
If you select Current as the default value, the qualifier is taken from the column definition.
If you enter a default value and the type is DATETIME or INTERVAL, enter only the value. The qualifier comes from the column definition.
If you select Literal as the default type, the ADD DEFAULT VALUE menu appears, which lets you assign a literal as the default value.

The ADD DEFAULT VALUE menu lets you add or modify the default value for a column in the current table with either the SQL editor or a system editor, as Figure 84 shows.

Figure 84. The ADD DEFAULT VALUE Menu

ADD DEFAULT VALUE column7:   New  Modify Use-editor Exit
Enter a new default value using the SQL editor.

-----------------mydata@mydbserv------------- Press CTRL-W for Help ----------

The ADD DEFAULT VALUE menu displays the following options.

Option: Purpose
New: Displays the blank SQL editor screen so that you can enter a new value
Modify: Displays the current default on the SQL editor screen so that you can modify the default value
Use-editor: Displays the current default in the system editor so that you can modify the current value
Exit: Returns to the ADD DEFAULT VALUE menu

To modify the column name, type, or value field

Select the Modify option to modify the Column Name, Type, or Value field where the highlight is located.
If you highlight a value, the MODIFY DEFAULT VALUE menu prompts you to invoke the SQL editor or the system editor so that you can type over or modify the existing default value.

Data Validation

When you enter a default value, DB–Access validates your entry. The database server validates the literal value and checks the following information:

The column name must exist.
The column type cannot be SERIAL.
If the column does not allow nulls, you cannot specify the default type as Null.
You can use the default type Current only with a DATETIME column type.
You can use the default type Db-server-name only with a column type of CHAR, NCHAR, VARCHAR, or NVARCHAR, which has a minimum length of 18 characters.
You can use the default type Site-name only with a column type of CHAR, NCHAR, VARCHAR, or NVARCHAR, which has a minimum length of 18 characters.
You can use the default type Today only with a column type of DATE.
You can use the default type User only with a column type of CHAR, NCHAR, VARCHAR, or NVARCHAR, which has a minimum length of 18 characters.