473,321 Members | 1,708 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Primary Key: I am trying to understand--please help if you can.

Hi all,

So I've been reading the newsgroups, and reading a few books trying to learn
SQL and SQL Server 2000. The books tell me I need a Primary Key, and that
every table should have one. I know (I think) that a Primary Key is a
special field that uniquely identifies each record or row within a table.

My question is this: If I have a field or column whose values are all (and
will be) different in every row, is that what a Primary Key is, or is it not
a primary key until I designate it as such with either a statement or by
right-clicking a little key on the table diagram.

If so, then what is the reason _behind_ why I have to do that, and have a
primary key?

I've been playing with a few tables, and I can select information just fine,
relate tables together with joins, and everything works fine (so far) all
without my designating any Primary Keys--so why, exactly, do I need one, and
exactly what is it?

I would appreciate any help and clarification.

Thanks.
Jul 20 '05 #1
4 1768
Replied in microsoft.public.sqlserver.programming.

Please don't multi-post.

--
David Portas
SQL Server MVP
--
Jul 20 '05 #2
David Portas scratched out in the sand
Replied in microsoft.public.sqlserver.programming.

Please don't multi-post.


....and for those of us who don't subscribe to m.p.s.p, your answer, David,
was...?

--
kai - kai at 3gproductions dot com
www.gamephreakz.com || www.filesite.org
"friends don't let friends use windows xp"
Jul 20 '05 #3
Hi Mavis,

As you suggest, a Primary key is a constraint on the column requiring that
every value is (and will be) different
in every row. Furthermore, it requires that no row can have a NULL value for
that column. So those are the restrictions
on a Primary Key field, that it be non-null and that each value be unique.
Further, a Primary Key can be made up of more than
one column. So, each table can have only one Primary Key, but it can be
made up of several columns. (jeesh- so far I'm probably
just making this MORE confusing).

So, although those are the characteristics of a Primary Key, a column is not
one until you designate it as such. The reason behind it is
twofold (there may be more reasons, but only two come to mind at the
moment - Note to self: more coffee):
1. Performance - when you designate a column as a Primary Key, it is
(clustered) indexed to assist in data retrieval.
2. Data Integrity - the database will ensure that every value that is
inserted (or updated) in the table will meet the requirements of a
Primary Key (i.e. unique and non-null) so you don't have to write those
checks into application code.

It's assumed that the Primary Key field will typically be the field used in
table joins, so because it's indexed (it's also a good general plan
to index the foreign key field) your queries will perform better.

I hope this is of some help Mavis,

Steve
"Mavis Tilden" <ma*********@hotmail.com> wrote in message
news:XV*******************@news20.bellglobal.com.. .
Hi all,

So I've been reading the newsgroups, and reading a few books trying to learn SQL and SQL Server 2000. The books tell me I need a Primary Key, and that
every table should have one. I know (I think) that a Primary Key is a
special field that uniquely identifies each record or row within a table.

My question is this: If I have a field or column whose values are all (and
will be) different in every row, is that what a Primary Key is, or is it not a primary key until I designate it as such with either a statement or by
right-clicking a little key on the table diagram.

If so, then what is the reason _behind_ why I have to do that, and have a
primary key?

I've been playing with a few tables, and I can select information just fine, relate tables together with joins, and everything works fine (so far) all
without my designating any Primary Keys--so why, exactly, do I need one, and exactly what is it?

I would appreciate any help and clarification.

Thanks.

Jul 20 '05 #4
On Mon, 11 Oct 2004 04:50:11 -0700, filesiteguy wrote:
David Portas scratched out in the sand
Replied in microsoft.public.sqlserver.programming.

Please don't multi-post.


...and for those of us who don't subscribe to m.p.s.p, your answer, David,
was...?


Hi filesiteguy,

http://www.google.com/groups?hl=en&l...bellglobal.com
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: js | last post by:
I am trying to create a primary key constraint on a view in the following statement. However, I got an error ORA-00907: missing right parenthesis. If the CONSTRAINT clause is removed, then the...
4
by: Peter Scott | last post by:
I created a table that has a column in that needs to contain a full Unix file path. Since 2048 was too long for a VARCHAR, I made it TEXT. I since populated the table. Now I want to make the...
9
by: Neil | last post by:
I've been discussing here a SQL 7 view which scrolls slowly when linked to an Access 2000 MDB. After trying various things, I've distilled it down to the following: when the linked view has a...
14
by: Abhi | last post by:
FYI: This message is for the benefit of MS Access Community. I found that this prblem has been encounterd by many but there is hardly any place where a complete solution is posted. So I thought...
7
by: sea | last post by:
Is it a good idea to programatically create a primary key? For example in a table called names, I have the following fields, (1) firstname (2)lastname (3) ID - will it be ok to create a primary...
4
by: Michael Hannon | last post by:
Greetings. We're running Postgres 7.3 on an Intel linux box (Redhat Enterprise server, version 3.0). We find ourselves in an awkward position: we have a database of attributes relating to...
1
by: GGerard | last post by:
Hello I'm trying to find the best way to set indexes and primary keys on MSAccess tables What are the advantages and disadvantages of indexes and primary keys? What fields should be indexed?...
8
by: Paul Hunter | last post by:
I am new to databases and thus to Access. I have a situation where I am trying to figure out how to key some tables I am working with. Consider that I have a database of my own records which are...
4
by: newbtemple | last post by:
New to programming and got a question. I'm trying to add a primary key in vb.net to a table I made in SQL. The table in SQL does not have a primary key. I pull that table into my program and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.