Create Table with Foreign Keys

How do I create a table with a foreign keyCREATE TABLE Parent ( ParentId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ColA INT)

CREATE TABLE Child(ChildId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ParentId INT NOT NULL CONSTRAINT FK_Child_Parent1 FOREIGN KEY REFERENCES Parent(ParentId))|||also a foreign key has the following options

FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]

FOREIGN KEY...REFERENCES

Is a constraint that provides referential integrity for the data in the column or columns. FOREIGN KEY constraints require that each value in the column exists in the corresponding referenced column(s) in the referenced table. FOREIGN KEY constraints can reference only columns that are PRIMARY KEY or UNIQUE constraints in the referenced table or columns referenced in a UNIQUE INDEX on the referenced table.

ref_table

Is the name of the table referenced by the FOREIGN KEY constraint.

(ref_column[,...n])

Is a column, or list of columns, from the table referenced by the FOREIGN KEY constraint.

ON DELETE {CASCADE | NO ACTION}

Specifies what action takes place to a row in the table created, if that row has a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.

If CASCADE is specified, a row is deleted from the referencing table if that row is deleted from the parent table. If NO ACTION is specified, SQL Server raises an error and the delete action on the row in the parent table is rolled back.

For example, in the Northwind database, the Orders table has a referential relationship with the Customers table. The Orders.CustomerID foreign key references the Customers.CustomerID primary key.

If a DELETE statement is executed on a row in the Customers table, and an ON DELETE CASCADE action is specified for Orders.CustomerID, SQL Server checks for one or more dependent rows in the Orders table. If any, the dependent rows in the Orders table are deleted, as well as the row referenced in the Customers table.

On the other hand, if NO ACTION is specified, SQL Server raises an error and rolls back the delete action on the Customers row if there is at least one row in the Orders table that references it.

ON UPDATE {CASCADE | NO ACTION}

Specifies what action takes place to a row in the table created, if that row has a referential relationship and the referenced row is updated in the parent table. The default is NO ACTION.

If CASCADE is specified, the row is updated in the referencing table if that row is updated in the parent table. If NO ACTION is specified, SQL Server raises an error and the update action on the row in the parent table is rolled back.

For example, in the Northwind database, the Orders table has a referential relationship with the Customers table: Orders.CustomerID foreign key references the Customers.CustomerID primary key.

If an UPDATE statement is executed on a row in the Customers table, and an ON UPDATE CASCADE action is specified for Orders.CustomerID, SQL Server checks for one or more dependent rows in the Orders table. If any exist, the dependent rows in the Orders table are updated, as well as the row referenced in the Customers.

Alternately, if NO ACTION is specified, SQL Server raises an error and rolls back the update action on the Customers row if there is at least one row in the Orders table that references it.

Also you should know
FOREIGN KEY Constraints
When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column; otherwise, a foreign key violation error message is returned.

FOREIGN KEY constraints are applied to the preceding column unless source columns are specified.

FOREIGN KEY constraints can reference only tables within the same database on the same server. Cross-database referential integrity must be implemented through triggers. For more information, see CREATE TRIGGER.

FOREIGN KEY constraints can reference another column in the same table (a self-reference).

The REFERENCES clause of a column-level FOREIGN KEY constraint can list only one reference column, which must have the same data type as the column on which the constraint is defined.

The REFERENCES clause of a table-level FOREIGN KEY constraint must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list.

CASCADE may not be specified if a column of type timestamp is part of either the foreign key or the referenced key.

It is possible to combine CASCADE and NO ACTION on tables that have referential relationships with each other. If SQL Server encounters NO ACTION, it terminates and rolls back related CASCADE actions. When a DELETE statement causes a combination of CASCADE and NO ACTION actions, all the CASCADE actions are applied before SQL Server checks for any NO ACTION.

A table can contain a maximum of 253 FOREIGN KEY constraints.

FOREIGN KEY constraints are not enforced on temporary tables.

A table can reference a maximum of 253 different tables in its FOREIGN KEY constraints.

FOREIGN KEY constraints can reference only columns in PRIMARY KEY or UNIQUE constraints in the referenced table or in a UNIQUE INDEX on the referenced table.|||also a foreign key has the following options

FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
..........
If you are using SQL Server 2005 you would do well to read the BoL entry as there are now more options than those above - more in line with the sql standard.|||Hopefully Nick won't be copying & Pasting the BOL entrys into this thread as well - lol

GW|||I am finishing up some handy dadndy little sql to find all of the foriegn keys without an index.

I need to find something better to do with my time.|||Hopefully Nick won't be copying & Pasting the BOL entrys into this thread as well - lol

GW

no i wont... i guess the previous one was enoughsql