OraFAQ Forum: Server Administration » Displaying Column constraints

Home » RDBMS Server » Server Administration » Displaying Column constraints

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Displaying Column constraints [message #59087]

Tue, 28 October 2003 00:36

Johnny
Messages: 15
Registered: October 2000

Junior Member

lets say i create a table as such:

CREATE TABLE temp(
empno number(3) CONSTRAINT temp_empno_pk primary key,
lname char(30),
fname char(30));

how do i go about displaying the constraint information of the table i just created?

Report message to a moderator

Re: Displaying Column constraints [message #59088 is a reply to message #59087]

Tue, 28 October 2003 01:11

Maaher
Messages: 7065
Registered: December 2001

Senior Member

You can make use of 2 dictionary tables:
USER_CONSTRAINTS and USER_CONS_COLUMNS:

SQL> CREATE TABLE temp(
  2  empno number(3) CONSTRAINT temp_empno_pk primary key,
  3  lname char(30),
  4  fname char(30));

Table created.

SQL> Select constraint_name
  2       , constraint_type
  3    From user_constraints
  4   Where table_name = 'TEMP';

CONSTRAINT_NAME                C
------------------------------ -
TEMP_EMPNO_PK                  P

SQL> Select column_name
  2    From user_cons_columns
  3   Where constraint_name = 'TEMP_EMPNO_PK';

COLUMN_NAME
----------------------------------------------------------
EMPNO

SQL>

You need a free subscription for the links.

MHE

Report message to a moderator

Re: Displaying Column constraints [message #59090 is a reply to message #59088]

Tue, 28 October 2003 02:44

Johnny
Messages: 15
Registered: October 2000

Junior Member

Hello, thank you for your help.

Lets say instead, that i created the table w/ out naming the constraint and w/ out defining empno as a primary key. Then, i add about 30 rows of data and finally define the constraint/primary key after adding the data by:

Alter Table temp
Add Constraint temp_empno_pk
Primary Key(empno);

In order to see the constraint i would type:

Select constraint_name, constraint_type
From user_constraints
Where table_name = 'TEMP';

I'd expect the same result, but for some reason i'm receiving "no rows selected". Would u happen to know y?

Report message to a moderator

Re: Displaying Column constraints [message #59093 is a reply to message #59090]

Tue, 28 October 2003 04:32

Maaher
Messages: 7065
Registered: December 2001

Senior Member

One reason could be that the user your connected with is not the owner of the table. Try this instead:

SQL> select constraint_name
  2       , constraint_type
  3     from user_constraints
  4   where table_name = 'TEST';

no rows selected

SQL> ed
Wrote file afiedt.buf

  1  select owner
  2       , constraint_name
  3       , constraint_type
  4     from all_constraints
  5*  where table_name = 'TEST'
SQL> /

OWNER                          CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
MHE                            TEST_PK                        P

SQL>

MHE

Report message to a moderator

Previous Topic:	error
Next Topic:	ORA-00020 maximum number of processes (150) exceeded

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 15:30:57 CDT 2024