2

Building two tables as one-to-one relationship, I would like to consider which one is a better design ? Having the same primary key for the parent table (person) as a primary key also for the child table (profile) or building another primary key for the profile table ?

I'm thinking that having the same primary key for tables are a better choice when joining the two tables because the primary key will be a clustered index on profile table so it won't scan the whole table to get a matching

relational data diagram

table creation statements

3
  • 2
    I see no reason why you cannot reuse the original primary key. This is actually an example of a 1:0-1 relationship, and general practice is to reuse the parent PK. Creating another one just means extra joins are necessary, and increases the size of the table, to no benefit Dec 24, 2021 at 12:47
  • primary key will be a clustered index Do NOT fall into the trap of thinking the primary key must also be the clustered index. You can only have one clustered index - choose it wisely and with understanding and with intention.
    – SMor
    Dec 24, 2021 at 21:34
  • @SMor of course, but I was talking about the default situation where the primary key is the clustered index, thanks Dec 25, 2021 at 8:56

1 Answer 1

6

I agree with Charlieface in regards to being able to use the same Primary Key field in both tables, since this is a one-to-one relationship.

But I would also like to mention, if you had a need to create a separate Primary Key field for your Profile table, and you used a secondary field as the Foreign Key reference to your Person table, that doesn't necessarily mean it'll "scan the whole table to get a matching" when you JOIN by that Foreign Key field. It could (and probably would) use an index seek operation actually, and be just as performant (so long as the secondary field has a nonclustered index on it.).

This is because SQL Server doesn't care if you're using the clustered indexed fields or a nonclustered indexed set of fields to JOIN by. Rather it determines which type of operation to use based on the cardinality of the values for the fields in your JOIN's ON clause. In this context cardinality refers to the number of rows your predicate returns relative to the total number of rows in their respective tables. The cardinality will be the same in your case whether you use the Primary Key field of your Profile table as the same field as your Person table or if you used a second field as such instead.

7
  • thanks, but I didn't understand how the operation will be an index seek if there's no index built on the foreign key even because of uniqueness Dec 24, 2021 at 18:58
  • @Abdalrahmanshebani You can create a nonclustered index on the Foreign Key field. Sorry I thought that was clear enough from my answer. But if you create a nonclustered index, it's possible for a seek to occur on that field.
    – J.D.
    Dec 25, 2021 at 0:54
  • 1
    it's all clear now thanks, so I can do both using the same primary key and it will be a clustered index seek or using another field and make it a non clustered index Dec 25, 2021 at 8:55
  • 1
    Uniqueness is only one aspect of cardinality. Table cardinality is rows in the entire table whether they’re unique or not, etc. Dec 26, 2021 at 15:44
  • 1
    Selectivity is uniqueness. Cardinality is estimates based on a whole bunch of fancy math that uniqueness is one of many inputs for. Dec 26, 2021 at 18:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.