how to make a foreign key a primary key in sql server

Question

How do I make a foreign key as a primary key for my table?

I'm using SQL Server 2008 for my web application developed with VS 2012.

Currently, this is how my database is being designed. After researching, it seems like all talbe must have a unique primary key to identify the rows. Unfortunately, some of my tables doesn't seems to require a primary key as my foreign key is sufficient as a unique key.

For example, I have 5 tables.

1. MemberReport
2. AdminAssign
3. PoliceReport
4. AdminOfficialReport
5. SuperiorOFficialReport

My memberreport table has a primary key called memberreportID which I can very much use it as a universal primary key for all my 5 tables in my database.

My adminassign table has memberreportID as a foreign key and after much research, I realized that almost all of them mentioned that it is a must for a table to have a primary key. Maybe something like an ID (Unique value).

However, in my opinion, memberreportID can basically be used as a unique key (primary key) for all the 5 tables that I have.

All my 5 tables require memberreportID as their foreign key. Hence I would like to ask if I can use my foreign key as a primary key for all my other tables and how to do so?

Answer 1

A column can be both a primary key and a foreign key. For example:

create table t1 
    (
    id int not null
,   constraint PK_T1 primary key (id)
    );

create table t2 
    (
    id int not null
,   constraint PK_T2 primary key (id)
,   constraint FK_T2_ID foreign key (id) references t1(id)
    );

Note that this means that you have to add the row in the first table before you can add it in the second.