4

I have a table named STUDENT with following attributes:

enroll VARCHAR(20), 
name VARCHAR(50), 
age INT, 
fees FLOAT(6,2)

Now I want to know that which all constraints (like Primary Key, Unique Key, NOT NULL, etc) are present in this table. Kindly let me know how can I get this?

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
rohit
  • 51
  • 2
  • 2
  • 9
  • I am looking for some query like 'SHOW INDEX FROM student ' which gives details of table in MySQL – rohit Oct 11 '16 at 05:37

4 Answers4

3

You can get it from user_constraints

SELECT *
FROM user_constraints
WHERE table_name = 'STUDENT'

Make sure you pass the STUDENT in caps. table_name is case sensitive

To know the constraint type check this column CONSTRAINT_TYPE

Type of constraint definition:

C (check constraint on a table)
P (primary key)
U (unique key)
R (referential integrity)
V (with check option, on a view)
O (with read only, on a view)
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • Hi Prdp, Thanks for your reply.. please can you explain me that what is user_constraints. Is is a keyword? This code is not working or I am unable to use it properly. – rohit Oct 11 '16 at 03:26
  • @rohit - Looks like it shows constraint definitions on tables owned by the current user. – Pரதீப் Oct 11 '16 at 03:37
  • @rohit - Also sure you pass the STUDENT in caps. table_name is case sensitive – Pரதீப் Oct 11 '16 at 03:40
  • Hi Prdp, Thanks it worked and showing lots of details. But still I am unable to get the details of Primary Key, Unique Keys etc through this. I am looking for some query like 'SHOW INDEX FROM STUDENT' query used in MySQL which clearly gives the details of all the constraints present in various columns. – rohit Oct 11 '16 at 04:22
  • @rohit - It will give the information of below constraints `(check constraint on a table) ,(primary key) ,(unique key) ,(referential integrity)`. Make sure you table you pass has these constraint – Pரதீப் Oct 11 '16 at 04:25
  • Hi Prdp, I am getting this result... http://ingenius.in/sql.jpg ...but I am unable to find constraints like Primary Key, NOT NULL, etc which are already present in this table – rohit Oct 11 '16 at 04:38
3

You can use the JOIN on two tables USER_CONSTRAINTS and USER_CONS_COLUMNS to fetch the Column Name, Constraint Type and Table Name.

SELECT ucc.COLUMN_NAME, uc.CONSTRAINT_TYPE ,uc.TABLE_NAME 
FROM USER_CONSTRAINTS uc  JOIN  USER_CONS_COLUMNS ucc 
ON uc.CONSTRAINT_NAME = ucc.CONSTRAINT_NAME 
AND uc.TABLE_NAME=ucc.TABLE_NAME
WHERE uc.TABLE_NAME='mytablename';

Constraint Definition can be referred as:

C (check constraint on a table)
P (primary key)
U (unique key)
R (referential integrity)
V (with check option, on a view)
O (with read only, on a view)

For more information you can view Oracle Documentation here

Ashish
  • 1,309
  • 1
  • 11
  • 17
0

Try this View query and specify your Table name to get all the constrains detail.

SELECT * FROM user_cons_columns WHERE table_name = '<YOUR_TABLE_NAME>';

Or

SELECT * FROM user_constraints WHERE table_name = '<your table name>' AND constraint_name = '<your constraint name>';

Ashwin Parmar
  • 3,025
  • 3
  • 26
  • 42
0

just use describe

describe student but remember you should use Web browser mode for it

Kartik Agarwal
  • 1,129
  • 1
  • 8
  • 27
  • Hi Kartik, Thanks for your reply but it is showing only NULL constraint. Kindly let me know that how to use it to get the details of other constraints like UNIQUE KEY, PRIMARY KEY, etc – rohit Oct 11 '16 at 05:29
  • Hi Rohit, as i mentioned If you are using SQL command Line from `describe` you can only see NULL constraints but in case of Web Browser mode you can see all of your constraints using `describe` – Kartik Agarwal Oct 11 '16 at 05:43
  • Hi Goldy, Thank for your reply. But I need the same to be done in command prompt. Kindly assist me in this. – rohit Oct 11 '16 at 06:07