Primary Key Such type of candidate key which is chosen as a primary key for table is known as primary key. Primary keys are used to identify tables. There is only one primary key per table. In SQL Server when we create primary key to any table then a clustered index is automatically created to that column. Foreign key is a field in the table that is primary key in another table. Foreign key can accept multiple null value. Foreign key do not automatically create an index, clustered or non-clustered. Add or change a table’s primary key in Access. It is the primary key. In the Orders table it is called a foreign key. A foreign key, simply stated, is another table's primary key. Often, a unique identification number, such as an ID number or a serial number or code, serves as a primary key in a table. For example, you might. Jan 10, 2006 yes, when a primary key is created an index for that column is automatically created. The reason why there is primary key is to enforce data integrity. If the rules of normalization is always to follow the relationship between tables should co-exist then this is where the primary key and foreign key comes to the picture. The Foreign key is for referential integrity, but doesn’t necessarily have to be, because any uniqueness of a column can connect and you don’t have to have a Primary key either, but cluster indexing (having a Primary key) speeds up the connection, because clustering is directly in memory.
Active4 years, 2 months ago
I have tried searching loads but I have not received a concrete answer to my problem.Suppose I have Table 'A' with primary key as Apk.
This Apk is a foreign key in Table 'B'. In Table 'B' I have also another column Bcol.
Now for 'B' can I have a composite primary key of 'Bcol' and 'Apk': PRIMARY KEY(Bcol, Apk)? Is it advisable?
migrated from stackoverflow.comNov 14 '12 at 12:57
This question came from our site for professional and enthusiast programmers.
Primary Key Foreign Key Relationship
3 Answers
so basically you have something like this (except I renamed your FK table to C since it was already pulling B from somewhere else):
this is completely normal and how you would form many to many relationships between 2 entities. You may wish to have some other auto generated unique identifier as the primary key but its a good idea to have the 2 FKs as a combined unique key to prevent duplicating rows of the same combinations
WebChemistWebChemist
Yes. It depends on your circumstances and preferences. I like to have all my tables have single/noncomposite primary keys. You can see a working example in SQL Fiddle here: http://sqlfiddle.com/#!2/b05df/1
marvinmarvin
This is not possible. The foreign key can not refer to part of composite primary key of other table. Because it is supposed to be one-to-one relationship and if you refer just part of primary-key, there might be more than one parent record available, which is neither allowed nor possible. i.e. Database will not allow you to do something where situation may arise that one child refers to multiple parent in 1-to-1 relation.