I am programming a site in ASP. I am used to using Access which forces you
to have a Primary Key. However, I am learning to use SQL which does not
seem to force you to have a Primary Key. Do I really need one? Please let
me know and why. Thanks!!Techniclly speaking you don't need one, but I would consider any table
without a primary key poor design.
The main reason you need one is that the primary key guarantees you'll have
a column in your table that you can use to uniquely identify each record.
Without a primary key you could potentialy end up with multiple identical
records in your table which you're not able to identify individualy using a
select, update or delete statement.
HTH
Karl Gram
http://www.gramonline.com
"michaaal" <res0gyio@.verizon.net> wrote in message
news:O6$6#0iDEHA.3784@.TK2MSFTNGP10.phx.gbl...
> I am programming a site in ASP. I am used to using Access which forces
you
> to have a Primary Key. However, I am learning to use SQL which does not
> seem to force you to have a Primary Key. Do I really need one? Please
let
> me know and why. Thanks!!
>|||Hi,
To add on to old post,
1. Enforce uniqueness for values entered in specified columns
2. Will not allow nulls.
3. If you define a primary key for a table in your database, you can relate
that table to other tables, thus reducing the need for redundant data.
This will allow you to have Parent child relation ship with out writing
code.
4. This will allow you to do Cascading (Refer boks inline)
Always for a better database modelling we should enforce Primary key /
Foregn key concept.
Thanks
Hari
MCDBA
"Karl Gram" <NOSPAMkarl@.gramonline.nl> wrote in message
news:#FrwxMkDEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Techniclly speaking you don't need one, but I would consider any table
> without a primary key poor design.
> The main reason you need one is that the primary key guarantees you'll
have
> a column in your table that you can use to uniquely identify each record.
> Without a primary key you could potentialy end up with multiple identical
> records in your table which you're not able to identify individualy using
a
> select, update or delete statement.
> --
> HTH
> Karl Gram
> http://www.gramonline.com
> "michaaal" <res0gyio@.verizon.net> wrote in message
> news:O6$6#0iDEHA.3784@.TK2MSFTNGP10.phx.gbl...
> you
> let
>|||> 3. If you define a primary key for a table in your database, you can
relate
> that table to other tables, thus reducing the need for redundant data.
> This will allow you to have Parent child relation ship with out
writing
> code.
The above statement brings on another question I had... Do I really
WANT to do this type of thing on the SQL server level? Or do I
want to do this type of thing in my code. My first inclination is to do
it in the code, however, I have not really sat down and researched the
possible speed differences. Any comments on this? Thank you!|||"michaaal" <res0gyio@.verizon.net> wrote in message
news:eRB$WFlDEHA.3980@.TK2MSFTNGP09.phx.gbl...
> relate
> writing
> The above statement brings on another question I had... Do I really
> WANT to do this type of thing on the SQL server level? Or do I
> want to do this type of thing in my code. My first inclination is to do
> it in the code, however, I have not really sat down and researched the
> possible speed differences. Any comments on this? Thank you!
Enforcing constraints in the code means that they will only be enforced in
your code. If someone uses Access or similar to access your database
directly they can by-pass all your constraints and wreak havoc.
It is also (IMHO) easier to document and troubleshoot. The constraints are
there as part of your table definition. All your data-centric info is in
one place.
As for speed, if you have the contsraints in SQL Server the optimizer and
can make informed decisions on how best to optimize the queries. Otherwise
in the code you will have to decide how to join the data, which is either
going to be very complicated, or not the best method in every circumstance.
Finally, and this is more a judgement on my programming skills than yours,
constraints work pretty much the way it says on the box. If you are
hand-coding all this, then bugs can creep in, you may not foresee every
eventuality, etc.
So my vote is for data-centric rules to be in the data tier.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004|||No, you should always enforce constraints at the DATA level. Enforcing them
in the code means that your data can become corrupt by someone simply
bypassing your application (e.g. running an insert/update/delete from Query
Analyzer).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"michaaal" <res0gyio@.verizon.net> wrote in message
news:eRB$WFlDEHA.3980@.TK2MSFTNGP09.phx.gbl...
> relate
> writing
> The above statement brings on another question I had... Do I really
> WANT to do this type of thing on the SQL server level? Or do I
> want to do this type of thing in my code. My first inclination is to do
> it in the code, however, I have not really sat down and researched the
> possible speed differences. Any comments on this? Thank you!
>
>|||RE/
>No, you should always enforce constraints at the DATA level. Enforcing the
m
>in the code means that your data can become corrupt by someone simply
>bypassing your application (e.g. running an insert/update/delete from Query
>Analyzer).
Do you prefer to enforce RI via triggers or the other way?
"Other way" because I don't know enough go spell it out...Converted a few MS
Access DBs and wound up with triggers - so that's all I know. MSDN Univers
al
coming soon - so I guess I'll get the option to go either way via MS Visio's
DB
design tool...
--
PeteCresswell|||No, triggers can be pretty poor for performance, depending on other
circumstances. I prefer traditional primary/foreign key relationships, then
violations are stopped in their tracks rather than after the fact.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"(Pete Cresswell)" <x@.y.z> wrote in message
news:k2op50heohj4o6j7tkgp05o7gk61hf1eue@.
4ax.com...
> RE/
> Do you prefer to enforce RI via triggers or the other way?
> "Other way" because I don't know enough go spell it out...Converted a few
> MS
> Access DBs and wound up with triggers - so that's all I know. MSDN
> Universal
> coming soon - so I guess I'll get the option to go either way via MS
> Visio's DB
> design tool...
> --
> PeteCresswell
