Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Question Unanswered: Oracle Doesn't Display Constraints

    I notice in Oracle 11g when I describe a table, it doesn't list UNIQUE or NOT NULL constraints using SQL*Plus. I was just wondering if there's a reason for this? I'm not familiar with Oracle to a strong degree so I searched and only found:

    constraint

    Code:
    SQL> describe CARLOS.USERS;
     Name					   Null?    Type
     ----------------------------------------- -------- ----------------------------
     USERS_ID				   NOT NULL NUMBER(38)
     USERS_NAME				   NOT NULL VARCHAR2(100)
     USERS_EMAIL				   NOT NULL VARCHAR2(100)
    I read that I can CREATE VIEW that display any of the 6 possible table constraints but not sure if CREATE VIEW is the ONLY way in Oracle I can view table constraints when I query the table.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    query ALL_CONSTRAINTS joined to ALL_CONS_COLUMNS
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Quote Originally Posted by anacedent View Post
    query ALL_CONSTRAINTS joined to ALL_CONS_COLUMNS
    Sorry I'm a noob

    Is the above a SQL command? I tried throwing it into SQL*Plus and it didn't stick.

    I'm guessing I need to replace some values above [CAPS] with my own values, no?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    08:04:32 SQL> desc all_constraints
     Name					   Null?    Type
     ----------------------------------------- -------- ----------------------------
     OWNER						    VARCHAR2(30)
     CONSTRAINT_NAME			   NOT NULL VARCHAR2(30)
     CONSTRAINT_TYPE				    VARCHAR2(1)
     TABLE_NAME				   NOT NULL VARCHAR2(30)
     SEARCH_CONDITION				    LONG
     R_OWNER					    VARCHAR2(30)
     R_CONSTRAINT_NAME				    VARCHAR2(30)
     DELETE_RULE					    VARCHAR2(9)
     STATUS 					    VARCHAR2(8)
     DEFERRABLE					    VARCHAR2(14)
     DEFERRED					    VARCHAR2(9)
     VALIDATED					    VARCHAR2(13)
     GENERATED					    VARCHAR2(14)
     BAD						    VARCHAR2(3)
     RELY						    VARCHAR2(4)
     LAST_CHANGE					    DATE
     INDEX_OWNER					    VARCHAR2(30)
     INDEX_NAME					    VARCHAR2(30)
     INVALID					    VARCHAR2(7)
     VIEW_RELATED					    VARCHAR2(14)
    
    08:04:40 SQL> desc all_cons_columns
     Name					   Null?    Type
     ----------------------------------------- -------- ----------------------------
     OWNER					   NOT NULL VARCHAR2(30)
     CONSTRAINT_NAME			   NOT NULL VARCHAR2(30)
     TABLE_NAME				   NOT NULL VARCHAR2(30)
     COLUMN_NAME					    VARCHAR2(4000)
     POSITION					    NUMBER
    
    08:04:49 SQL>
    do you know how to write SQL that joins two views?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Quote Originally Posted by anacedent View Post
    do you know how to write SQL that joins two views?
    I sadly do NOT yet...

    I'm just learning the Oracle RDBMS and how it manages data. I've never written SQL or joined any relational tables together in a view or trigger. I can only use SELECT, UPDATE, ALTER, GRANT, DROP, CREATE statements to a very minimal / novice level. Sorry.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
      1  SELECT constraint_type,
      2	    Count(*)
      3  FROM   user_constraints
      4  GROUP  BY constraint_type
      5* ORDER  BY 1
    10:21:55 SQL> /
    
    C   COUNT(*)
    - ----------
    C	  32
    O	   1
    P	   8
    R	  10
    U	   1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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