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.
我正在使用SQL Server 2008作为使用VS 2012开发的Web应用程序。
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.
目前,这就是我的数据库设计方式。经过研究,似乎所有的talbe都必须有一个唯一的主键来识别行。不幸的是,我的一些表似乎不需要主键,因为我的外键足以作为唯一键。
For example, I have 5 tables.
例如,我有5个表。
- MemberReport
- AdminAssign
- PoliceReport
- AdminOfficialReport
- 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.
我的memberreport表有一个名为memberreportID的主键,我可以将它作为我数据库中所有5个表的通用主键。
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).
我的adminassign表有memberreportID作为外键,经过大量研究后,我意识到几乎所有人都提到了表必须有一个主键。也许像ID(唯一值)。
However, in my opinion, memberreportID can basically be used as a unique key (primary key) for all the 5 tables that I have.
但是,在我看来,memberreportID基本上可以用作我拥有的所有5个表的唯一键(主键)。
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?
我的所有5个表都需要memberreportID作为其外键。因此,我想问我是否可以使用我的外键作为我所有其他表的主键以及如何操作?
2 个解决方案
#1
3
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.
请注意,这意味着您必须先在第一个表中添加该行,然后才能在第二个表中添加该行。
#2
0
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.
主键和外键是两种类型的约束,可用于在SQL Server表中强制实施数据完整性。这些是重要的数据库对象。
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.
表通常具有列或列组合,其中包含唯一标识表中每一行的值。此列或列称为表的主键(PK),并强制实施表的实体完整性。由于主键约束保证唯一数据,因此它们通常在标识列上定义。为表指定主键约束时,数据库引擎会通过自动为主键列创建唯一索引来强制实施数据唯一性。当在查询中使用主键时,此索引还允许快速访问数据。如果在多个列上定义主键约束,则值可能在一列中重复,但主键约束定义中所有列的每个值组合必须是唯一的。
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.
外键(FK)是列或列的组合,用于在两个表中的数据之间建立和实施链接,以控制可以存储在外键表中的数据。在外键引用中,当包含一个表的主键值的一列或多列由另一个表中的一列或多列引用时,将在两个表之间创建链接。此列成为第二个表中的外键。
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.
我猜一个给定的memberreportID将在MemberReport表中出现一次,但可以在其他表中多次出现。在MemeberReport表中将memberreportID定义为PK将确保该表中ID的唯一性。在其他表中将其定义为FK将确保ID存在于MemberReport表中,然后才能存在于任何其他表中。
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.
在所有表中将其定义为PK将确保给定ID在每个表中只能存在一次。如果您的数据属于这种情况,那么您可以使用与PK和FK相同的列。但是,如果是这种情况,我觉得你的数据库设计可能需要重新审视。
Why don't you post up the table structures and some sample data?
你为什么不张贴表格结构和一些样本数据?
Raj
#1
3
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.
请注意,这意味着您必须先在第一个表中添加该行,然后才能在第二个表中添加该行。
#2
0
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.
主键和外键是两种类型的约束,可用于在SQL Server表中强制实施数据完整性。这些是重要的数据库对象。
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.
表通常具有列或列组合,其中包含唯一标识表中每一行的值。此列或列称为表的主键(PK),并强制实施表的实体完整性。由于主键约束保证唯一数据,因此它们通常在标识列上定义。为表指定主键约束时,数据库引擎会通过自动为主键列创建唯一索引来强制实施数据唯一性。当在查询中使用主键时,此索引还允许快速访问数据。如果在多个列上定义主键约束,则值可能在一列中重复,但主键约束定义中所有列的每个值组合必须是唯一的。
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.
外键(FK)是列或列的组合,用于在两个表中的数据之间建立和实施链接,以控制可以存储在外键表中的数据。在外键引用中,当包含一个表的主键值的一列或多列由另一个表中的一列或多列引用时,将在两个表之间创建链接。此列成为第二个表中的外键。
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.
我猜一个给定的memberreportID将在MemberReport表中出现一次,但可以在其他表中多次出现。在MemeberReport表中将memberreportID定义为PK将确保该表中ID的唯一性。在其他表中将其定义为FK将确保ID存在于MemberReport表中,然后才能存在于任何其他表中。
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.
在所有表中将其定义为PK将确保给定ID在每个表中只能存在一次。如果您的数据属于这种情况,那么您可以使用与PK和FK相同的列。但是,如果是这种情况,我觉得你的数据库设计可能需要重新审视。
Why don't you post up the table structures and some sample data?
你为什么不张贴表格结构和一些样本数据?
Raj