PowerShell - Add-ForeignKey

Adds a foreign key to an existing table that doesn't have a foreign key constraint.

Syntax

Add-ForeignKey [-TableName] <String> [-SchemaName <String>] [-ColumnName] <String[]> [-References] <String> [-ReferencesSchema <String>] [-ReferencedColumn] <String[]> [-OnDelete <String>] [-OnUpdate <String>] [-NotForReplication] [-Name <String>] [-NoCheck] [<CommonParameters>]

Description

Adds a foreign key to a table. The table/column that the foreign key references must have a primary key. If the table already has a foreign key, make sure to remove it with Remove-ForeignKey.

Related Commands

Parameters

Name	Type	Description	Required?	Pipeline Input	Default Value
TableName	String	The name of the table to alter.	true	false
SchemaName	String	The schema name of the table. Defaults to `dbo`.	false	false	dbo
ColumnName	String[]	The column(s) that should be part of the foreign key.	true	false
References	String	The table that the foreign key references	true	false
ReferencesSchema	String	The schema name of the reference table. Defaults to `dbo`.	false	false	dbo
ReferencedColumn	String[]	The column(s) that the foreign key references	true	false
OnDelete	String	Specifies what action happens to rows in the table that is altered, if those rows have a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.	false	false
OnUpdate	String	Specifies what action happens to rows in the table altered when those rows have a referential relationship and the referenced row is updated in the parent table. The default is NO ACTION.	false	false
NotForReplication	SwitchParameter	Can be specified for FOREIGN KEY constraints and CHECK constraints. If this clause is specified for a constraint, the constraint is not enforced when replication agents perform insert, update, or delete operations.	false	false	False
Name	String	The name for the	false	false
NoCheck	SwitchParameter	Specifies that the data in the table is not validated against a newly added FOREIGN KEY constraint. If not specified, WITH CHECK is assumed for new constraints.	false	false	False

Name

Type

Description

Required?

Pipeline Input

Default Value

TableName

String

The name of the table to alter.

true

false

SchemaName

String

The schema name of the table. Defaults to dbo.

false

dbo

ColumnName

String[]

The column(s) that should be part of the foreign key.

true

false

References

String

The table that the foreign key references

true

false

ReferencesSchema

String

The schema name of the reference table. Defaults to dbo.

false

dbo

ReferencedColumn

String[]

The column(s) that the foreign key references

true

false

OnDelete

String

Specifies what action happens to rows in the table that is altered, if those rows have a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.

false

OnUpdate

String

Specifies what action happens to rows in the table altered when those rows have a referential relationship and the referenced row is updated in the parent table. The default is NO ACTION.

false

NotForReplication

SwitchParameter

Can be specified for FOREIGN KEY constraints and CHECK constraints. If this clause is specified for a constraint, the constraint is not enforced when replication agents perform insert, update, or delete operations.

false

False

Name

String

The name for the

false

NoCheck

SwitchParameter

Specifies that the data in the table is not validated against a newly added FOREIGN KEY constraint. If not specified, WITH CHECK is assumed for new constraints.

false

False

EXAMPLE 1

Adds a foreign key to the 'Cars' table on the 'DealerID' column that references the 'DealerID' column on the 'Dealer' table.

EXAMPLE 2

Adds a foreign key to the 'Cars' table on the 'DealerID' column that references the 'DealerID' column on the 'Dealer' table with the options to cascade on delete and update, and also set notforreplication

EXAMPLE 3

Adds a foreign key to the 'Cars' table on the 'DealerID' column that references the 'DealerID' column on the 'Dealer' table without validating the current contents of the table against this key.