Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184

    How find constraints on a table

    Is there any view/table that gives me information in db2 to find the constraints defined on a table?... like user_constraints & user_cons_columns in oracle..

    TIA

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    If you use the db2look command (a DB2 command, not SQL) with the e parameter, you will see the table DDL with constraints. See the Command Reference manual.

    If you want to look at the DB2 catalog, look at Appendix D in of the SQL Manual Volume 1, where the DB2 system catalog tables are described. It's probably a bit more complex than Oracle because different constraints may be in different catalog tables, but I assume it is all there somewhere.

  3. #3
    Join Date
    Nov 2002
    Location
    vienna
    Posts
    9

    Re: How find constraints on a table

    assumeing that you are using DB2 for Unix,Linux,Windows or OS/2:

    SYSCAT.TABCONST:
    CONSTNAME VARCHAR(18)
    TABSCHEMA VARCHAR(128)
    TABNAME VARCHAR(128)
    DEFINER VARCHAR(128)
    TYPE CHAR(1) ( F= FOREIGN KEY, K=CHECK, P=PRIMARY KEY, U=UNIQUE)
    REMARKS VARCHAR(254)

    Each row represents a table constraint of type CHECK, UNIQUE, PRIMARY KEY or FOREIGN KEY
    Last edited by sandner; 09-18-03 at 02:49.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    That gives the name of the constraint, but not the definition.

  5. #5
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    Originally posted by Marcus_A
    If you use the db2look command (a DB2 command, not SQL) with the e parameter, you will see the table DDL with constraints. See the Command Reference manual.

    If you want to look at the DB2 catalog, look at Appendix D in of the SQL Manual Volume 1, where the DB2 system catalog tables are described. It's probably a bit more complex than Oracle because different constraints may be in different catalog tables, but I assume it is all there somewhere.
    Ya, thanks that was useful. However, using syscat tables is not possible for non-privileged users.

    Cheers

  6. #6
    Join Date
    Nov 2002
    Location
    vienna
    Posts
    9
    Hallo Markus

    This is a table that gives information in db2 to find the constraints defined on a table
    That's the question

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    No one has select access to any table unless someone grants it to them. Ask your DBA for select access to the catalog tables.

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,649
    The following tables also give you constraint info :

    1) SYSCAT.CHECKS - For Check Constraints - TEXT column gives the check constraint definition

    2) SYSCAT.KEYCOLUSE - Gives info on Primary Key and Foreign Key Constraints

    As Marcus indicated, you'll have to contact your administrator for SELECT permission

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •