Table Editor tab - Constraints

On the Constraints tab, you can manage primary and foreign keys, unique and check constraints, and indexes.

Constraints grid

Right-click the grid and select the required option:

• Add Check Constraint to create a CHECK constraint.
• Add Foreign Key to create a foreign key.
• Add Primary Key to create a primary key.
• Add Unique Key to create a unique key.
• Remove Constraint to delete the selected constraint. Alternatively, press Ctrl+Del.

Warning

If a primary key is already set to a column, the Add Primary Key option is disabled.

Check constraint editor

The table describes the available settings.

Name	Description
Name	Specify the check constraint name.
Description	Specify the check constraint description.
Condition	Define a condition for a check constraint.
Enabled	When a check constraint is enabled, the database validates new or updated data against the condition, rejecting rows that don’t meet the condition. When a check constraint is disabled, no validation is performed, and data that doesn’t meet the condition can be inserted or updated without error. By default, a check constraint is enabled.
Not for Replication	Disable or enable a check constraint whenever a replication operation occurs on this table.
Check Existing Data	Ensures the constraint is checked and enforced on data that existed in the table before the constraint was added. By default, the option is selected.

Foreign key editor

The table describes the available settings.

Name	Description
Name	Specify the foreign key name.
Description	Specify the foreign key description.
Delete Rule	Specify the action to take when attempting to delete a row with data that is involved in a foreign key relationship. The options are NO ACTION, RESTRICT, CASCADE, or SET NULL.
Update Rule	Specify the action to take when attempting to update a row with data that is involved in a foreign key relationship. The options are NO ACTION, RESTRICT, CASCADE, or SET NULL.
Referenced Schema Name	Select a schema in which the referenced table or object resides. To update a list of schemas, click Refresh. Shortcut: Ctrl+R
Referenced Table Name	Select a parent table to which a foreign key points. To update a list of tables, click Refresh. Shortcut: Ctrl+R
Enabled	When a foreign key is enabled, the database checks that every value of the foreign key column from the child table matches a value in the parent table. If not, it rejects any insert or update operations that violate this rule. When a foreign key is disabled, the constraint still exists but is not enforced. You can insert or update rows with values that don’t exist in the parent table. By default, a foreign key is enabled.
Not for Replication	Disable or enable a foreign key whenever a replication operation occurs on this table.
Check Existing Data	Enforce the foreign key on existing data that was present in the table before the foreign key was added. By default, the option is selected.

Constraint columns

Name	Description
Name	Choose the constraint column.
Data Type	Indicates a data type of the constraint column.
Not Null	Indicates whether the constraint column is NOT NULL.
Remove	Removes the selected constraint column. Alternatively, select Remove Column from the shortcut menu. Shortcut: Ctrl+Del
Move up	Moves the selected column up. Alternatively, select Move Column Up from the shortcut menu. Shortcut: Ctrl+Up
Move down	Moves the selected column down. Alternatively, select Move Column Down from the shortcut menu. Shortcut: Ctrl+Down

Referenced columns

Name	Description
Name	Choose the referenced column.
Data Type	Indicates a data type of the referenced column.
Not Null	Indicates whether the referenced column is NOT NULL.
Remove	Removes the selected referenced column. Alternatively, select Remove Column from the shortcut menu. Shortcut: Ctrl+Del
Move up	Moves the selected column up. Alternatively, select Move Column Up from the shortcut menu. Shortcut: Ctrl+Up
Move down	Moves the selected column down. Alternatively, select Move Column Down from the shortcut menu. Shortcut: Ctrl+Down

Primary and unique key editors

General

The table describes the general settings.

Name	Description
Name	Specify the primary/unique key name.
Description	Specify the primary/unique key description.
Index Type	Choose an index type for the column: Clustered or Nonclustered.

Constraint columns

The table describes the settings for the constraint columns.

Name	Description
Name	Choose the constraint column.
Data Type	Indicates a data type of the constraint column.
Not Null	Indicates whether the constraint column is NOT NULL.
Remove	Removes the selected constraint column. Alternatively, select Remove Column from the shortcut menu. Shortcut: Ctrl+Del
Move up	Moves the selected column up. Alternatively, select Move Column Up from the shortcut menu. Shortcut: Ctrl+Up
Move down	Moves the selected column down. Alternatively, select Move Column Down from the shortcut menu. Shortcut: Ctrl+Down

Options

The table describes options of the primary/unique key.

Name	Category	Description	Default value
Auto recompute statistics	General	When the option is enabled, SQL Server automatically updates the statistics as data changes.	Enabled
Ignore duplicate values	General	When the option is enabled, SQL Server ignores duplicate rows instead of causing an error.	Disabled
Allow page locks	Locks	When the option is enabled, SQL Server can lock entire pages of data when accessing the primary/unique key. When the option is disabled, SQL Server doesn’t acquire page locks on the index and uses row-level or table-level locks instead. Note: This may lead to blocking or deadlocks if multiple users try to access rows on the same page.	Enabled
Allow row locks	Locks	When the option is enabled, SQL Server can lock individual rows in the index or table. When the option is disabled, SQL Server cannot acquire row-level locks for that index and uses page-level or table-level locks instead. Note: This may lead to blocking or deadlocks if multiple users try to access rows on the same page.	Enabled
Maximum degree of parallelism	Operation	Controls how many CPU cores SQL Server can use in parallel to build or rebuild that index.	0
Fill factor	Storage	Specifies the percentage of space to fill on each leaf-level page with data, reserving the remaining space for future inserts or updates.	0
Pad Index	Storage	When the option is enabled, SQL Server applies the FILLFACTOR option to the intermediate-level pages of the index. When the option is disabled, SQL Server fills the intermediate index pages almost fully, with minimal space left for one additional maximum-sized row.
Sort in tempdb	Storage	When the option is enabled, SQL Server uses the tempdb system database for sorting during index creation, after which the completed index is written to the target database. When the option is disabled, SQL Server performs the sort operation within the user database.

Storage

The table describes storage options of the primary/unique key.

Name	Description
Regular data space	Specifies the filegroup where the main table data will be physically stored. To update a list of filegroups, click Refresh. Note: PRIMARY is the default filegroup created with the database unless otherwise specified. Shortcut: Ctrl+R
Partition column	Defines a column that splits the data in multiple partitions, each stored in a different filegroup. When the option is disabled, partitioning is not configured for this table.
Filestream data space	Defines where SQL Server will store FILESTREAM-enabled columns. When it is set to <Default>, it implies that FILESTREAM data (binary large objects, such as documents or images) will use the default storage settings. To update a list of filegroups, click Refresh. Shortcut: Ctrl+R
Text/Image filegroup	Controls where to store TEXT, NTEXT, or IMAGE data types. To update a list of filegroups, click Refresh. Shortcut: Ctrl+R

Fragmentation

The fragmentation settings determine how data is distributed and stored in a database based on the primary/unique key. They include size, leaf-level rows, average row size, maximum and minimum row size, etc.

Note

When you select a constraint in the grid, it is highlighted in the T-SQL preview pane.

Save the changes

An asterisk ( * ) on the table editor tab title indicates that there are unsaved changes.

To save and apply them, on the Table Editor toolbar, select Apply Changes.

Note

Use Ctrl+F to locate column definitions or table settings in the DDL script.