Re: How do I discover a table's primary key?

How exactly do I "grab the source code for sp_helpconstraint or
sp_helpindex"? That would be very helpful, but I don't see how to
do it.

I could understand that my program might throw out stdout or stderr,
but that isn't what seems to be happening. When I use the
sp_helpconstraints system procedure in isql the result looks like
the result I get when I do an ordinary select command (the part up
until "(4 rows affected)" in my example below), and then some extra
text gets tacked on. In my program I call sp_helpconstraints
exactly the same way I call an ordinary select command, and I get
back a table, but in this case the table only contains 1 row, not
4. I can understand the extra text being lost, but not 3 of the
4 table rows.

Larry Coon has helpfully called my attention to the index_col
function, which in fact allows me to extract the column names for
an index once I have an entry in sysindexes. Since I already know
how to find a primary key in sysindexes, and since I no longer have
to interpret opaque varbinary field values to get the column names,
that is a solution to my problem right there. Using sp_helpconstraints
would be easier though; I just wish I understood what was going on
with the output.

In article <3rFkg.43933$iF6.3290@pd7tw2no>,
Mark A. Parsons <hooru@xxxxxxxxx> wrote:
What little 'application' work I do is typically in the form of UNIX shell
scripting ... using isql. (Yeah, yeah, yeah .. I hear the Perl(ie) guys
moaning ...)

My guess is that your program is probably ignoring/throwing-out some of the
sp_helpconstraint output (eg, ... very simplistic example ... suppose
sp_helpconstraint info comes across on stdout and stderr, but your program
is only watching stdout).

Anyhoo, what I would recommend is that you grab the source code for
sp_helpconstraint or sp_helpindex ... and see how these procs rebuild the
column list from the varbinary data. You basically want to pick-n-choose
the bits-n-pieces *YOU* need to pull back the specific info *YOU* want.
Then it's just a question of whether you implement your code as dynamic
SQL, or your own stored proc.

If you're going to be pulling this kind of information on a regular basis
the stored proc may be the better solution. And if you plan on using this
code in different databases, consider prefacing the stored proc name with
'sp_' and placing it in the sybsystemprocs database and ... wala ... you've
created your own dataserver-level stored proc which can be called from
anywhere in the dataserver.

John Brock wrote:

The status field appears to be what I was looking for. Thank you!

However I now realize that I don't know how the figure out the
index columns from the sysindexes table! The varbinary values look
forbidding, and I don't see where they are explained. Beyond that,
I am working with VB.NET, and I seem to remember that when I tried
to do something with varbinary data a year ago I had trouble. So
this may still be problematic.

However it just occurred to me that I could call the sp_helpconstraint
system procedure from my program. I tried this just now, and it
returned a table with the information I am interested in!

So is this a normal thing to do? What considerations are there
for calling system procedures from programs? May this be done
freely? Or were these procedures intended to be used manually,
and not from programs?

Disturbingly, calling sp_helpconstraint from isql gives different
results than calling it from my program! When I call sp_helpconstraint
from my program I get back a table with information about default
values, primary keys, and other indexes on that table. But when
I call sp_helpconstraint on the same table from isql I get additional
rows with information about referential constraints (i.e., foreign
keys that refer to the table), and in addition isql spews out some
non-tabular text talking about the referential constraints. Here
is an actual example of what I am talking about. From isql:

=== BEGIN EXAMPLE ===

1> sp_helpconstraint City
2> go
name definition created
------------------------------ ------------------------------------------------------------ -------------------
Building_1697750420 Building FOREIGN KEY (City) REFERENCES City(City) Jun 12 2006 4:05PM
Employee_1969751389 Employee FOREIGN KEY (City) REFERENCES City(City) Jun 12 2006 4:05PM
Department_30268482 DepartmentManager FOREIGN KEY (City) REFERENCES City(City) Jun 12 2006 4:05PM
City_16497502491 PRIMARY KEY INDEX ( City) : CLUSTERED, FOREIGN REFERENCE Jun 12 2006 4:05PM

(4 rows affected)

Total Number of Referential Constraints: 3

Details:
-- Number of references made by this table: 0
-- Number of references to this table: 3
-- Number of self references to this table: 0

Formula for Calculation:
Total Number of Referential Constraints
= Number of references made by this table
+ Number of references made to this table
- Number of self references within this table
(return status = 0)

=== END EXAMPLE ===

But when I execute the same statement ("sp_helpconstraint City")
from within my program I only get back one row, the PRIMARY KEY
row. This happens to be the row that I want, but it still makes
me nervous. What is going on? Why does the system procedure return
two different results when called from two different contexts? Is
this something I should be worried about? There is probably just
a matter of some setting somewhere, but I would be a lot more
comfortable if I knew exactly what was going on.


In article <8TDkg.43326$iF6.3875@pd7tw2no>,
Mark A. Parsons <hooru@xxxxxxxxx> wrote:

Grab a copy of the 4th Reference Manual (Tables).

Then have a gander at the various status/status2 settings for sysindexes.

Looks like sysindexes.status & 2048 => index on primary key, while
sysindexes.status2 & 2 => constraint supports primary key.

See if these settings match with what's in your sysindexes table for some
of the tables you've configured with primary keys.

----------------

As for sp_primarykey/sp_commonkey/sp_foreignkey), these are primarily for
documenation purposes and to provide support for 3rd party tools which are
too lazy to go looking in the other system tables ;-)


John Brock wrote:

I have an existing Sybase database (ASE 12.5) in which every table
is defined with a primary key, e.g.:

create table City (
City varchar(20) not null,
Region varchar(20) not null,
primary key (City)
)

Is there a way to look at the system tables and discover what the
primary key on this table is?

I know that there is an entry in sysindexes. But some tables have
multiple indexes, and it is not clear to me from looking at the
definition of sysindexes that there is any way to pick out which
index enforces the primary key relationship.

I also looked at syskeys, but right now it is essentially empty.
I know that I could populate it using sp_primarykey, but that would
require that I update the code that generates our database definitions
so to generate the appropriate sp_primarykey statements. I can do
that, but if there is a way to get this information from the database
as it currently stands that would be preferable. Is there a way?

I have to say I really don't understand the reasoning behind
sp_primarykey. It seems to me that syskeys should be a read-only
table that is populated automatically when tables and relationships
are created. This would be less work for the database designer,
and would also reduce the possibility of errors (note that
sp_primarykey can be used to define "primary keys" that are completely
nonsensical!). Is there some good reason for doing things this
way, or is sp_primarykey basically a legacy misfeature?




--
John Brock
jbrock@xxxxxxxxx

.

Relevant Pages

  • Re: How do I discover a tables primary key?
    ... -- Number of references made by this table: ... But when I execute the same statement ("sp_helpconstraint City") ... from within my program I only get back one row, the PRIMARY KEY ... Why does the system procedure return ...
    (comp.databases.sybase)
  • Re: How do I discover a tables primary key?
    ... column names of the primary key on a table) by calling index_col ... I would have to go to sysindexes to get it. ... sp_helpconstraints system procedure in isql the result looks like ... the result I get when I do an ordinary select command (the part up ...
    (comp.databases.sybase)
  • Re: How do I discover a tables primary key?
    ... calling sp_helpconstraint from isql gives different ... -- Number of references made by this table: ... But when I execute the same statement ("sp_helpconstraint City") ... from within my program I only get back one row, the PRIMARY KEY ...
    (comp.databases.sybase)
  • Re: Access as a RDBMS--why the multiple relationships?
    ... non-unique field in TABLE B. In the relationship diagram this shows ... referenced field is not a primary key, ... (CompositePartNo Integer references PARTS.PartNo, ... ComponentPartnentNo Integer references PARTS.PartNo, ...
    (comp.databases.theory)
  • Re: object model to table design mapping problem
    ... the INCITS H2 Database Standards Committee(nee ANSI X3H2 ... NOT NULL PRIMARY KEY, ... REFERENCES Courses, ... ON UPDATE CASCADE ...
    (microsoft.public.sqlserver.programming)