November 5, 2007 at 6:02 am
Hi Experts,
I have read an article about 'Document Key',Which denotes PrimaryKey and Foreign Key. In that article article they told how to create Document Key using sp_primarykey & sp_foreignkey , but they didn't explained the uses of it.
Can any experienced experts tell me this complete story ?
karthik
November 5, 2007 at 3:42 pm
Why don't you link to the article so we can see what it is you are talking about?
"Document key" being comprised of a pk and fk sounds either like something the author made up (nothing wrong with that, but we'd need context to see the purpose he/she had in mind), or a vertical market term which I've never heard.
November 6, 2007 at 2:32 am
URL : http://msdn2.microsoft.com/en-us/library/aa215533(SQL.80).aspx
if you see the sqlserver6.X side from the table , you can see the below sentence
'Several system stored procedures were used for documenting keys'.
I am talking about documenting keys.
Another URL :
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarsqlsg/html/msdn_sqlport.asp
Below definition is taken from the above URL.
-------------------------------------------------------------------------------------
.
.
.
.
.
Primary and Foreign Keys
A primary key consists of one or more fields that uniquely identify a record in a table. A foreign key is one or more fields that refer to a primary key in another table. In our example, consider the Author and Article tables, and their primary and foreign keys:
CREATE TABLE Author
(AuthorID PKID,
Name StdDescription)
exec sp_primarykey Author, AuthorID
CREATE TABLE Article
(ArticleID PKID,
ArticleType TypeCode,
AuthorID PKID,
Title StdDescription NULL)
exec sp_primarykey Article, ArticleID
exec sp_foreignkey Article, Author, AuthorID
Here we have created the two tables, declared a primary key on each, and declared a foreign key in the Article table. Unfortunately, defining these keys does not mean that the database will enforce them! In version 4.21, primary and foreign key declarations are for documentation purposes only; calling sp_primarykey and sp_foreignkey simply creates entries in the syskeys table and Microsoft SQL Server does nothing more with them. To enforce the primary keys, a unique index must be created:
CREATE UNIQUE CLUSTERED INDEX XPKAuthor
ON Author (AuthorID)
In order to enforce the foreign key, several triggers must be created:
Table Operation Trigger
Author Delete tD_Author
Author Update tU_Author
Article Insert tI_Article
Article Update tU_Article
Each trigger must check whether the requested operation violates the foreign key relationship. If it does, the trigger will roll back the transaction and raise an error message.
This multistep process of declaring and enforcing referential integrity is one reason why referential integrity breakdowns are so common in version 4.21 databases: Tables may be defined with primary and foreign key declarations but without unique indexes and triggers to actually enforce the relationship rules.
Beginning with Microsoft SQL Server version 6.0, triggers are no longer needed to enforce foreign key relationships, and unique indexes are not required to enforce primary keys. In their place, ANSI-standard attribute constraints are introduced. Consider the updated declarations for the Author and Article tables:
CREATE TABLE Author
(AuthorID PKID IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Name StdDescription NULL)
CREATE TABLE Article
(ArticleID PKID IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ArticleType TypeCode,
AuthorID PKID FOREIGN KEY (AuthorID) REFERENCES Author(AuthorID),
Title StdDescription NULL)
Table definitions in versions 6.0 and 6.5 also allow ANSI-standard DEFAULT and CHECK constraints, which can replace the old-style defaults and rules. Using defaults and rules required that the defaults and rules first be created and then bound to either a specific column or to a user-defined datatype. The ANSI-standard DEFAULT and CHECK constraints simplify this process considerably. Note that the version 4.21 rule syntax and default syntax are still supported for backward compatibility. They can still be useful if you want to define these constraints for a user-defined datatype rather than defining them column by column.
By defining primary and foreign keys in the table declaration, we eliminate the need for the calls to sp_primarykey and sp_foreignkey. Unique indexes need no longer be explicitly declared on the primary key attributes, and we can do away with the triggers—the database enforces the relationships automatically. That's a lot of code that we can eliminate. Less code means less maintenance and fewer chances to forget to build an object or relationship when recreating the database.
.
.
.
.
...
....
......
-------------------------------------------------------------------------------------
My Question is :
I have two tables called Table A and Table B.
Table A -> primary key is created by using sp_primarykey TableA,Eno
Table B -> Foreign Key is created by using sp_foreignkey TableB,TableA,Eno
As per the above definition sp_primarykey and sp_foreignkey just document the keys in syskeys table. Actually,It doesnt enforce the integrity.So if i insert like below it could be accepted in the both table.
Table A :
Eno
1
2
3
4
5
Table B :
10
20
30
40
Because Table B doesnt have the actual foreign key. Again as per the above one we need to create trigger to enforce referential integrity. OK ! let us come to the point ,assume i am upgrading from sqlserver6.X to sqlserver7 or above.
As per the first URL , Sqlserver7.x and above versions are not supporting these keys.
Suppose if i create actual primary key and foreign key relationship before inserting data's into TableA and TableB in Sqlserver7.x or above version, i am sure it should throw an error message.
How to handle this situation ? What this keys does in olden days ? what it doesn't doing now a days ?
What triggered in their mind at the time of this key creation ? why they dropped this key now ?
:hehe:
karthik
November 6, 2007 at 10:57 am
I think I see the confusion. That article isn't a highly specific article about the objects called "documenting keys", it's a very broad article about migrating from one version of SQL Server (which I haven't seen in forever) to a slightly less ancient version of SQL Server. It's a bunch of different areas covered that you seem to have rolled up into a single abstract concept in your head.
They're aren't discussing an object known as a "documenting key", they are discussing the documenting of database objects, both abstract and literal, which happens to include keys, as in the act of documenting the object known as a "key".
November 6, 2007 at 12:07 pm
Gotta tell you - I'm feeling a bit let down... With a title like that - I was hoping for something in a fairly tale format....
"Daddy - could you tell us the one about the Primary Key and the Foreign Key again? PLEEEEEEEASE?"
...must be time to renew my cafeine drip....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 7, 2007 at 12:48 am
Please try to understand my question !
My Question is:
I have two tables called Table A and Table B.
Table A -> primary key is created by using sp_primarykey TableA,Eno
Table B -> Foreign Key is created by using sp_foreignkey TableB,TableA,Eno
As per the above definition sp_primarykey and sp_foreignkey just document the keys in syskeys table. Actually,It doesnt enforce the integrity.So if i insert like below it could be accepted in the both table.
Table A :
Eno
1
2
3
4
5
Table B :
10
20
30
40
Because Table B doesnt have the actual foreign key. Again as per the above one we need to create trigger to enforce referential integrity. OK ! let us come to the point ,assume i am upgrading from sqlserver6.X to sqlserver7 or above.
As per the first URL , Sqlserver7.x and above versions are not supporting these keys.
Suppose if i create actual primary key and foreign key relationship before inserting data's into TableA and TableB in Sqlserver7.x or above version, i am sure it should throw an error message.
How to handle this situation ? What this keys does in olden days ? what it doesn't doing now a days ?
What triggered in their mind at the time of this key creation ? why they dropped this key now ?
karthik
November 9, 2007 at 2:29 am
Experts,
Any Input ?
karthik
November 26, 2007 at 3:24 am
Expecting some good answers from veteran developer.
karthik
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply