Thread: Finding primary keys in a table
I am trying to finish off my Python interface with some extra helper
functions and I need to find the primary key in a table if it exists.
I have two questions.
Although I can't imagine doing so, will the system allow you to create
more than one primary key on a table? I just need to know whether I
ned to test for multiple keys.
Can someone suggest a SQL statement to pull out the primary key(s) from
a table?
Also, if multiple keys are allowed, what are people's opinions about
using them? Basically I am creating a get function that is defined as:
def db_get(db, cl, arg, keyname = None):
where db is the database handle, cl is the class, arg is either a value
to lookup or a dictionary containing the value and keyname is the
field to lookup which defaults to the primary key. The question is,
what do I do if keyname is omitted (defaults to primary) and there
are two primary keys. Should I just use the first one or should I
raise an exception. I favour the latter.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
> I am trying to finish off my Python interface with some extra helper > functions and I need to find the primary key in a table if it exists. > I have two questions. > > Although I can't imagine doing so, will the system allow you to create > more than one primary key on a table? I just need to know whether I > ned to test for multiple keys. > > Can someone suggest a SQL statement to pull out the primary key(s) from > a table? > > Also, if multiple keys are allowed, what are people's opinions about > using them? Basically I am creating a get function that is defined as: > > def db_get(db, cl, arg, keyname = None): > > where db is the database handle, cl is the class, arg is either a value > to lookup or a dictionary containing the value and keyname is the > field to lookup which defaults to the primary key. The question is, > what do I do if keyname is omitted (defaults to primary) and there > are two primary keys. Should I just use the first one or should I > raise an exception. I favour the latter. Because we just create a unique index on a PRIMARY specification, I think any unique index on a field shows it as primary. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
Thus spake Bruce Momjian
> > Can someone suggest a SQL statement to pull out the primary key(s) from
> > a table?
> Because we just create a unique index on a PRIMARY specification, I
> think any unique index on a field shows it as primary.
Hmm. Any chance we can somehow flag it as well? Perhaps a new bool
field in pg_index the next time we do a dump & reload release? I
assume we will need it eventually anyway.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
> Thus spake Bruce Momjian > > > Can someone suggest a SQL statement to pull out the primary key(s) from > > > a table? > > Because we just create a unique index on a PRIMARY specification, I > > think any unique index on a field shows it as primary. > > Hmm. Any chance we can somehow flag it as well? Perhaps a new bool > field in pg_index the next time we do a dump & reload release? I > assume we will need it eventually anyway. Yes, we will. The question is when to add it. Probably best to wait until we do the whole thing. If we do it now, it is probable it will change when we add foreign keys. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
> > Because we just create a unique index on a PRIMARY specification, I
> > think any unique index on a field shows it as primary.
> Hmm. Any chance we can somehow flag it as well? Perhaps a new bool
> field in pg_index the next time we do a dump & reload release? I
> assume we will need it eventually anyway.
I'm not sure I understand all the issues, but if we can avoid
distinctions between different indices that would be A Good Thing. Since
multiple unique indices are allowed, what would be the extra
functionality of having one designated "primary"? Is it an arbitrary
SQL92-ism which fits with older databases, or something which enables
new and interesting stuff?
- Tom
Thus spake Thomas G. Lockhart
> I'm not sure I understand all the issues, but if we can avoid
> distinctions between different indices that would be A Good Thing. Since
> multiple unique indices are allowed, what would be the extra
> functionality of having one designated "primary"? Is it an arbitrary
> SQL92-ism which fits with older databases, or something which enables
> new and interesting stuff?
Well, in database design there is a distinction between indeces and
keys. Being able to specify this distinction in the database seems
useful to me from a database designer perspective.
Here's how I use that distinction in my Python code.
data = db_get(db, customer, client_id)
data = db_get(db, province, data)
The first line gets the client record based on the client ID. The next
line gets the information on the client's province such as full name,
tax rate, etc. It does this because it knows that prov, the two letter
code field, is the primary key on province and it can find the prov
field in the data dictionary which came from the customer class. This
is similar to how some 4GLs do it.
FIND FIRST customer WHERE client_id = x, province OF customer.
Certainly there are alternate ways of doing this but it is nice to be
able to put as much information into the RDBMS as possible. Codd's
first rule might be used in support of this.
"All information represented only in tables."
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.