File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Unique constraint issue ...

 
Vassili Vladimir
Ranch Hand
Posts: 1585
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

In JDBC, there are ways to obtain the primary and foreign key(s) of a table, but, is there a way to obtain other constraints like unique or others ?

Thanks in advance ...
 
Paul Campbell
Ranch Hand
Posts: 338
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can clear up one without even looking... a SQL primary key is always a unique key and it cannot be null. The last part... cannot be null is the difference between a primary key and a unique key.
 
Vassili Vladimir
Ranch Hand
Posts: 1585
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But how can you identify a non primary column was unique or not using JDBC ?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33671
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Vassili,
Many databases store the constraint definitions in a system table which you can query. Which database are you using?
 
Vassili Vladimir
Ranch Hand
Posts: 1585
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm using MySQL, can you please tell me which table(s) to use ?
 
Paul Campbell
Ranch Hand
Posts: 338
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
information_schema.key_column_usage

If the constraint is a foreign key, then this is the column of the foreign key, not the column that the foreign key references.

The value of ORDINAL_POSITION is the column's position within the constraint, not the column's position within the table. Column positions are numbered beginning with 1.

The value of POSITION_IN_UNIQUE_CONSTRAINT is NULL for unique and primary-key constraints. For foreign-key constraints, it is the ordinal position in key of the table that is being referenced.

Table constraints are in information_schema.table_constraints table.

The CONSTRAINT_TYPE value can be UNIQUE, PRIMARY KEY, or FOREIGN KEY.

The UNIQUE and PRIMARY KEY information is about the same as what you get from the Key_name field in the output from SHOW INDEX when the Non_unique field is 0.

The CONSTRAINT_TYPE column can contain one of these values: UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK. This is a CHAR (not ENUM) column.
[ October 07, 2007: Message edited by: Paul Campbell ]
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic