PK vs FK - Identity

[MicahDB]

This is awfully basic, but I'm pretty new to developing sql server dbs. I've most just maintained them.

I've got a PK on an employeeID column. The data type is Int with Yes in the Identity field. Should the FK be marked as Int with Yes for the Identity field if I want to store multiple records using the employeeID?

Is that clear as mud?

Let me know if I'm just confusing.

Thanks.

 

[SQLSister]

No the foreign key should not be an indentity, itshould take the same value as the field inthe table with the primary key. Let me give you an exapmle

Customers Table
CustomerID int identity,
CustomerName varchar (50)

Orders Table
OrderID int identity
CustomerID int
PartNo varchar (10)
Price money

Data
Customers table
1000 Joe Smith
1001 Jim Smith

Orders
OrderID CustomerID PartNo Price
1000 1000 NJ789 100.00
1001 1000 JKLI 125.98
1002 1001 gy76 34.56
1003 1000 12LX 12.96

As you can see the customerID inthe orders table may be repeated and should match to the customer table data. An identity field is an autonumber , it will put inthe next number of the series automatically and other data cannot be inserted to it (without turning the identity feature off).

Hope this helps.

Questions about posting. See faq183-874

 

[MatthewPeters]

Your FK should be the same data type of the PK that it is related to. The identity property for your FK should be set to NO.

The identity property is similiar to the Auto-Number functionality in Access. As new records are inserted the value of the identity field will be filled automatically.

 

[MicahDB]

Good deal. Thanks a bunch.

Micah