windows-1252?Q52=65=3A=20=77=69=6E=64=6F=77=73=2D=
A unique index is not neccessarily a primary key constraint, or even a unique
key constraint. While both primary key and unique constraints REQUIRE a unique
index to function (and adding the constraint will build one if an appropriate
index does not exist already) a unique index is NOT a constraint. If you want
to know whether any table has NO UNIQUE indexes, that's another query
altogether:
SELECT tabname
FROM systables as st
LEFT OUTER JOIN sysindexes as si
ON si.tabid = st.tabid AND si.idxtype = 'U'
WHERE si.tabid IS NULL;
That will pick up ANY tables that have no primary key constraint, no unique
constraint, and no unique indexes that are not supporting such a constraint.
Art S. Kagel
----- Original Message -----
From: Jonathan Smaby <ids@iiug.org>
To: ids@iiug.org
At: 10/26 14:28:06
Thank-you Art for the SQL.
I ran this and I've picket up several false-positives. One of my tables,
acadsum_table has following schema index/key:
index
unique acadsum_prim on (id, prog, sess, yr)
acadsum_key on (prog, sess, yr)
acadsum_key2 on (yr, sess, id)
acadsum_id on (id)
Any suggestions?
Thanks again for your help, I'm much close than before.
Merci =E0 l'avance
Jonathan B. Smaby
DBSA
Pomona College, ITS-AISO Office
jonathan.smaby@pomona.edu
Tel. (909) 621-8506
Web. http://aiso.pomona.edu
--
=B3Experience is something you don't get until just after you need it.=B2
~Steven Wright
-----Original Message-----
> From: "ART KAGEL, BLOOMBERG/ 731 LEXIN" <kagel@bloomberg.net>
> Reply-To: <ids@iiug.org>
> Date: Fri, 26 Oct 2007 13:33:31 -0400 (EDT)
> To: <ids@iiug.org>
> Subject:
windows-1252?Q52=3D65=3D3A=3D48=3D65=3D6C=3D70=3D20=3D66=3D69=3D6E.... [10274]
>=20
> select tabname=20
> from systables as st
> left outer join sysconstraints as sc
> on st.tabid =3D sc.tabid and st.tabid >=3D 100 {No system catalog tables}
> and sc.constrtype =3D 'P'
> WHERE sc.constrid is NULL;
>=20
> Art S. Kagel=20
>=20
> ----- Original Message -----
> From: Jonathan Smaby <ids@iiug.org>
> To: ids@iiug.org=20
> At: 10/26 13:14:03
>=20
> Good morning,=20
>=20
> I have IDS 10.00.FC5 under HPUX 11.23. If there is a way to query sysmast=
e=3D
> r=20
> / data-dictionary to identify tables that don=3DB9t have a Primary Key, I w=
ould
> be eternally grateful for any assistance.
>=20
> Merci =3DE0 l'avance
>=20
> Jonathan B. Smaby
> DBSA=20
> Pomona College, ITS-AISO Office
> jonathan.smaby@pomona.edu
> Web. http://www.pomona.edu
>=20
> --=20
> "In theory, there is no difference between theory and practice. But, in
> practice, there is."
> ~ Jan L. A. Van de Snepscheut (1953-1994)
>=20
> -------------------------------------------------------------
> This message has been scanned by Postini anti-virus software.
> =3D0D=20
>=20
>=20
> *************************************************************************=
*****
> *=20
> Forum Note: Use "Reply" to post a response in the discussion forum.
>=20
>=20
> *************************************************************************=
*****
> *=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20
-------------------------------------------------------------
This message has been scanned by Postini anti-virus software.
=0D
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.