aspose file tools*
The moose likes JDBC and the fly likes Unique constraint issue ... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Unique constraint issue ..." Watch "Unique constraint issue ..." New topic
Author

Unique constraint issue ...

Vassili Vladimir
Ranch Hand

Joined: Mar 08, 2007
Posts: 1585
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 ...


Vassili ...
SCJP 5.0, SCWCD 1.4, SCJA 1.0
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
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

Joined: Mar 08, 2007
Posts: 1585
But how can you identify a non primary column was unique or not using JDBC ?
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30929
    
158

Vassili,
Many databases store the constraint definitions in a system table which you can query. Which database are you using?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Vassili Vladimir
Ranch Hand

Joined: Mar 08, 2007
Posts: 1585
I'm using MySQL, can you please tell me which table(s) to use ?
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
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 ]
 
Consider Paul's rocket mass heater.
 
subject: Unique constraint issue ...