Fixed Value for same Composite Key-CodePudding

I have been trying to create a database in Microsoft SQL Server Management Studio and I am trying to have a fixed value when I am using the same composite key.

To be more clear:

| CrewID | Phone | Email         |
 -------- ------- --------------- 
| 565    | 210   | [email protected] |
| 565    | 212   | [email protected] |
| 565    | 213   | [email protected] |
| 935    | 641   | [email protected] |
| 935    | 646   | [email protected] |

Basically CrewID, Phone is the composite key, allowing multiple phone numbers for the same CrewID, but I want the email to be locked for each unique ID.

So whenever I enter new data, if the ID is the same, auto fill email with the email of the first entry with that specific ID.

CodePudding user response：

I solved it rather quickly. My initial thought was to keep it simple and split the phone and crew tables, but I wanted to try something more advanced. It was not necessary in the end.

I just put PhoneNum (PK), Description, CrewID (FK) on a new table, and kept the Crew table simple with just CrewID (PK) and Email. Now, for each unique CrewID I have a single email, while the Phone table allows me to store multiple phone numbers.