...
To add foreign key constraints, within the 01-createCPSTables.yaml file create a new change-set and increment the change-set id as done above.
In the following example, I have created a second test table, which will contain a foreign key shared with the primary key of the first test_table.
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
- changeSet:
id: 1-39
author: cps
changes:
- createTable:
columns:
- column:
autoIncrement: true
constraints:
nullable: false
primaryKey: true
primaryKeyName: test_table__with_fk_pkey
name: test_table_with_fk_id
type: INTEGER
- column:
constraints:
nullable: false
name: column_1
type: TEXT
- column:
constraints:
nullable: false
name: test_table_pk
type: INTEGER
tableName: test_table_with_fk |
From the table above we have created a column of type integer called test_table_pk. This is a reference to the primary key within the first test table created above, which will be our foreign key in this table.
Below is the syntax within YAML for creating a foreign key relation between both tables, the base table and column being the table we are creating the constraint on, and the referenced table and column being the table which contains the PK column we are creating our FK relationship with.
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
- changeSet:
id: 1-40
author: cps
changes:
- addForeignKeyConstraint:
baseColumnNames: test_table_pk
baseTableName: test_table_with_fk
constraintName: test_table_with_fk_fkey
deferrable: false
initiallyDeferred: false
onDelete: NO ACTION
onUpdate: NO ACTION
referencedColumnNames: id
referencedTableName: test_table
validate: true |
Insert Data
Adding New Yang Resource
...