Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql: Support ALTER TABLE ADD COLUMN colname REFERENCES tablename(colname); #32917

Closed
BramGruneir opened this issue Dec 6, 2018 · 16 comments · Fixed by #47082
Closed

sql: Support ALTER TABLE ADD COLUMN colname REFERENCES tablename(colname); #32917

BramGruneir opened this issue Dec 6, 2018 · 16 comments · Fixed by #47082
Assignees
@rohany
Labels
A-schema-changes A-sql-pgcompat Semantic compatibility with PostgreSQL C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)

Comments

@BramGruneir
Copy link
Member

This is currently unimplemented (and returns an error) but I don't think it would be hard to add. There's no worry about violating the constraint since the column is new.

It also points to #8855, but that's not the correct issue. So I'll send a PR to update the issue number to this one.

Assigning @vivekmenezes for triage.
@BramGruneir BramGruneir added this to the 2.2 milestone Dec 6, 2018
@vivekmenezes vivekmenezes removed their assignment Dec 6, 2018
@vivekmenezes vivekmenezes added A-schema-changes C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) labels Dec 6, 2018
@vivekmenezes vivekmenezes removed this from the 2.2 milestone Dec 6, 2018
@awoods187
Copy link
Contributor

This is needed for #5582 right?

@vivekmenezes
Copy link
Contributor

@awoods187 this looks like a small effort.

@BramGruneir BramGruneir mentioned this issue Dec 12, 2018
Closed
BramGruneir added a commit to BramGruneir/cockroach that referenced this issue Dec 13, 2018
@BramGruneir
sql: Uopdate unimplemented reference
645ba1a 
And also clean up the wording to be more explicit.

The error for not being able to add a reference via alter was pointing to the
incorrect github ID cockroachdb#8855. It's been updated to point to cockroachdb#32917 instead.

Release note: None
@BramGruneir BramGruneir mentioned this issue Dec 13, 2018
Merged
craig bot pushed a commit that referenced this issue Dec 13, 2018
@BramGruneir
Merge #33144
ca5d0cd 
33144: sql: Uopdate unimplemented reference r=BramGruneir a=BramGruneir

And also clean up the wording to be more explicit.

The error for not being able to add a reference via alter was pointing to the
incorrect github ID #8855. It's been updated to point to #32917 instead.

Release note: None

Co-authored-by: Bram Gruneir <bram@cockroachlabs.com>
@solongordon
Copy link
Contributor

For what it's worth, I just hit this while writing a migration for the managed service console. Would be nice to support.

@BramGruneir BramGruneir mentioned this issue Jan 2, 2019
Open
18 tasks
vivekmenezes added a commit to vivekmenezes/cockroach that referenced this issue Mar 13, 2019
@vivekmenezes
sql: improve error message while adding column with REFERENCE
02e061b 
This will eventually be fixed through cockroachdb#32917

Also added a unittest to check that this fails specifically for
a column needing a backfill.

Release note: None
@vivekmenezes vivekmenezes mentioned this issue Mar 13, 2019
Merged
vivekmenezes added a commit to vivekmenezes/cockroach that referenced this issue Mar 13, 2019
@vivekmenezes
sql: improve error message while adding column with REFERENCE
639e713 
This will eventually be fixed through cockroachdb#32917

Also added a unittest to check that this fails specifically for
a column needing a backfill.

Release note: None
craig bot pushed a commit that referenced this issue Mar 13, 2019
@vivekmenezes
Merge #35697
ca68ad0 
35697: sql: improve error message while adding column with REFERENCE r=vivekmenezes a=vivekmenezes

This will eventually be fixed through #32917

Also added a unittest to check that this fails specifically for
a column needing a backfill.

Release note: None

Co-authored-by: Vivek Menezes <vivek@cockroachlabs.com>
@rolandcrosby
Copy link

@awoods187 can you help me understand the relative urgency of this? It sounds like it would help us add foreign keys transactionally but I'm not sure how much that buys us for ORM compat.

@awoods187
Copy link
Contributor

I'm not sure--it was needed for compat but a lot of work has changed here with FKs and ORMs. @jordanlewis do you know?

@rafiss rafiss added the A-sql-pgcompat Semantic compatibility with PostgreSQL label Feb 8, 2020
@jordanlewis
Copy link
Member

I'd like to tackle this soon. cc @rohany

@rohany rohany self-assigned this Mar 31, 2020
@rohany
Copy link
Contributor

rohany commented Mar 31, 2020

Just to clarify this is just adding a foreign key constraint on the column right

@rohany rohany mentioned this issue Apr 6, 2020
Merged
rohany added a commit to rohany/cockroach that referenced this issue Apr 6, 2020
@rohany
sql: support the ADD COLUMN ... REFERENCES syntax
3c89166 
Fixes cockroachdb#32917.

This PR adds support for the add column references
statement by allowing the foreign key building code
to use columns and indexes added in the current txn.
The schema changer already understands how to add
the combination of the three in the same transaction.

Release note (sql change): This PR adds support for the
`ALTER TABLE ... ADD COLUMN ... REFERENCES ...` syntax.
rohany added a commit to rohany/cockroach that referenced this issue Apr 27, 2020
@rohany
sql: support the ADD COLUMN ... REFERENCES syntax
6ccf57e 
Fixes cockroachdb#32917.

This PR adds support for the add column references
statement by allowing the foreign key building code
to use columns and indexes added in the current txn.
The schema changer already understands how to add
the combination of the three in the same transaction.

Release note (sql change): This PR adds support for the
`ALTER TABLE ... ADD COLUMN ... REFERENCES ...` syntax
for tables that are empty.
rohany added a commit to rohany/cockroach that referenced this issue Apr 27, 2020
@rohany
sql: support the ADD COLUMN ... REFERENCES syntax
626dc47 
Fixes cockroachdb#32917.

This PR adds support for the add column references
statement by allowing the foreign key building code
to use columns and indexes added in the current txn.
The schema changer already understands how to add
the combination of the three in the same transaction.

Release note (sql change): This PR adds support for the
`ALTER TABLE ... ADD COLUMN ... REFERENCES ...` syntax
for tables that are empty.
rohany added a commit to rohany/cockroach that referenced this issue Apr 30, 2020
@rohany
sql: support the ADD COLUMN ... REFERENCES syntax
a779997 
Fixes cockroachdb#32917.

This PR adds support for the add column references
statement by allowing the foreign key building code
to use columns and indexes added in the current txn.
The schema changer already understands how to add
the combination of the three in the same transaction.

Release note (sql change): This PR adds support for the
`ALTER TABLE ... ADD COLUMN ... REFERENCES ...` syntax
for tables that are empty.
rohany added a commit to rohany/cockroach that referenced this issue Apr 30, 2020
@rohany
sql: support the ADD COLUMN ... REFERENCES syntax
fa3cfff 
Fixes cockroachdb#32917.

This PR adds support for the add column references
statement by allowing the foreign key building code
to use columns and indexes added in the current txn.
The schema changer already understands how to add
the combination of the three in the same transaction.

Release note (sql change): This PR adds support for the
`ALTER TABLE ... ADD COLUMN ... REFERENCES ...` syntax
for tables that are empty.
craig bot pushed a commit that referenced this issue May 4, 2020
@rohany
Merge #47082
f6d95c4 
47082: sql: support the ADD COLUMN ... REFERENCES syntax r=lucy-zhang a=rohany

Fixes #32917.

This PR adds support for the add column references
statement by allowing the foreign key building code
to use columns and indexes added in the current txn.
The schema changer already understands how to add
the combination of the three in the same transaction.

Release note (sql change): This PR adds support for the
`ALTER TABLE ... ADD COLUMN ... REFERENCES ...` syntax.

Co-authored-by: Rohan Yadav <rohany@alumni.cmu.edu>
@craig craig bot closed this as completed in 83caa70 May 4, 2020
@jordanlewis
Copy link
Member

Nice!

@proffalken
Copy link

\o/

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@rohany rohany

Labels
A-schema-changes A-sql-pgcompat Semantic compatibility with PostgreSQL C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

sql: support the ADD COLUMN ... REFERENCES syntax rohany/cockroach