Do all tables need primary keys?
Brian Vallelunga
books.
BookCollection Table
- CollectionId
- Description
- Etc
Book Table
- BookId
- Title
- Author
Now suppose the book could be in multiple collections at once. I might
create a table linking these two in a many-to-many fashion:
BookAssignment Table
- CollectionId
- BookId
Both of these would be foreign keys. Do I need a primary key for this table?
What would it be used for?
Thanks,
Brian
Anith Sen
table, in relational terms.
--
- Anith
(Please respond only to newsgroups)
David Browne
A table without a primay key is not in first normal form.
It is, in other words, not a relation at all.
In your case
> BookAssignment Table
> - CollectionId
> - BookId
The primary key would be (CollectionId,BookID),
and it is needed to avoid duplicate records.
Imagine if a duplicate (CollectionID, BookID)
found its way into your linking table.
Many of your queries would start outputing wrong results.
eg:
Select collectionID, count(*)
from BookAssignment
group by collectionID
and your inserts, updates and deletes might stop working.
David
"Brian Vallelunga" <bgvall...@hotmail.com> wrote in message
news:ewLuWmd4...@TK2MSFTNGP12.phx.gbl...
Joe Celko
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. That is the minimal netiquete around here.
Is thisn what you meant to say? I assume you have more than one book,
and meant to follow the convention that sets are plural or collecitve
nouns, that you know what an ISBN is, etc.
CREATE TABLE BookCollections
(collection_id INTEGER NOT NULL PRIMARY KEY,
description VARCHAR(30) NOT NULL,
..);
CREATE TABLE Books
(isbn CHAR(10) NOT NULL PRIMARY KEY,
title VARCHAR(100) NOT NULL,
author VARCHAR(30) NOT NULL,
...);
>> Now suppose the book could be in multiple collections at once. ...Do
I need a primary key for this table? <<
You already have a primary key!!
CREATE TABLE CollectionBooks
(collection_id INTEGER NOT NULL
REFERENCES BookCollections(collection_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
isbn CHAR(10) NOT NULL
REFERENCES Books(isbn)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (collection_id, isbn));
Now, if you want to make a rule that a book can be in only collection:
CREATE TABLE CollectionBooks
(collection_id INTEGER NOT NULL
REFERENCES BookCollections(collection_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
isbn CHAR(10) NOT NULL UNIQUE
REFERENCES Books(isbn)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (collection_id, isbn));
Were you thinking of adding some stupid IDENTITY property or other
proprietary, non-relational garbage to the table? Surely not!!
--CELKO--
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Brian Vallelunga
"Joe Celko" <71062...@compuserve.com> wrote in message
news:uWouF0d4...@TK2MSFTNGP12.phx.gbl...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are. That is the minimal netiquete around here.
>
> Is thisn what you meant to say? I assume you have more than one book,
> and meant to follow the convention that sets are plural or collecitve
> nouns, that you know what an ISBN is, etc.
>
> CREATE TABLE BookCollections
> (collection_id INTEGER NOT NULL PRIMARY KEY,
> description VARCHAR(30) NOT NULL,
> ..);
>
> CREATE TABLE Books
> (isbn CHAR(10) NOT NULL PRIMARY KEY,
> title VARCHAR(100) NOT NULL,
> author VARCHAR(30) NOT NULL,
> ...);
I am sorry for not posting the DLL. I thought my question was general enough
without it. This is very close to what I was trying to convey, but I was
planning on using UNIQUEIDENTIFIER instead of INTEGER for my ID field.
> >> Now suppose the book could be in multiple collections at once. ...Do
> I need a primary key for this table? <<
>
> You already have a primary key!!
>
> CREATE TABLE CollectionBooks
> (collection_id INTEGER NOT NULL
> REFERENCES BookCollections(collection_id)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> isbn CHAR(10) NOT NULL
> REFERENCES Books(isbn)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> PRIMARY KEY (collection_id, isbn));
Ok, so you are saying that my primary key should be the combination of both
the isbn and collection_id fields. I was unsure if this was the appropriate
method setting a primary key, versus adding "some stupid IDENTITY" column.
Am I to understand that using a UNIQUEIDENTIFIER as a primary key for the
book table is a bad thing? If so, why? (I have my reasons for wanting to do
so, and am just curious as to the downside)
One last thing, I_know_I have read that it is proper to use singlular names
when naming database tables. Is there really an accepted standard on this,
or is this a db religious war that I really don't want to get involved in?
Anyway, thanks for the help. I've only dealt with simple database structures
before and am trying to learn more. Any good places online to read up? I've
done a google search but can't find anything more than "my first database
with Access."
Brian
BP Margolin
> One last thing, I_know_I have read that it is proper to use singlular names
> when naming database tables. Is there really an accepted standard on this,
> or is this a db religious war that I really don't want to get involved in?
This is a db religious war ... regardless of what Joe might happen to say ... and for what it's worth, I happen to agree with Joe on the use of plural names for tables ... but that still doesn't change the fact that it is really just a db religious war :-)
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"Brian Vallelunga" <bgvall...@hotmail.com> wrote in message news:eJs9a4f4...@TK2MSFTNGP11.phx.gbl...
Bob Barrows
<bgvall...@hotmail.com> wrote:
>
>Ok, so you are saying that my primary key should be the combination of both
>the isbn and collection_id fields. I was unsure if this was the appropriate
>method setting a primary key, versus adding "some stupid IDENTITY" column.
>Am I to understand that using a UNIQUEIDENTIFIER as a primary key for the
>book table is a bad thing? If so, why? (I have my reasons for wanting to do
>so, and am just curious as to the downside)
>
The downside is:
It's a wide column (16 bytes), making the index built using it larger
and therefore slower to search
The upside:
It is glogally unique, so it is useful in replication situations.
My view: if I don't need global uniqueness, I use an Integer column
when I want to use a surrogate primary key.
HTH,
Bob Barrows
Please reply to the newsgroup. My reply-to address is my "spam trap" and I don't check it very often.
Jeff Clausius
(off topic of main thread)
i get a big kick out of people telling you a table's name should be
plural or singular. or heaven forbid, a column name is in hungarian
notation.
as long as your entire schema is named consistently throughout, do
things the way you want them done. odds are you will be the one
maintaining the database, and you'll have the documentation on how
things work. so do things that make the most sense in your current
situation.
just my $0.02,
jeff clausius
sourcegear corporation
p.s.
personally, a table is generally a "collection" of rows. to me the
collective nature of a table implicitly means plurality, so i like to
use the plural form. as for columns, most programmers eventually end up
with the ddl in front of them, which should contain the column data
types, so i like to keep things clean and **not** use data type or any
other abbreviations for column names.
"Brian Vallelunga" <bgvall...@hotmail.com> wrote in
news:eJs9a4f4...@TK2MSFTNGP11.phx.gbl:
Brian Vallelunga
time), but my db won't be used for anything big enough for that to be a
concern. The main advantage to using the unique keys is that when working
with a .net app and diconnected datasets, you can create the keys on the
client side before updating the database. This greatly helps with foreign
keys because you don't need to have the database generate them for you. If
speed was my main concern, I would have probably just gone with integers
that were auto-incremented on the server.
Anyway, I'm glad I have permission to stick with my database naming scheme
though. ;-) It is consistent, which is the main issue.
Brian
"Bob Barrows" <reb_...@yahoo.com> wrote in message
news:3e648e29....@msnews.microsoft.com...
Joe Celko
enough without it. <<
It is always better to use DDL even when the question is "so clear the
entire universe knows this application!" <g>.
>> This is very close to what I was trying to convey, but I was planning
on using UNIQUEIDENTIFIER instead of INTEGER for my ID field [sic]. <<
Notice that you said FIELD and not column. Fields are things inside
files, and are totally different from columns. You are still thinking
in terms of a sequential file model, which is why you asked the
question.
>> Ok, so you are saying that my primary key should be the combination
of both the isbn and collection_id fields [sic]. <<
Yes!! A key is BY DEFINITION a subset of the attributes of an entity
which uniquely identify it. The (isbn, collection_id) *is* a key by its
nature and you have nothign to say about it, if you want this database
to work right.
>> I was unsure if this was the appropriate method setting a primary
key, versus adding "some stupid IDENTITY" column. <<
You don't make up a key; it exists in the data model.
>> Am I to understand that using a UNIQUEIDENTIFIER as a primary key for
the book table is a bad thing? If so, why? <<
So insanely bad, I will use it as example of bad coding in my next book.
The IDENTITY column is a hold over from the early programming language
which were <i>very<i> close to the hardware. For example, the fields in
a COBOL or FORTRAN program were assumed to be physically located in main
storage in the order they were declared in the program. This meant that
you could define a template that overlaid the same physical space and
read the representation in several different ways. In COBOL, the
command was REDEFINES, EQUIVALENCE in FORTRAN and a union in 'C'.
From a logical viewpoint, this redefinition makes no sense at all. It
is confusing the numeral with the number the numeral represents. The
history of programming after this point in time has been to divorce the
logical and physical models completely.
The early SQLs were based on existing file systems. The data was kept
in physically contiguous disk pages, in physically contiguous rows, made
up of physically contiguous columns. In short, just like a deck of
punch cards or a magnetic tape.
But physically contiguous storage is only one way of building a
relational database and it is not always the best one. But aside from
that, the whole idea of a relational database is that user is not
supposed to know how things are stored at all, much less write code that
depends on the particular physical representation in a particular
release of a particular product.
One of the biggest errors is the IDENTITY column in the Sybase family
(SQL Server and Sybase). People actually program with this "feature"
and even use it as the primary key for the table! Now, let's go into
painful details as to why this thing is bad.
The practical considerations are that IDENTITY is proprietary and
non-portable, so you know that you will have maintenance problems when
you change releases or port your system to other products. It also has
some very strange bugs in both Sybase and SQL Server; go to a newsgroup
and do a search.
But let's look at the logical problems. First try to create a table
with two columns and try to make them both IDENTITY columns. If you
cannot declare more than one column to be of a certain datatype, then
that thing is not a datatype at all, by definition.
Next, create a table with one column and make it an IDENTITY column.
Now try to insert, update and delete different numbers from it. If you
cannot insert, update and delete rows from a table, then it is not a
table by definition.
Finally create a simple table with one IDENTITY column and a few other
columns. Use a few statements like
INSERT INTO Foobar (a, b, c) VALUES ('a1', 'b1', 'c1');
INSERT INTO Foobar (a, b, c) VALUES ('a2', 'b2', 'c2');
INSERT INTO Foobar (a, b, c) VALUES ('a3', 'b3', 'c3');
to put a few rows into the table and notice that the IDENTITY column
sequentially numbered them in the order they were presented. If you
delete a row, the gap in the sequence is not filled in and the sequence
continues from the highest number that has ever been used in that column
in that particular table.
But now use a statement with a query expression in it, like this:
INSERT INTO Foobar (a, b, c)
SELECT x, y, z
FROM Floob;
Since a query result is a table, and a table is a set which has no
ordering, what should the IDENTITY numbers be? The entire, whole,
completed set is presented to Foobar all at once, not a row at a time.
There are (n!) ways to number (n) rows, so which one do you pick? The
answer has been to use whatever the physical order of the result set
happened to be. That non-relational phrase "physical order" again.
But it is actually worse than that. If the same query is executed
again, but with new statistics or after an index has been dropped or
added, the new execution plan could bring the result set back in a
different physical order. Can you explain from a logical model why the
same rows in the second query get different IDENTITY numbers? In the
relational model, they should be treated the same if all the values of
all the attributes are identical.
>> (I have my reasons for wanting to do so, and am just curious as to
the downside) <<
Explain why anyone would want a database subject to corruption, a table
bigger than it needs to be and full of proprietary code that will never
port. I guess I just don't get it after a few decades of cleaning up
code like this as an over-priced consultant.
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Joe Celko
scheme though. ;-) It is consistent, which is the main issue. <<
The main issue for names is intelligability; consistency follows from
that property. Just talk to the Data Quality people; the main cause of
corrupted data is poor data element names which lead to duplicate and
ambigous metadata over time.
David Browne
> >> I am sorry for not posting the DLL. I thought my question was general
> enough without it. <<
>
> It is always better to use DDL even when the question is "so clear the
> entire universe knows this application!" <g>.
>
> >> This is very close to what I was trying to convey, but I was planning
> on using UNIQUEIDENTIFIER instead of INTEGER for my ID field [sic]. <<
>
> Notice that you said FIELD and not column. Fields are things inside
> files, and are totally different from columns. You are still thinking
> in terms of a sequential file model, which is why you asked the
> question.
>
> >> Ok, so you are saying that my primary key should be the combination
> of both the isbn and collection_id fields [sic]. <<
>
> Yes!! A key is BY DEFINITION a subset of the attributes of an entity
> which uniquely identify it. The (isbn, collection_id) *is* a key by its
> nature and you have nothign to say about it, if you want this database
> to work right.
>
> >> I was unsure if this was the appropriate method setting a primary
> key, versus adding "some stupid IDENTITY" column. <<
>
> You don't make up a key; it exists in the data model.
>
> >> Am I to understand that using a UNIQUEIDENTIFIER as a primary key for
> the book table is a bad thing? If so, why? <<
>
> So insanely bad, I will use it as example of bad coding in my next book.
>
> The IDENTITY column is a hold over from the early programming language
> which were <i>very<i> close to the hardware.
Not at all. Joe, you are ignoring the problem that IDENTITY is designed to
solve.
The real problem addressed by IDENTITY and similar extensions is the storage
of entities
which are not uniquely identified by a small set of immutable attributes.
Take a customer, or a student, or any sort of person entity.
You have a name, an address, a telephone number,
a fax number, etc. Which subset of these attributes uniquely identify a
person?
Would we need to capture and maintain more information than we really care
about just
to uniquify our records: DOB, shoe size, country of origin, mother's maiden
name, etc.
We can't use SSN, because that just begs the question. SSN is an "IDENTITY"
assigned by the
government, because it has the same problem we do.
Even if we add enough columns to the key to uniquely identify a person,
we're still
in deep trouble. The primary key would have many fields in it, so any time
we
want to use it in a foreign key relationship, it's a giant hastle.
select * from
student join
class
on student.name = class.student_name
and student.dob = class.student_DOB
and student.shoe_size = class.student_shoe_size
and . . .
The primary key is not immutable, leading to more performance
and maintence problems. Indexing structures are inneficient
and our lives, in general, get worse.
In cases where the "real" primary key contains an unreasonable
number of columns, the only resonable solution is to use an artifial
"proxy" key.
Other common entities raising these problems are completely
artifical entities like orders, line items, log entries, appointments.
Some such entities might be able to be named, but that requires user
intervention.
You can have humans naming each new Partnership entity, but not each new
Order entity.
For such an entity, its ID column is a natural key, not an artifial one.
An order is uniquily identified by an ID because it is an entity created
out of whole cloth. Other than the ID there may be no other columns there
but CUSTOMER, and ORDER_DATE, which could easily not be unique.
For such completely artifical entities as well, then, the only reasonable
solution
is to generate some key.
That leads to another problem of how to generate a new unique value for the
key column.
Since there is no reasonable method to do this in ANSI SQL, each vendor has
an
extension to generate keys.
David
Brandon Lilly
> We can't use SSN, because that just begs the question. SSN is an "IDENTITY"
> assigned by the government, because it has the same problem we do.
You shouldn't use SSN anyhow, because the government re-uses the numbers. While a SSN DOES uniquely identify LIVING american citizens, a single SSN can be identifying 10 other dead ones :)
Cheers,
Brandon
--
"In the beginning the universe was created. This has made a lot of people very angry, and has been widely regarded as a bad move." - Douglas Noel Adams (1952-2001)
[Please remove "nospam_" from email address to reply.]
Colin Young
that doesn't work anywhere else in the world, and frequently doesn't work
even within the U.S. (just try to open a joint bank account when your spouse
has a non-working status and therefore no SSN).
Colin
"Brandon Lilly" <blilly@nospam_medevolve.com> wrote in message
news:Oz7BDTy4...@TK2MSFTNGP09.phx.gbl...
BP Margolin
> I guess I just don't get it after a few decades of cleaning up
> code like this as an over-priced consultant.
I'd love to hear the comments of people who come in after you and have to maintain or port an application you worked on. Betcha the comments would be interesting :-)
BPM
Anith Sen
Your post basically gives the arguments for using a surrogate when the
natural primary keys suffers the known drawbacks of stability, simplicity
etc. However equating those arguments to identity column may not be always
fair.
There is a school of thought that’s, identity columns were first put in
place to retain the customers who are upgrading from Access database (which
has an auto number). It was not designed to solve any 'problems', because a
dumb attribute can never solve a real world problem, unless it can be
identified in the real world, although there are certain minor exceptions.
Had the implementation of identity has some intelligence with which it can
relate to the natural keys in a table, it would have been far better. Also,
just wanted to point out SSN is not a dumb number like identity, it has
certain information built into that like state(s) where it is issued etc.
There is another aspect which needs to be clarified with respect to natural
keys. In a sense every natural key can be considered as an artificial key. A
new-born baby's name, a character identifier, though artificially coined and
assigned by the parents, when used in the real world becomes a natural
attribute. A zip code assigned to a locality and used in real world, becomes
its natural attribute. Same is the case with policy numbers, VIN, Employee
Identifiers, Department Names etc. Of Course, SSN once assigned and then
used outside the context of the SSA database, in the real world, becomes
natural and columns representing them in a database becomes the natural
keys. So arguing that artificial keys are necessary for all entity
identification purposes is moot. For all data design purposes, any attribute
which is assigned a meaning to itself outside the context of the database
can be termed as a natural attribute.
I am not arguing for natural keys in all tables all the time, but it will be
far better if the data designers understand when and why a surrogate key is
needed and use them judiciously rather than simply start out designing a
table by simply adding an identity column assuming it is going to solve some
problems.
Joe Celko
things the way you want them done. odds are you will be the one
maintaining the database, and you'll have the documentation on how
things work. so do things that make the most sense in your current
situation. <<
No. Write code as if **someone else** had to maintain it and their last
name is "Gotti" and they have your home address. Make your naming
conventions follow ISO standards and use all the other industry
standards you can find. Think about your system as part of a global
community, not as just a "current situation" -- that what the difference
between amateur and professional code.
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Jeff Clausius
hmmm... i've re-read my post. i'm pretty sure i didn't state he should
*not* use iso standards for naming conventions. it should be a given to
use standards relevant to your particular industry and/or needs.
my post was addressing the statement about plurality of table names, not
the content of the name itself. in regards to this matter, do things as
one would see fit (singular or plural), and maintain that consistency
throughout the design.
</to joe>
consistency is only *one* of the keys in good schema design. i can't
tell you how many times i've been brought in to clean up someone's mess,
to only find tables with column names like table_customer.customer_no
and orders.cust_num. however, the naming mismatch caused them to miss
referential integrity between the tables.
<soapbox>
in sum, joe is correct, please use iso standards and other naming
conventions found in your industry. however, this is not the end of the
story. to prevent future headaches, document those naming conventions,
table relationships, and other ambiguous areas in your ddl, and always
remember to remain consistent throughout the design.
</soapbox>
jeff
sourcegear corporation
Joe Celko <71062...@compuserve.com> wrote in
news:OFXUrD14...@TK2MSFTNGP10.phx.gbl:
> Attachment decoded: untitled-2.txt
> ------=_NextPart_000_27282764
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> <META content="MSHTML 5.50.4807.2300" name=GENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY bgColor=#c0c0c0>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>So you mean if we give small access to a
> user then the user will be able to see all database structure ? That
> is terrible vulnerability.</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>We have SCHEMA in Oracle so i can
> efficiently hide any part of database from user. Do we have similar
> thing in SQL server? I know SqlServer 2000 has scema capability. Can
> it solve my problem ?</FONT></DIV> <DIV><FONT face=Arial
> size=2></FONT> </DIV> <DIV><FONT face=Arial
> size=2>Thanks,</FONT></DIV> <DIV><FONT face=Arial
> size=2>Ali</FONT></DIV> <DIV><FONT face=Arial
> size=2></FONT> </DIV> <DIV><FONT face=Arial
> size=2></FONT> </DIV> <DIV><FONT face=Arial
> size=2></FONT> </DIV> <DIV><FONT face=Arial
> size=2></FONT> </DIV> <BLOCKQUOTE dir=ltr
> style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px;
> BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
> <DIV>"Tom Moreau" <<A
> href="mailto:t...@dont.spam.me.cips.ca">t...@dont.spam.me.cips.ca</A>
&g
> t; wrote in message <A
> href="news:#xtfKY04...@TK2MSFTNGP09.phx.gbl">news:
#xtfKY04CHA.24
> 0...@TK2MSFTNGP09.phx.gbl</A>...</DIV> <DIV><FONT face=Tahoma
> size=2>Well, yes and no. You can create a view, proc or
> function and specify the WITH ENCRYPTION option. However, it
> is quite difficult to prevent users from seeing a list of
> objects.</FONT></DIV> <DIV><BR>-- <BR>Tom</DIV>
> <DIV> </DIV>
> <DIV>---------------------------------------------------------------
<
> BR>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA<BR>SQL Server
> MVP<BR>Columnist, SQL Server Professional<BR>Toronto, ON
> Canada <A
> href="mailto:t...@cips.ca">t...@cips.ca</A><BR><A
> href="http://www.pinnaclepublishing.com/sql">
www.pinnaclepublishing.c
> om/sql</A><BR></DIV> <BLOCKQUOTE
> style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px;
> BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
> <DIV>"A.M" <<A
> href="mailto:hate...@spam.com">hate...@spam.com</A>> wrote
> in message <A
> href="news:O$KCE2z4C...@TK2MSFTNGP09.phx.gbl">news:O
$KCE2z4CHA.
> 24...@TK2MSFTNGP09.phx.gbl</A>...</DIV>Hi,<BR><BR>Can i limit a
> user to just run a select statemet on a view, but do not
> allow<BR>him see the view definition or list of other database
> objects?<BR>If i make a login member of database public role, then
> that user will be<BR>able to see all database structue such as
> view/table/sp definitions.<BR>We need to restrict a user and hide
> database structure from him. All we want<BR>him to do is run a
> select statement on a view, but we don't want him to see<BR>view
> structure.<BR><BR>Any help would be
> appreciated,<BR>Ali<BR><BR><BR></BLOCKQUOTE></BLOCKQUOTE></BODY>
</H
> TML>
>
>
> Attachment decoded: _alt.0
> ------=_NextPart_000_27282764--