Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Getting all the constraints on a table.

 
Chinmay Bajikar
Ranch Hand
Posts: 159
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,
Is there any way through which I can find out all the constraints in a table?
I mean is there any api which will give me a result set of constraint names on the table?
Thanks,
Chinmay....
 
Tina Coleman
Ranch Hand
Posts: 150
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
DatabaseMetaData seems to give the PK constraint info (getPrimaryKeys) and the FK constraint info (several methods, including getImportedKeys, getExportedKeys, and getCrossReference), but I'm not seeing where we'd get column-level constraints. I assume it'd be in getColumns, but I'm not seeing anything specific there, other than the ability to check to see whether a column is nullable.
 
SJ Adnams
Ranch Hand
Posts: 925
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would imagine the code would be database specific since there are so many 'exotic' sorts of contraints that can be applied to columns.
What do you need this info for? Are you writing a toad like application?
 
Chinmay Bajikar
Ranch Hand
Posts: 159
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Thanks Tina and Simon for ur replies..
Tina,I m aware of the methods u mentioned.
Right now I am using the getExportedKeys() and it retrns me a ResultSet of constraint-names and table-names.
Sadly there seems to be nothing like this for getting all the constraints.
Simon,I m writing a wierd application wherein i m required to delete some tables based on user selections.But have to take care of the drop table queries failing as there are conflicting constraints.
(And btw what is Toad??)
 
Volodymyr Lysenko
Ranch Hand
Posts: 512
1
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I understand that this thread is very old.
But it is the first on google.

In mysql 5 there is table called table_constraints. It has data about table constraints:
To get all constraints of mysql table 'answer' I query:

select * from information_schema.table_constraints where table_name='answer';

Also there is table referential_constraints that contains more data of referential constraints(such as on update, on delete)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic