Displaying the constraints in a table

Question

Hello I am trying to display the constraints in one of my tables but for some reason I get the message no rows selected. Noted below is the table I have created.

Create table Teams (
   TeamID varCHAR2(4) constraint Teams_TeamID_PK Primary Key,
   TeamName VARCHAR2(40) 
);

This is the code I am using to show my constraints.

SELECT constraint_name, 
       constraint_type,
       search_condition
  FROM USER_CONSTRAINTS
 WHERE table_name = 'Teams';

I am a rookie so I want to make sure I understand what is wrong. I have tried to drop the table thinking that my constraints did not take - I did not, nor did I receive any errors when I created the table and I am referencing TeamID in another table. So when I try to drop the table I get an error message when is what I was hoping for.

Your issue strikes me as that you are performing this actions as different users and/or aren't committing the transaction when you create the table. — OMG Ponies, Dec 03 '09 at 05:56
CREATE TABLE is DDL that autocommits upon execution in Oracle. — DCookie, Dec 03 '09 at 06:05
You can't drop a table that is referenced by other tables, unless you specify "CASCADE CONSTRAINTS", i.e. "DROP TABLE Teams CASCADE CONSTRAINTS" — Erich Kitzmueller, Dec 03 '09 at 09:11

score 23 · Answer 1 · answered Dec 03 '09 at 06:04

23

Try this:

SELECT constraint_name, 
       constraint_type,
       search_condition
  FROM USER_CONSTRAINTS
 WHERE table_name = 'TEAMS';

Unless double-quoted when created, all object names in Oracle are upper case.

answered Dec 03 '09 at 06:04

DCookie

42,630
11
83
92

score 5 · Answer 2 · answered Nov 04 '16 at 08:28

5

I personally use:

SELECT * FROM all_constraints WHERE Table_Name = <TableName>;

answered Nov 04 '16 at 08:28

Tenzin

2,415
2
23
36

score 4 · Answer 3 · edited Nov 18 '16 at 23:54

4

Use the following code:

show create table table_name;

edited Nov 18 '16 at 23:54

the Tin Man

158,662
42
215
303

answered Mar 21 '14 at 04:02

user3444871

49
1

Will work in MySQL but not in MS SQL Server and Oracle. – Anand Murali Mar 21 '14 at 04:28

score 1 · Answer 4 · answered Dec 03 '09 at 09:06

If you prefer the CamelCase names, your create table script should have been:

Create table "Teams" ( 
  "TeamID" varCHAR2(4) constraint "Teams_TeamID_PK" Primary Key, 
  "TeamName" VARCHAR2(40)  
);

Without double-quotes Oracle helpfully converts all identifiers to uppercase :)

score 1 · Accepted Answer · edited Nov 18 '16 at 23:53

1

select dbms_mview.get_ddl('TABLE',USER,'TEAMS') from dual;

edited Nov 18 '16 at 23:53

the Tin Man

158,662
42
215
303

answered Dec 03 '09 at 12:31

Jeff Hunter

26
1

score 1 · Answer 6 · edited Apr 13 '17 at 07:15

1

Type the table name in upper case in where clause within the single quotes.

e.g. WHERE table_name = 'TEAMS';

edited Apr 13 '17 at 07:15

Stefano Zanini

5,876
2
13
33

answered Apr 13 '17 at 07:10

Rahul Kharche

11
2

1

The SQL keywords are case insensitive: http://stackoverflow.com/questions/153944/is-sql-syntax-case-sensitive – Ema.jar Apr 13 '17 at 07:23

Displaying the constraints in a table

6 Answers6

Linked

Related