primary key

hey all,
i have an invoice table where the customerID/invoiceNo uniquely identify a
record, also i have just a tableID (if I'm not mistaken, is a good practice
to do). which one should be my primary key?
thanks,
rodcharIf tableID is an auto-incrementing integer field, then I'd use that one.
You can build an index on CustomerID, InvoiceNo for the lookups.
Personally, I would make the InvoiceNo unique as well, unless there's a
reason the system requires the uniqueness to be both customerID and
InvoiceNo.
-Steve-
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:2B8D7EAE-1AF3-4622-A219-63E72EEEF228@.microsoft.com...
> hey all,
> i have an invoice table where the customerID/invoiceNo uniquely identify a
> record, also i have just a tableID (if I'm not mistaken, is a good
> practice
> to do). which one should be my primary key?
> thanks,
> rodchar|||"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:2B8D7EAE-1AF3-4622-A219-63E72EEEF228@.microsoft.com...
> hey all,
> i have an invoice table where the customerID/invoiceNo uniquely identify a
> record, also i have just a tableID (if I'm not mistaken, is a good
> practice
> to do). which one should be my primary key?
> thanks,
> rodchar
Whatever makes most sense to you. Both columns are candidate keys if they
are unique and not nullable therefore it would make sense to declare unique
constraints on both of them (either NOT NULL UNIQUE or PRIMARY KEY). In SQL
Server the practical differences between a PRIMARY KEY and one that is NOT
NULL UNIQUE are purely to do with default behaviours: PKs are used as the
default for foreign key references; they are clustered by default and PK
columns default to NOT NULL rather than NULL. All of these defaults can be
explicitly overridden so most of the time it doesn't make any difference
which key is PK rather than NOT NULL UNIQUE. If a surrogate key is used as a
foreign key reference then that is frequently the one declared as PRIMARY
KEY but that doesn't have to be so.
ER modelling tools may handle PRIMARY KEYs in a special way (using a
different notation in diargrams for example) so you may want to take that
into account when you choose.
Notice that throughout I've written PRIMARY KEY in caps. That's because
SQL's PRIMARY KEY is not the same thing as a "primary key" in the relational
model and design theory. Standard SQL uses the PRIMARY KEY purely as a way
of designating the default reference for a foreign key constraint.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:wbudnVf6Cc9-Es_eRVnyjw@.giganews.com...

> PKs are used as the
> default for foreign key references; they are clustered by default and PK
> columns default to NOT NULL rather than NULL. All of these defaults can be
> explicitly overridden
Hopefully it's obvious that if you override the default nullability on a PK
column by declaring it explicitly as NULL rather than NOT NULL then the
result is an error and the PK won't be created. :-)
David Portas
SQL Server MVP
--|||>> i have an invoice table where the (customer_id, iinvoice_nbr) uniquely identif
y a record [sic], also i have just a tableID (if I'm not mistaken, is a good pr
actice to do). which one should be my primary key? <<
I would have assumed that the invoice number would be the identifier
for an Invoice, and not the pair (customer_id, iinvoice_nbr). And I
assume that you have an audit trail and check digits on the invoice
numbers.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS so that table_id makes no sense. All attributes
have to map to something inthe reality of the data model -- where did
God put this table_id'
WHY do you think that "magical, universal table id" would be part of
the relational model' Can you quote anything from Dr. Codd or any
book on RDBMS to support this?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129496221.897333.45100@.f14g2000cwb.googlegroups.com...
> I would have assumed that the invoice number would be the identifier
> for an Invoice, and not the pair (customer_id, iinvoice_nbr). And I
> assume that you have an audit trail and check digits on the invoice
> numbers.
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files; there is no sequential access or
> ordering in an RDBMS so that table_id makes no sense. All attributes
> have to map to something inthe reality of the data model -- where did
> God put this table_id'
> WHY do you think that "magical, universal table id" would be part of
> the relational model' Can you quote anything from Dr. Codd or any
> book on RDBMS to support this?
>
I also advise against the table_id here, however I think it's fine for
invoice_nbr to be an IDENTITY column and a candidate key.
In any case add a unique index on invoice_nbr and a unique index on
(customer_id,invoice_nbr). Which is your "primary key" is a matter of
preference. For performance the more important question is which index you
make your clustered index. You can either optimize for access by customer
or access by invoice_nbr, it's up to you.
David|||>> however I think it's fine for invoice_nbr to be an IDENTITY column and a
candidate key. <<
No. IDENTITYwill have gaps and has no check digits or other validation
methods. How do you get validation of the invoice numbers? How do you
get an audit trail, when you have machibne-generated, untraceable gaps?
Would you do this with upor checkbook?|||thanks for the great replies.
"rodchar" wrote:

> hey all,
> i have an invoice table where the customerID/invoiceNo uniquely identify a
> record, also i have just a tableID (if I'm not mistaken, is a good practic
e
> to do). which one should be my primary key?
> thanks,
> rodchar|||so, say for instance there are many customers. I would like each customer to
have their own sequence of invoice numbers. Please advise.
"Steve Zimmelman" wrote:

> If tableID is an auto-incrementing integer field, then I'd use that one.
> You can build an index on CustomerID, InvoiceNo for the lookups.
> Personally, I would make the InvoiceNo unique as well, unless there's a
> reason the system requires the uniqueness to be both customerID and
> InvoiceNo.
> -Steve-
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:2B8D7EAE-1AF3-4622-A219-63E72EEEF228@.microsoft.com...
>
>|||Personally, I have never used that concept. IMO, an invoice # should be as
unuique as the customer ID. That's the way I've always built all my sytems.
I don't think I've ever seen an accounting package that used duplicate
invoice numbers for different customers. I tend to think of invoice numbers
as transaction numbers. Unquie by their very nature. It's also less
complicated for the end-user to look up an invoice by just the invoice#.
Why should they have to select or enter the customer ID as well? Obviously
you should still have the customerID as part of the invoice record, I just
prefer not to combine the two for uniqueness.
HTH,
-Steve-
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:4CAB0A12-E8EA-498F-B39F-0BC7B68B9BD8@.microsoft.com...
> so, say for instance there are many customers. I would like each customer
> to
> have their own sequence of invoice numbers. Please advise.
>