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?

Solution 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.

Solution 2

Primary keys and foreign keys are two types of constraints that can be used to enforce data integrity in SQL Server tables. These are important database objects.

A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. Because primary key constraints guarantee unique data, they are frequently defined on an identity column. When you specify a primary key constraint for a table, the Database Engine enforces data uniqueness by automatically creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. If a primary key constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the primary key constraint definition must be unique.

A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table. In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table.

I guess a given memberreportID will appear once in the MemberReport table, but can appear multiple times in other tables. Defining memberreportID as PK in your MemeberReport table, will ensure uniqueness of the ID in that table. Defining it as FK in the other tables will ensure that the ID exists in the MemberReport Table before it can exist in any of the other tables.

Defining it as PK in all the tables, will ensure that a given ID can exist only once in each table. If this is the case with your data, then you can use the same column as PK as well as FK. But, if that is the case, I feel that your DB design might need to be relooked at.

Why don't you post up the table structures and some sample data?

Raj

Comments

[Raj]

This would also mean that T1 and T2 have a one-to-one relationship, right?

[Andomar]

@Raj: To be nitpicky, a one to one-or-zero relation. It's fine to omit a row in the second table.

[Raj]

True. I believe this is not what the OP is asking for. He is talking about a "Universal Primary Key", whatever that means.

[Bryan]

From my description above, it does seems like my tables are all 1 : 1 relationship. However, i'm very sure if it is correct for all 5 tables to have a 1 : 1 r/s since my memberreportID can be used as a primary key for all 5 tables. Because it seems a little wrong to have the same primary key for all 5 tables.