0
# Schema Management
1

2
Database schema operations for creating, altering, and managing tables, columns, indexes, and constraints across different database systems with comprehensive DDL support.
3

4
## Capabilities
5

6
### Schema Builder
7

8
Creates and manages database schemas (namespaces) for organizing tables and other database objects.
9

10
```typescript { .api }
11
/**
12
 * Access the schema builder instance
13
 * @returns SchemaBuilder for database schema operations
14
 */
15
schema: Knex.SchemaBuilder;
16

17
interface SchemaBuilder {
18
  /**
19
   * Create a new database schema
20
   * @param schemaName - Name of the schema to create
21
   * @returns SchemaBuilder for chaining
22
   */
23
  createSchema(schemaName: string): SchemaBuilder;
24

25
  /**
26
   * Create schema if it doesn't exist
27
   * @param schemaName - Name of the schema to create
28
   * @returns SchemaBuilder for chaining
29
   */
30
  createSchemaIfNotExists(schemaName: string): SchemaBuilder;
31

32
  /**
33
   * Drop an existing schema
34
   * @param schemaName - Name of the schema to drop
35
   * @param cascade - Whether to cascade drop dependent objects
36
   * @returns SchemaBuilder for chaining
37
   */
38
  dropSchema(schemaName: string, cascade?: boolean): SchemaBuilder;
39

40
  /**
41
   * Drop schema if it exists
42
   * @param schemaName - Name of the schema to drop
43
   * @param cascade - Whether to cascade drop dependent objects
44
   * @returns SchemaBuilder for chaining
45
   */
46
  dropSchemaIfExists(schemaName: string, cascade?: boolean): SchemaBuilder;
47

48
  /**
49
   * Use a specific schema for subsequent operations
50
   * @param schemaName - Schema name to use
51
   * @returns SchemaBuilder with schema context
52
   */
53
  withSchema(schemaName: string): SchemaBuilder;
54
}
55
```
56

57
### Table Operations
58

59
Create, alter, and manage database tables with comprehensive DDL operations.
60

61
```typescript { .api }
62
/**
63
 * Create a new table
64
 * @param tableName - Name of the table to create
65
 * @param callback - Callback to define table structure
66
 * @returns SchemaBuilder for chaining
67
 */
68
createTable(tableName: string, callback: (tableBuilder: CreateTableBuilder) => any): SchemaBuilder;
69

70
/**
71
 * Create table if it doesn't exist
72
 * @param tableName - Name of the table to create
73
 * @param callback - Callback to define table structure
74
 * @returns SchemaBuilder for chaining
75
 */
76
createTableIfNotExists(tableName: string, callback: (tableBuilder: CreateTableBuilder) => any): SchemaBuilder;
77

78
/**
79
 * Create table like an existing table
80
 * @param tableName - Name of the new table
81
 * @param likeTableName - Name of the table to copy structure from
82
 * @param callback - Optional callback for additional modifications
83
 * @returns SchemaBuilder for chaining
84
 */
85
createTableLike(tableName: string, likeTableName: string, callback?: (tableBuilder: CreateTableBuilder) => any): SchemaBuilder;
86

87
/**
88
 * Alter an existing table
89
 * @param tableName - Name of the table to alter
90
 * @param callback - Callback to define alterations
91
 * @returns SchemaBuilder for chaining
92
 */
93
alterTable(tableName: string, callback: (tableBuilder: AlterTableBuilder) => any): SchemaBuilder;
94

95
/**
96
 * Rename a table
97
 * @param oldName - Current table name
98
 * @param newName - New table name
99
 * @returns SchemaBuilder for chaining
100
 */
101
renameTable(oldName: string, newName: string): SchemaBuilder;
102

103
/**
104
 * Drop a table
105
 * @param tableName - Name of the table to drop
106
 * @returns SchemaBuilder for chaining
107
 */
108
dropTable(tableName: string): SchemaBuilder;
109

110
/**
111
 * Drop table if it exists
112
 * @param tableName - Name of the table to drop
113
 * @returns SchemaBuilder for chaining
114
 */
115
dropTableIfExists(tableName: string): SchemaBuilder;
116

117
/**
118
 * Check if a table exists
119
 * @param tableName - Name of the table to check
120
 * @returns Promise resolving to boolean
121
 */
122
hasTable(tableName: string): Promise<boolean>;
123

124
/**
125
 * Check if a column exists in a table
126
 * @param tableName - Name of the table
127
 * @param columnName - Name of the column to check
128
 * @returns Promise resolving to boolean
129
 */
130
hasColumn(tableName: string, columnName: string): Promise<boolean>;
131
```
132

133
### View Operations
134

135
Create and manage database views and materialized views.
136

137
```typescript { .api }
138
/**
139
 * Create a database view
140
 * @param viewName - Name of the view to create
141
 * @param callback - Callback to define view query
142
 * @returns SchemaBuilder for chaining
143
 */
144
createView(viewName: string, callback: (viewBuilder: ViewBuilder) => any): SchemaBuilder;
145

146
/**
147
 * Create or replace a view
148
 * @param viewName - Name of the view
149
 * @param callback - Callback to define view query
150
 * @returns SchemaBuilder for chaining
151
 */
152
createViewOrReplace(viewName: string, callback: (viewBuilder: ViewBuilder) => any): SchemaBuilder;
153

154
/**
155
 * Create a materialized view (PostgreSQL)
156
 * @param viewName - Name of the materialized view
157
 * @param callback - Callback to define view query
158
 * @returns SchemaBuilder for chaining
159
 */
160
createMaterializedView(viewName: string, callback: (viewBuilder: ViewBuilder) => any): SchemaBuilder;
161

162
/**
163
 * Refresh a materialized view (PostgreSQL)
164
 * @param viewName - Name of the materialized view
165
 * @param concurrently - Whether to refresh concurrently
166
 * @returns SchemaBuilder for chaining
167
 */
168
refreshMaterializedView(viewName: string, concurrently?: boolean): SchemaBuilder;
169

170
/**
171
 * Drop a view
172
 * @param viewName - Name of the view to drop
173
 * @returns SchemaBuilder for chaining
174
 */
175
dropView(viewName: string): SchemaBuilder;
176

177
/**
178
 * Drop view if it exists
179
 * @param viewName - Name of the view to drop
180
 * @returns SchemaBuilder for chaining
181
 */
182
dropViewIfExists(viewName: string): SchemaBuilder;
183

184
/**
185
 * Drop a materialized view
186
 * @param viewName - Name of the materialized view to drop
187
 * @returns SchemaBuilder for chaining
188
 */
189
dropMaterializedView(viewName: string): SchemaBuilder;
190

191
/**
192
 * Drop materialized view if it exists
193
 * @param viewName - Name of the materialized view to drop
194
 * @returns SchemaBuilder for chaining
195
 */
196
dropMaterializedViewIfExists(viewName: string): SchemaBuilder;
197

198
/**
199
 * Rename a view
200
 * @param oldName - Current view name
201
 * @param newName - New view name
202
 * @returns SchemaBuilder for chaining
203
 */
204
renameView(oldName: string, newName: string): SchemaBuilder;
205

206
/**
207
 * Alter a view
208
 * @param viewName - Name of the view to alter
209
 * @param callback - Callback to define alterations
210
 * @returns SchemaBuilder for chaining
211
 */
212
alterView(viewName: string, callback: (viewBuilder: ViewBuilder) => any): SchemaBuilder;
213

214
interface ViewBuilder {
215
  /**
216
   * Define the view query
217
   * @param query - Query builder or raw SQL for the view
218
   * @returns ViewBuilder for chaining
219
   */
220
  as(query: QueryBuilder | Raw): ViewBuilder;
221

222
  /**
223
   * Set view columns
224
   * @param columns - Array of column names
225
   * @returns ViewBuilder for chaining
226
   */
227
  columns(columns: readonly string[]): ViewBuilder;
228
}
229
```
230

231
### Table Builder
232

233
Define table structure with columns, constraints, and indexes during table creation.
234

235
```typescript { .api }
236
interface CreateTableBuilder {
237
  // Primary key columns
238
  /**
239
   * Auto-incrementing integer primary key
240
   * @param columnName - Name of the column (default: 'id')
241
   * @param options - Column options
242
   * @returns ColumnBuilder for further configuration
243
   */
244
  increments(columnName?: string, options?: { primaryKey?: boolean }): ColumnBuilder;
245

246
  /**
247
   * Auto-incrementing big integer primary key
248
   * @param columnName - Name of the column (default: 'id')
249
   * @returns ColumnBuilder for further configuration
250
   */
251
  bigIncrements(columnName?: string): ColumnBuilder;
252

253
  // Numeric columns
254
  /**
255
   * Integer column
256
   * @param columnName - Name of the column
257
   * @param length - Optional length specification
258
   * @returns ColumnBuilder for further configuration
259
   */
260
  integer(columnName: string, length?: number): ColumnBuilder;
261

262
  /**
263
   * Tiny integer column (MySQL: TINYINT)
264
   * @param columnName - Name of the column
265
   * @param length - Optional length specification
266
   * @returns ColumnBuilder for further configuration
267
   */
268
  tinyint(columnName: string, length?: number): ColumnBuilder;
269

270
  /**
271
   * Small integer column
272
   * @param columnName - Name of the column
273
   * @returns ColumnBuilder for further configuration
274
   */
275
  smallint(columnName: string): ColumnBuilder;
276

277
  /**
278
   * Medium integer column (MySQL: MEDIUMINT)
279
   * @param columnName - Name of the column
280
   * @returns ColumnBuilder for further configuration
281
   */
282
  mediumint(columnName: string): ColumnBuilder;
283

284
  /**
285
   * Big integer column
286
   * @param columnName - Name of the column
287
   * @returns ColumnBuilder for further configuration
288
   */
289
  bigint(columnName: string): ColumnBuilder;
290

291
  /**
292
   * Big integer column (alias for bigint)
293
   * @param columnName - Name of the column
294
   * @returns ColumnBuilder for further configuration
295
   */
296
  bigInteger(columnName: string): ColumnBuilder;
297

298
  /**
299
   * Floating point number column
300
   * @param columnName - Name of the column
301
   * @param precision - Total number of digits
302
   * @param scale - Number of decimal places
303
   * @returns ColumnBuilder for further configuration
304
   */
305
  float(columnName: string, precision?: number, scale?: number): ColumnBuilder;
306

307
  /**
308
   * Double precision floating point column
309
   * @param columnName - Name of the column
310
   * @param precision - Total number of digits
311
   * @param scale - Number of decimal places
312
   * @returns ColumnBuilder for further configuration
313
   */
314
  double(columnName: string, precision?: number, scale?: number): ColumnBuilder;
315

316
  /**
317
   * Decimal/numeric column with fixed precision
318
   * @param columnName - Name of the column
319
   * @param precision - Total number of digits
320
   * @param scale - Number of decimal places
321
   * @returns ColumnBuilder for further configuration
322
   */
323
  decimal(columnName: string, precision?: number, scale?: number): ColumnBuilder;
324

325
  // String columns
326
  /**
327
   * Variable-length string column
328
   * @param columnName - Name of the column
329
   * @param length - Maximum length (default varies by database)
330
   * @returns ColumnBuilder for further configuration
331
   */
332
  string(columnName: string, length?: number): ColumnBuilder;
333

334
  /**
335
   * Variable-length text column
336
   * @param columnName - Name of the column
337
   * @param textType - Text type ('text', 'mediumtext', 'longtext')
338
   * @returns ColumnBuilder for further configuration
339
   */
340
  text(columnName: string, textType?: string): ColumnBuilder;
341

342
  // Date/time columns
343
  /**
344
   * Boolean column
345
   * @param columnName - Name of the column
346
   * @returns ColumnBuilder for further configuration
347
   */
348
  boolean(columnName: string): ColumnBuilder;
349

350
  /**
351
   * Date column (date only, no time)
352
   * @param columnName - Name of the column
353
   * @returns ColumnBuilder for further configuration
354
   */
355
  date(columnName: string): ColumnBuilder;
356

357
  /**
358
   * DateTime column
359
   * @param columnName - Name of the column
360
   * @param options - DateTime options
361
   * @returns ColumnBuilder for further configuration
362
   */
363
  dateTime(columnName: string, options?: Readonly<{ useTz?: boolean; precision?: number }>): ColumnBuilder;
364

365
  /**
366
   * DateTime column (alias for dateTime)
367
   * @param columnName - Name of the column
368
   * @param options - DateTime options
369
   * @returns ColumnBuilder for further configuration
370
   */
371
  datetime(columnName: string, options?: Readonly<{ useTz?: boolean; precision?: number }>): ColumnBuilder;
372

373
  /**
374
   * Time column (time only, no date)
375
   * @param columnName - Name of the column
376
   * @returns ColumnBuilder for further configuration
377
   */
378
  time(columnName: string): ColumnBuilder;
379

380
  /**
381
   * Timestamp column
382
   * @param columnName - Name of the column
383
   * @param options - Timestamp options
384
   * @returns ColumnBuilder for further configuration
385
   */
386
  timestamp(columnName: string, options?: Readonly<{ useTz?: boolean; precision?: number }>): ColumnBuilder;
387

388
  /**
389
   * Add created_at and updated_at timestamp columns
390
   * @param useTimestamps - Whether to use timestamps (default: true)
391
   * @param defaultToNow - Whether to default to current timestamp (default: false)
392
   * @param useCamelCase - Whether to use camelCase names (createdAt, updatedAt)
393
   * @returns CreateTableBuilder for chaining
394
   */
395
  timestamps(useTimestamps?: boolean, defaultToNow?: boolean, useCamelCase?: boolean): CreateTableBuilder;
396

397
  // Binary and specialized columns
398
  /**
399
   * Binary data column
400
   * @param columnName - Name of the column
401
   * @param length - Maximum length
402
   * @returns ColumnBuilder for further configuration
403
   */
404
  binary(columnName: string, length?: number): ColumnBuilder;
405

406
  /**
407
   * JSON column
408
   * @param columnName - Name of the column
409
   * @returns ColumnBuilder for further configuration
410
   */
411
  json(columnName: string): ColumnBuilder;
412

413
  /**
414
   * JSONB column (PostgreSQL binary JSON)
415
   * @param columnName - Name of the column
416
   * @returns ColumnBuilder for further configuration
417
   */
418
  jsonb(columnName: string): ColumnBuilder;
419

420
  /**
421
   * UUID column
422
   * @param columnName - Name of the column
423
   * @param options - UUID options
424
   * @returns ColumnBuilder for further configuration
425
   */
426
  uuid(columnName: string, options?: Readonly<{ useBinaryUuid?: boolean; primaryKey?: boolean }>): ColumnBuilder;
427

428
  /**
429
   * Enum column with predefined values
430
   * @param columnName - Name of the column
431
   * @param values - Array of allowed values
432
   * @param options - Enum options
433
   * @returns ColumnBuilder for further configuration
434
   */
435
  enum(columnName: string, values: readonly Value[], options?: EnumOptions): ColumnBuilder;
436

437
  /**
438
   * Enum column (alias for enum)
439
   * @param columnName - Name of the column
440
   * @param values - Array of allowed values
441
   * @param options - Enum options
442
   * @returns ColumnBuilder for further configuration
443
   */
444
  enu(columnName: string, values: readonly Value[], options?: EnumOptions): ColumnBuilder;
445

446
  // Geometric columns
447
  /**
448
   * Geometry column for spatial data
449
   * @param columnName - Name of the column
450
   * @returns ColumnBuilder for further configuration
451
   */
452
  geometry(columnName: string): ColumnBuilder;
453

454
  /**
455
   * Geography column for spatial data
456
   * @param columnName - Name of the column
457
   * @returns ColumnBuilder for further configuration
458
   */
459
  geography(columnName: string): ColumnBuilder;
460

461
  /**
462
   * Point column for geometric points
463
   * @param columnName - Name of the column
464
   * @returns ColumnBuilder for further configuration
465
   */
466
  point(columnName: string): ColumnBuilder;
467

468
  /**
469
   * Column with database-specific type
470
   * @param columnName - Name of the column
471
   * @param type - Database-specific type string
472
   * @returns ColumnBuilder for further configuration
473
   */
474
  specificType(columnName: string, type: string): ColumnBuilder;
475
}
476

477
interface AlterTableBuilder extends CreateTableBuilder {
478
  /**
479
   * Drop a column
480
   * @param columnName - Name of the column to drop
481
   * @returns AlterTableBuilder for chaining
482
   */
483
  dropColumn(columnName: string): AlterTableBuilder;
484

485
  /**
486
   * Drop multiple columns
487
   * @param columnNames - Names of columns to drop
488
   * @returns AlterTableBuilder for chaining
489
   */
490
  dropColumns(...columnNames: string[]): AlterTableBuilder;
491

492
  /**
493
   * Rename a column
494
   * @param from - Current column name
495
   * @param to - New column name
496
   * @returns AlterTableBuilder for chaining
497
   */
498
  renameColumn(from: string, to: string): AlterTableBuilder;
499

500
  /**
501
   * Make a column nullable
502
   * @param columnName - Name of the column
503
   * @returns AlterTableBuilder for chaining
504
   */
505
  setNullable(columnName: string): AlterTableBuilder;
506

507
  /**
508
   * Make a column not nullable
509
   * @param columnName - Name of the column
510
   * @returns AlterTableBuilder for chaining
511
   */
512
  dropNullable(columnName: string): AlterTableBuilder;
513
}
514
```
515

516
### Column Builder
517

518
Configure individual column properties, constraints, and relationships.
519

520
```typescript { .api }
521
interface ColumnBuilder {
522
  /**
523
   * Add an index to the column
524
   * @param indexName - Optional name for the index
525
   * @param indexType - Optional index type
526
   * @returns ColumnBuilder for chaining
527
   */
528
  index(indexName?: string, indexType?: string): ColumnBuilder;
529

530
  /**
531
   * Make column a primary key
532
   * @param constraintName - Optional constraint name
533
   * @returns ColumnBuilder for chaining
534
   */
535
  primary(constraintName?: string): ColumnBuilder;
536

537
  /**
538
   * Add unique constraint to column
539
   * @param options - Unique constraint options
540
   * @returns ColumnBuilder for chaining
541
   */
542
  unique(options?: Readonly<{ indexName?: string; storageEngineIndexType?: string; deferrable?: deferrableType }>): ColumnBuilder;
543

544
  /**
545
   * Create foreign key reference
546
   * @param columnName - Referenced column name
547
   * @returns ReferencingColumnBuilder for foreign key configuration
548
   */
549
  references(columnName: string): ReferencingColumnBuilder;
550

551
  /**
552
   * Set default value for column
553
   * @param value - Default value
554
   * @param options - Default value options
555
   * @returns ColumnBuilder for chaining
556
   */
557
  defaultTo(value: Value, options?: DefaultToOptions): ColumnBuilder;
558

559
  /**
560
   * Make numeric column unsigned
561
   * @returns ColumnBuilder for chaining
562
   */
563
  unsigned(): ColumnBuilder;
564

565
  /**
566
   * Make column NOT NULL
567
   * @returns ColumnBuilder for chaining
568
   */
569
  notNullable(): ColumnBuilder;
570

571
  /**
572
   * Make column nullable
573
   * @returns ColumnBuilder for chaining
574
   */
575
  nullable(): ColumnBuilder;
576

577
  /**
578
   * Add comment to column
579
   * @param val - Comment text
580
   * @returns ColumnBuilder for chaining
581
   */
582
  comment(val: string): ColumnBuilder;
583

584
  /**
585
   * Alter column definition
586
   * @param options - Alter options
587
   * @returns ColumnBuilder for chaining
588
   */
589
  alter(options?: ColumnAlterOptions): ColumnBuilder;
590

591
  /**
592
   * Position column after another column (MySQL)
593
   * @param columnName - Column to position after
594
   * @returns ColumnBuilder for chaining
595
   */
596
  after(columnName: string): ColumnBuilder;
597

598
  /**
599
   * Position column first in table (MySQL)
600
   * @returns ColumnBuilder for chaining
601
   */
602
  first(): ColumnBuilder;
603

604
  /**
605
   * Set column collation
606
   * @param collation - Collation name
607
   * @returns ColumnBuilder for chaining
608
   */
609
  collate(collation: string): ColumnBuilder;
610

611
  // Check constraints
612
  /**
613
   * Add check constraint for positive values
614
   * @param constraintName - Optional constraint name
615
   * @returns ColumnBuilder for chaining
616
   */
617
  checkPositive(constraintName?: string): ColumnBuilder;
618

619
  /**
620
   * Add check constraint for negative values
621
   * @param constraintName - Optional constraint name
622
   * @returns ColumnBuilder for chaining
623
   */
624
  checkNegative(constraintName?: string): ColumnBuilder;
625

626
  /**
627
   * Add check constraint for values in list
628
   * @param values - Array of allowed values
629
   * @param constraintName - Optional constraint name
630
   * @returns ColumnBuilder for chaining
631
   */
632
  checkIn(values: readonly Value[], constraintName?: string): ColumnBuilder;
633

634
  /**
635
   * Add check constraint for values not in list
636
   * @param values - Array of disallowed values
637
   * @param constraintName - Optional constraint name
638
   * @returns ColumnBuilder for chaining
639
   */
640
  checkNotIn(values: readonly Value[], constraintName?: string): ColumnBuilder;
641

642
  /**
643
   * Add check constraint for values in range
644
   * @param values - Array with min and max values
645
   * @param constraintName - Optional constraint name
646
   * @returns ColumnBuilder for chaining
647
   */
648
  checkBetween(values: readonly [Value, Value], constraintName?: string): ColumnBuilder;
649

650
  /**
651
   * Add check constraint for string length
652
   * @param operator - Comparison operator
653
   * @param length - Length to compare against
654
   * @param constraintName - Optional constraint name
655
   * @returns ColumnBuilder for chaining
656
   */
657
  checkLength(operator: string, length: number, constraintName?: string): ColumnBuilder;
658

659
  /**
660
   * Add check constraint with regex pattern
661
   * @param regex - Regular expression pattern
662
   * @param constraintName - Optional constraint name
663
   * @returns ColumnBuilder for chaining
664
   */
665
  checkRegex(regex: string, constraintName?: string): ColumnBuilder;
666
}
667

668
interface ReferencingColumnBuilder extends ColumnBuilder {
669
  /**
670
   * Specify the referenced table
671
   * @param tableName - Name of the referenced table
672
   * @returns ColumnBuilder for chaining
673
   */
674
  inTable(tableName: string): ColumnBuilder;
675

676
  /**
677
   * Set foreign key constraint name
678
   * @param constraintName - Name for the foreign key constraint
679
   * @returns ColumnBuilder for chaining
680
   */
681
  withKeyName(constraintName: string): ColumnBuilder;
682

683
  /**
684
   * Set ON DELETE action
685
   * @param command - Action to take (CASCADE, SET NULL, RESTRICT, etc.)
686
   * @returns ColumnBuilder for chaining
687
   */
688
  onDelete(command: string): ColumnBuilder;
689

690
  /**
691
   * Set ON UPDATE action
692
   * @param command - Action to take (CASCADE, SET NULL, RESTRICT, etc.)
693
   * @returns ColumnBuilder for chaining
694
   */
695
  onUpdate(command: string): ColumnBuilder;
696

697
  /**
698
   * Make foreign key constraint deferrable
699
   * @param type - Deferrable type
700
   * @returns ColumnBuilder for chaining
701
   */
702
  deferrable(type: deferrableType): ColumnBuilder;
703
}
704
```
705

706
### Table Constraints
707

708
Add table-level constraints including primary keys, foreign keys, and indexes.
709

710
```typescript { .api }
711
/**
712
 * Add primary key constraint
713
 * @param columnNames - Array of column names for composite primary key
714
 * @param options - Primary key options
715
 * @returns CreateTableBuilder for chaining
716
 */
717
primary(columnNames: readonly string[], options?: Readonly<{ constraintName?: string; deferrable?: deferrableType }>): CreateTableBuilder;
718

719
/**
720
 * Add unique constraint
721
 * @param columnNames - Array of column names
722
 * @param options - Unique constraint options
723
 * @returns CreateTableBuilder for chaining
724
 */
725
unique(columnNames: readonly string[], options?: Readonly<{ indexName?: string; storageEngineIndexType?: string; deferrable?: deferrableType }>): CreateTableBuilder;
726

727
/**
728
 * Add database index
729
 * @param columnNames - Array of column names
730
 * @param indexName - Optional index name
731
 * @param options - Index options
732
 * @returns CreateTableBuilder for chaining
733
 */
734
index(columnNames: readonly (string | Raw)[], indexName?: string, options?: Readonly<{ indexType?: string; storageEngineIndexType?: string; predicate?: QueryBuilder }>): CreateTableBuilder;
735

736
/**
737
 * Add foreign key constraint
738
 * @param columns - Local column names
739
 * @param constraintName - Optional constraint name
740
 * @returns TableBuilder for chaining
741
 */
742
foreign(columns: string | readonly string[], constraintName?: string): ReferencingColumnBuilder;
743

744
/**
745
 * Add check constraint
746
 * @param checkPredicate - Check condition as raw SQL or QueryBuilder
747
 * @param bindings - Parameter bindings for raw SQL
748
 * @param constraintName - Optional constraint name
749
 * @returns CreateTableBuilder for chaining
750
 */
751
check(checkPredicate: string | Raw | QueryBuilder | boolean, bindings?: RawBinding[], constraintName?: string): CreateTableBuilder;
752

753
/**
754
 * Drop primary key constraint
755
 * @param constraintName - Optional constraint name
756
 * @returns AlterTableBuilder for chaining
757
 */
758
dropPrimary(constraintName?: string): AlterTableBuilder;
759

760
/**
761
 * Drop unique constraint
762
 * @param columnNames - Column names or index name
763
 * @param indexName - Index name if first parameter is columns
764
 * @returns AlterTableBuilder for chaining
765
 */
766
dropUnique(columnNames: readonly string[] | string, indexName?: string): AlterTableBuilder;
767

768
/**
769
 * Drop index
770
 * @param columnNames - Column names or index name
771
 * @param indexName - Index name if first parameter is columns
772
 * @returns AlterTableBuilder for chaining
773
 */
774
dropIndex(columnNames: readonly (string | Raw)[] | string, indexName?: string): AlterTableBuilder;
775

776
/**
777
 * Drop foreign key constraint
778
 * @param columnNames - Column names or constraint name
779
 * @param constraintName - Constraint name if first parameter is columns
780
 * @returns AlterTableBuilder for chaining
781
 */
782
dropForeign(columnNames: readonly string[] | string, constraintName?: string): AlterTableBuilder;
783

784
/**
785
 * Drop check constraints
786
 * @param checkConstraintNames - Array of check constraint names to drop
787
 * @returns AlterTableBuilder for chaining
788
 */
789
dropChecks(checkConstraintNames: readonly string[]): AlterTableBuilder;
790

791
/**
792
 * Drop timestamp columns (created_at, updated_at)
793
 * @param useCamelCase - Whether timestamp columns use camelCase names
794
 * @returns AlterTableBuilder for chaining
795
 */
796
dropTimestamps(useCamelCase?: boolean): AlterTableBuilder;
797
```
798

799
## Types
800

801
```typescript { .api }
802
type Value = string | number | boolean | Date | Array<string | number | boolean | Date> | null | undefined;
803
type deferrableType = 'not deferrable' | 'immediate' | 'deferred';
804

805
interface EnumOptions {
806
  useNative?: boolean;
807
  existingType?: boolean;
808
  schemaName?: string;
809
  enumName?: string;
810
}
811

812
interface DefaultToOptions {
813
  constraintName?: string;
814
}
815

816
interface ColumnAlterOptions {
817
  alterNullable?: boolean;
818
  alterType?: boolean;
819
}
820

821
interface TableBuilder extends CreateTableBuilder {
822
  queryContext(context: any): TableBuilder;
823
}
824
```
825

826
**Usage Examples:**
827

828
```javascript
829
const knex = require('knex')({ client: 'postgresql', connection: process.env.DATABASE_URL });
830

831
// Create a comprehensive users table
832
await knex.schema.createTable('users', table => {
833
  table.increments('id').primary();
834
  table.string('email', 255).notNullable().unique();
835
  table.string('username', 50).notNullable().unique();
836
  table.string('password_hash').notNullable();
837
  table.string('first_name', 100);
838
  table.string('last_name', 100);
839
  table.date('birth_date');
840
  table.enum('status', ['active', 'inactive', 'suspended']).defaultTo('active');
841
  table.json('preferences').defaultTo('{}');
842
  table.boolean('email_verified').defaultTo(false);
843
  table.timestamp('email_verified_at').nullable();
844
  table.timestamps(true, true); // created_at, updated_at with defaults
845
  
846
  // Indexes
847
  table.index(['last_name', 'first_name']);
848
  table.index('email_verified');
849
  
850
  // Check constraints
851
  table.check('length(username) >= 3', [], 'username_min_length');
852
  table.check('birth_date IS NULL OR birth_date < CURRENT_DATE', [], 'valid_birth_date');
853
});
854

855
// Create posts table with foreign key
856
await knex.schema.createTable('posts', table => {
857
  table.increments('id');
858
  table.string('title').notNullable();
859
  table.text('content');
860
  table.string('slug').unique();
861
  table.integer('user_id').unsigned().notNullable()
862
    .references('id').inTable('users').onDelete('CASCADE');
863
  table.enum('status', ['draft', 'published', 'archived']).defaultTo('draft');
864
  table.integer('view_count').unsigned().defaultTo(0);
865
  table.timestamp('published_at').nullable();
866
  table.timestamps(true, true);
867
  
868
  table.index(['user_id', 'status']);
869
  table.index('published_at');
870
});
871

872
// Alter table to add new column
873
await knex.schema.alterTable('users', table => {
874
  table.string('phone', 20).nullable().after('email');
875
  table.index('phone');
876
});
877

878
// Create schema and table within it
879
await knex.schema.createSchema('blog');
880
await knex.schema.withSchema('blog').createTable('categories', table => {
881
  table.increments('id');
882
  table.string('name').notNullable().unique();
883
  table.string('slug').notNullable().unique();
884
  table.text('description');
885
  table.integer('parent_id').unsigned().nullable()
886
    .references('id').inTable('blog.categories');
887
  table.timestamps(true, true);
888
});
889

890
// Create materialized view (PostgreSQL)
891
await knex.schema.createMaterializedView('user_stats', view => {
892
  view.as(knex.select('user_id')
893
    .count('* as post_count')
894
    .sum('view_count as total_views')
895
    .from('posts')
896
    .groupBy('user_id'));
897
});
898
```