>>Just out of curiosity....
>>
>>SQL Server won't seem to let me do this:
>>
>>Table1: Key = ID
>>Table2: Key = ID = Table1.ID (Only some Table1 records have a Table2 record)
>>Table3: ForeignKey = Table2ID = Table2.ID (Table3's FK must exist in Table2)
>>
>>I have to do this:
>>
>>Table1: Key = ID
>>Table2: Key = ID = Table1.ID
>>Table3: ForeignKey = Table2ID = Table1.ID
>>
>>I can live with the second method... But the first describes the relationship better and seems safer. Attempting to do it the first way results in:
>>
>>The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Table3_Table2ID". The conflict occurred in database "myData", table "dbo.myTable2", column 'ID'.
>>
>>Is that a known limitation of mixing Foreign keys and Primary keys... or am I missing something?
>
>You mean Table2.ID is equal to Table1.Id?
>And you have PK by this field?
>What if you want to put second record in that table?

It's a 1 to 1 or 1 to none relation ship.

It's a bit of an unusual setup.

Table 1 is a self referencing hierarchical data tree, each leaf represents a different piece of data relating to the same root entry. So when a record of type table2 gets added to table1 it will always be a 1 to 1 relationship, since each table2 entry has it's own leaf in table1.

Table 1 is a free form contact record, that represents all the data related to a contact in a parent child type of relationship. A company for instance may have many contacts and even departments, divisions and locations in it. The root would be the company. Information such as an address and phone numbers flow up the tree from the parent records, but can be overridden on the children when it is different, or combined with the child when info relevant. Bit of an odd setup but I like it.

In this specific case Table 2 is a USPS Mailing Permit. Table 3 is a Mailing that uses that Permit. Table2 to Table3 is a 1 to Many relationship.

I originally had a separate Identity field in table 2 that I used in table 3 but that was wasted space since the table 1 ID will always be unique. However... it may be better to go back to that for data integrity reasons.