0
# Constraints and Relationships
1

2
Complete constraint discovery and relationship navigation including primary keys, foreign keys, unique constraints, check constraints, and indexes with comprehensive relationship analysis.
3

4
## Capabilities
5

6
### Primary Key Analysis
7

8
Primary key discovery and analysis for table identification and relationship navigation.
9

10
```java { .api }
11
/**
12
 * Gets the table's primary key constraint
13
 * @returns UniqueKeyDefinition for primary key or null if none
14
 */
15
UniqueKeyDefinition getPrimaryKey();
16

17
/**
18
 * Gets all primary keys across all schemas
19
 * @returns List of all primary key definitions
20
 */
21
List<UniqueKeyDefinition> getPrimaryKeys();
22

23
/**
24
 * Gets primary keys within a specific schema
25
 * @param schema - Schema to search within
26
 * @returns List of primary key definitions in the schema
27
 */
28
List<UniqueKeyDefinition> getPrimaryKeys(SchemaDefinition schema);
29

30
/**
31
 * Checks if a unique key is a primary key
32
 * @returns true if this unique key is the primary key
33
 */
34
boolean isPrimaryKey();
35
```
36

37
**Usage Examples:**
38

39
```java
40
import org.jooq.meta.TableDefinition;
41
import org.jooq.meta.UniqueKeyDefinition;
42
import org.jooq.meta.ColumnDefinition;
43

44
// Get table primary key
45
TableDefinition table = database.getTable(schema, "users");
46
UniqueKeyDefinition primaryKey = table.getPrimaryKey();
47

48
if (primaryKey != null) {
49
    System.out.println("Primary Key: " + primaryKey.getName());
50
    List<ColumnDefinition> keyColumns = primaryKey.getKeyColumns();
51
    System.out.println("Key Columns: ");
52
    
53
    for (ColumnDefinition column : keyColumns) {
54
        System.out.println("  - " + column.getName() + " (" + column.getType().getType() + ")");
55
    }
56
}
57

58
// Get all primary keys in schema
59
List<UniqueKeyDefinition> allPrimaryKeys = database.getPrimaryKeys(schema);
60
for (UniqueKeyDefinition pk : allPrimaryKeys) {
61
    System.out.println("Table: " + pk.getTable().getName() + 
62
                      ", PK: " + pk.getName() + 
63
                      ", Columns: " + pk.getKeyColumns().size());
64
}
65
```
66

67
### Unique Key Analysis
68

69
Unique constraint discovery and analysis for data integrity understanding.
70

71
```java { .api }
72
/**
73
 * Gets all unique constraints for the table
74
 * @returns List of unique key definitions (including primary key)
75
 */
76
List<UniqueKeyDefinition> getUniqueKeys();
77

78
/**
79
 * Gets all unique keys across all schemas
80
 * @returns List of all unique key definitions
81
 */
82
List<UniqueKeyDefinition> getUniqueKeys();
83

84
/**
85
 * Gets unique keys within a specific schema
86
 * @param schema - Schema to search within
87
 * @returns List of unique key definitions in the schema
88
 */
89
List<UniqueKeyDefinition> getUniqueKeys(SchemaDefinition schema);
90

91
/**
92
 * Gets combined primary and unique keys
93
 * @returns List of all key definitions (primary + unique)
94
 */
95
List<UniqueKeyDefinition> getKeys();
96

97
/**
98
 * Gets combined primary and unique keys for a schema
99
 * @param schema - Schema to search within
100
 * @returns List of all key definitions in the schema
101
 */
102
List<UniqueKeyDefinition> getKeys(SchemaDefinition schema);
103
```
104

105
**Usage Examples:**
106

107
```java
108
import org.jooq.meta.UniqueKeyDefinition;
109
import org.jooq.meta.ColumnDefinition;
110

111
// Get all unique constraints for table
112
List<UniqueKeyDefinition> uniqueKeys = table.getUniqueKeys();
113
for (UniqueKeyDefinition key : uniqueKeys) {
114
    System.out.println("Unique Key: " + key.getName());
115
    System.out.println("  Primary: " + key.isPrimaryKey());
116
    System.out.println("  Columns: ");
117
    
118
    for (ColumnDefinition column : key.getKeyColumns()) {
119
        System.out.println("    - " + column.getName());
120
    }
121
}
122

123
// Separate primary key from other unique keys
124
List<UniqueKeyDefinition> allKeys = table.getUniqueKeys();
125
UniqueKeyDefinition primaryKey = allKeys.stream()
126
    .filter(UniqueKeyDefinition::isPrimaryKey)
127
    .findFirst()
128
    .orElse(null);
129

130
List<UniqueKeyDefinition> uniqueConstraints = allKeys.stream()
131
    .filter(key -> !key.isPrimaryKey())
132
    .collect(Collectors.toList());
133
```
134

135
### Foreign Key Analysis
136

137
Foreign key relationship discovery and navigation for referential integrity understanding.
138

139
```java { .api }
140
/**
141
 * Gets all foreign keys for the table
142
 * @returns List of foreign key definitions
143
 */
144
List<ForeignKeyDefinition> getForeignKeys();
145

146
/**
147
 * Gets all foreign keys across all schemas
148
 * @returns List of all foreign key definitions
149
 */
150
List<ForeignKeyDefinition> getForeignKeys();
151

152
/**
153
 * Gets foreign keys within a specific schema
154
 * @param schema - Schema to search within
155
 * @returns List of foreign key definitions in the schema
156
 */
157
List<ForeignKeyDefinition> getForeignKeys(SchemaDefinition schema);
158

159
/**
160
 * Gets foreign key columns (referencing columns)
161
 * @returns List of columns that are part of this foreign key
162
 */
163
List<ColumnDefinition> getKeyColumns();
164

165
/**
166
 * Gets the referenced unique key
167
 * @returns UniqueKeyDefinition that this foreign key references
168
 */
169
UniqueKeyDefinition getReferencedKey();
170

171
/**
172
 * Gets referenced columns (columns being referenced)
173
 * @returns List of columns in the referenced table
174
 */
175
List<ColumnDefinition> getReferencedColumns();
176
```
177

178
**Usage Examples:**
179

180
```java
181
import org.jooq.meta.ForeignKeyDefinition;
182
import org.jooq.meta.UniqueKeyDefinition;
183
import org.jooq.meta.ColumnDefinition;
184

185
// Analyze foreign keys for table
186
List<ForeignKeyDefinition> foreignKeys = table.getForeignKeys();
187
for (ForeignKeyDefinition fk : foreignKeys) {
188
    System.out.println("\nForeign Key: " + fk.getName());
189
    
190
    // Get referencing columns
191
    List<ColumnDefinition> keyColumns = fk.getKeyColumns();
192
    System.out.println("  Referencing Columns:");
193
    for (ColumnDefinition col : keyColumns) {
194
        System.out.println("    - " + col.getName());
195
    }
196
    
197
    // Get referenced table and columns
198
    UniqueKeyDefinition referencedKey = fk.getReferencedKey();
199
    System.out.println("  Referenced Table: " + referencedKey.getTable().getName());
200
    System.out.println("  Referenced Key: " + referencedKey.getName());
201
    
202
    List<ColumnDefinition> referencedColumns = fk.getReferencedColumns();
203
    System.out.println("  Referenced Columns:");
204
    for (ColumnDefinition col : referencedColumns) {
205
        System.out.println("    - " + col.getName());
206
    }
207
}
208

209
// Find relationships between specific tables
210
TableDefinition ordersTable = database.getTable(schema, "orders");
211
TableDefinition customersTable = database.getTable(schema, "customers");
212

213
List<ForeignKeyDefinition> ordersFKs = ordersTable.getForeignKeys();
214
for (ForeignKeyDefinition fk : ordersFKs) {
215
    if (fk.getReferencedKey().getTable().equals(customersTable)) {
216
        System.out.println("Found relationship: orders -> customers via " + fk.getName());
217
    }
218
}
219
```
220

221
### Check Constraint Analysis
222

223
Check constraint discovery for business rule and data validation understanding.
224

225
```java { .api }
226
/**
227
 * Gets all check constraints for the table
228
 * @returns List of check constraint definitions
229
 */
230
List<CheckConstraintDefinition> getCheckConstraints();
231

232
/**
233
 * Gets check constraints within a specific schema
234
 * @param schema - Schema to search within
235
 * @returns List of check constraint definitions in the schema
236
 */
237
List<CheckConstraintDefinition> getCheckConstraints(SchemaDefinition schema);
238

239
/**
240
 * Gets the check constraint expression
241
 * @returns SQL expression string for the check constraint
242
 */
243
String getCheckClause();
244
```
245

246
**Usage Examples:**
247

248
```java
249
import org.jooq.meta.CheckConstraintDefinition;
250

251
// Analyze check constraints
252
List<CheckConstraintDefinition> checkConstraints = table.getCheckConstraints();
253
for (CheckConstraintDefinition check : checkConstraints) {
254
    System.out.println("Check Constraint: " + check.getName());
255
    System.out.println("  Expression: " + check.getCheckClause());
256
    System.out.println("  Table: " + check.getTable().getName());
257
}
258

259
// Find constraints with specific patterns
260
List<CheckConstraintDefinition> ageConstraints = table.getCheckConstraints().stream()
261
    .filter(check -> check.getCheckClause().contains("age"))
262
    .collect(Collectors.toList());
263
```
264

265
### Index Analysis
266

267
Database index discovery and analysis for performance optimization understanding.
268

269
```java { .api }
270
/**
271
 * Gets all indexes for the table
272
 * @returns List of index definitions
273
 */
274
List<IndexDefinition> getIndexes();
275

276
/**
277
 * Gets indexes within a specific schema
278
 * @param schema - Schema to search within
279
 * @returns List of index definitions in the schema
280
 */
281
List<IndexDefinition> getIndexes(SchemaDefinition schema);
282

283
/**
284
 * Gets indexes for a specific table
285
 * @param table - Table to get indexes for
286
 * @returns List of index definitions for the table
287
 */
288
List<IndexDefinition> getIndexes(TableDefinition table);
289

290
/**
291
 * Gets the table this index belongs to
292
 * @returns TableDefinition for the indexed table
293
 */
294
TableDefinition getTable();
295

296
/**
297
 * Gets index column definitions
298
 * @returns List of index column definitions with sort order
299
 */
300
List<IndexColumnDefinition> getIndexColumns();
301

302
/**
303
 * Checks if index is unique
304
 * @returns true if index enforces uniqueness
305
 */
306
boolean isUnique();
307
```
308

309
**Usage Examples:**
310

311
```java
312
import org.jooq.meta.IndexDefinition;
313
import org.jooq.meta.IndexColumnDefinition;
314

315
// Analyze table indexes
316
List<IndexDefinition> indexes = table.getIndexes();
317
for (IndexDefinition index : indexes) {
318
    System.out.println("\nIndex: " + index.getName());
319
    System.out.println("  Unique: " + index.isUnique());
320
    System.out.println("  Table: " + index.getTable().getName());
321
    System.out.println("  Columns:");
322
    
323
    for (IndexColumnDefinition indexCol : index.getIndexColumns()) {
324
        System.out.println("    - " + indexCol.getColumn().getName() + 
325
                          " (" + indexCol.getSortOrder() + ")");
326
    }
327
}
328

329
// Find unique indexes
330
List<IndexDefinition> uniqueIndexes = table.getIndexes().stream()
331
    .filter(IndexDefinition::isUnique)
332
    .collect(Collectors.toList());
333

334
// Find indexes on specific columns
335
String columnName = "email";
336
List<IndexDefinition> emailIndexes = table.getIndexes().stream()
337
    .filter(index -> index.getIndexColumns().stream()
338
        .anyMatch(col -> col.getColumn().getName().equals(columnName)))
339
    .collect(Collectors.toList());
340
```
341

342
### Identity Column Analysis
343

344
Identity and auto-increment column discovery and analysis.
345

346
```java { .api }
347
/**
348
 * Gets identity columns within a specific schema
349
 * @param schema - Schema to search within
350
 * @returns List of identity definitions in the schema
351
 */
352
List<IdentityDefinition> getIdentities(SchemaDefinition schema);
353

354
/**
355
 * Gets the identity column for the table
356
 * @returns IdentityDefinition or null if no identity column
357
 */
358
IdentityDefinition getIdentity();
359

360
/**
361
 * Gets the column that is the identity column
362
 * @returns ColumnDefinition for the identity column
363
 */
364
ColumnDefinition getColumn();
365
```
366

367
### Relationship Navigation
368

369
Advanced relationship navigation and discovery using the Relations interface.
370

371
```java { .api }
372
/**
373
 * Gets the relationship manager for the database
374
 * @returns Relations instance for relationship navigation
375
 */
376
Relations getRelations();
377

378
/**
379
 * Gets primary key using relations manager
380
 * @param table - Table to get primary key for
381
 * @returns UniqueKeyDefinition for primary key
382
 */
383
UniqueKeyDefinition getPrimaryKey(TableDefinition table);
384

385
/**
386
 * Gets unique keys using relations manager
387
 * @param table - Table to get unique keys for
388
 * @returns List of unique key definitions
389
 */
390
List<UniqueKeyDefinition> getUniqueKeys(TableDefinition table);
391

392
/**
393
 * Gets foreign keys using relations manager
394
 * @param table - Table to get foreign keys for
395
 * @returns List of foreign key definitions
396
 */
397
List<ForeignKeyDefinition> getForeignKeys(TableDefinition table);
398

399
/**
400
 * Gets exported foreign keys (keys referencing this table)
401
 * @param table - Table to get exported keys for
402
 * @returns List of foreign keys that reference this table
403
 */
404
List<ForeignKeyDefinition> getExportedKeys(TableDefinition table);
405
```
406

407
## Types
408

409
```java { .api }
410
interface UniqueKeyDefinition extends ConstraintDefinition {
411
    boolean isPrimaryKey();
412
    List<ColumnDefinition> getKeyColumns();
413
    List<ForeignKeyDefinition> getReferencingForeignKeys();
414
    TableDefinition getTable();
415
}
416

417
interface ForeignKeyDefinition extends ConstraintDefinition {
418
    List<ColumnDefinition> getKeyColumns();
419
    UniqueKeyDefinition getReferencedKey();
420
    List<ColumnDefinition> getReferencedColumns();
421
    TableDefinition getTable();
422
}
423

424
interface CheckConstraintDefinition extends ConstraintDefinition {
425
    String getCheckClause();
426
    TableDefinition getTable();
427
}
428

429
interface IndexDefinition extends Definition {
430
    TableDefinition getTable();
431
    List<IndexColumnDefinition> getIndexColumns();
432
    boolean isUnique();
433
}
434

435
interface IdentityDefinition extends Definition {
436
    ColumnDefinition getColumn();
437
    TableDefinition getTable();
438
}
439

440
interface Relations {
441
    UniqueKeyDefinition getPrimaryKey(TableDefinition table);
442
    List<UniqueKeyDefinition> getUniqueKeys(TableDefinition table);
443
    List<ForeignKeyDefinition> getForeignKeys(TableDefinition table);
444
    List<ForeignKeyDefinition> getExportedKeys(TableDefinition table);
445
}
446
```
447

448
**Usage Examples:**
449

450
```java
451
import org.jooq.meta.Relations;
452
import org.jooq.meta.UniqueKeyDefinition;
453
import org.jooq.meta.ForeignKeyDefinition;
454

455
// Complete relationship analysis using Relations
456
Relations relations = database.getRelations();
457

458
TableDefinition customersTable = database.getTable(schema, "customers");
459
TableDefinition ordersTable = database.getTable(schema, "orders");
460

461
// Get primary keys
462
UniqueKeyDefinition customerPK = relations.getPrimaryKey(customersTable);
463
UniqueKeyDefinition orderPK = relations.getPrimaryKey(ordersTable);
464

465
// Get foreign keys
466
List<ForeignKeyDefinition> customerFKs = relations.getForeignKeys(customersTable);
467
List<ForeignKeyDefinition> orderFKs = relations.getForeignKeys(ordersTable);
468

469
// Get exported keys (foreign keys that reference this table)
470
List<ForeignKeyDefinition> customerExported = relations.getExportedKeys(customersTable);
471

472
System.out.println("Tables referencing customers:");
473
for (ForeignKeyDefinition exportedFK : customerExported) {
474
    System.out.println("  " + exportedFK.getTable().getName() + 
475
                      " via " + exportedFK.getName());
476
}
477
```