What is the purpuse of primary key and foreign key?

[ZAMBER25]

Hi all. i wonder what is the purpuse of primary key and foreign key? could any one explain it to me as i am new to sql world. Furthermore, at what stage should we define the primary key and forgign key? After creating tables ? What is best practice. I be happy if explain these in the sql server 2000 environment.Thanks

 

[fredericofonseca]

http://www.sql.org

and

http://www.microsoft.com

for more specific details regarding SQL Server

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[juliane26]

primary key is like the primary criteria for your table: for an employee it can be the employee number. It is UNIQUE. Usually it is also not subject to changes.
Primary key always comes with an index. It is rather often a search criteria and it should be most often the join criteria.

A foreign key is like a reference. So your employee is in a department. Of course you have a different table here specifying all details about departments (name, manager etc). So in you employee table for each employee you have a reference, a department number.
To ensure the department which number you assigned, actually exists - this can be done using foreign keys. So nobody can be assigned to non-existing departments. Also: departments cannot be deleted while employee exists (this can be specified in the so called delete-rules)
btw, the department number itself is primary key for a department table.



Juliane

 

[ZAMBER25]

juliane Many thanks for u your detailed explaination.should we create the primary key an forgine key when we create table statements or we can do it after creatation of tables in db? How we can define the delte rules for example in sql server 2k?Thanks

 

[fredericofonseca]

I presume you have gone to the links I mentioned.

If so you will find some very usefull info about what you are asking.

As for defining the primary/foreign keys at create stage or after will depend on you own decision.

Either way is correct, but some shops (mine included) will have as a standard that constraints are created OUTSIDE the create table statement.

As for the delete rules on SQL Server 2000,

http://www.microsoft.com

will have the full syntax for what you need, as does books online which is supplyed with EVERY copy of SQL Server 2000. Please do read it, and if you have a particular question related to an individual bit of the definition please come back to us with the sample code you are trying.

Note that not everyone believes in allowing automated deletes, and prefer to use triggers or otherwise SQL code to perform the same function. Once again it may be on your shop standards.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[juliane26]

Since I am not 100% sure what you intend to do, go and check here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_8jtx.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp

        | [ [ FOREIGN KEY ] 
            REFERENCES ref_table [ ( ref_column ) ] 
            [ ON DELETE { CASCADE | NO ACTION } ] 
            [ ON UPDATE { CASCADE | NO ACTION } ] 
            [ NOT FOR REPLICATION ] 
            ]

many other options are documented there.


Juliane