How to alter table add column with named default constraint and named foreign key constraint?

I have two existing tables (TableA and TableB) and I need to add a new column to TableA that has a foreign key to TableB and has a default value that is not null... and both these constraints need to be named. How can I do this?

73445 次浏览
小开
最佳答案

Adding both constraints in one statement wasn't as easy as I thought it would be and there didn't seem to be many examples out there (at least I wasn't able to find any very easily), so I thought I'd share how I did it here and maybe someone can suggest a better way?

ALTER TABLE [table name] ADD
[New Column Name] [Column Type]
CONSTRAINT [constraint name] DEFAULT ([default value]) NOT NULL,
CONSTRAINT [constraint name] FOREIGN KEY ([New Column Name])
REFERENCES [Other Table] ([Foreign ID])

Example:

ALTER TABLE tableA ADD
myNewColumn BIGINT
CONSTRAINT myNamedConstraint_df default (1) NOT NULL,
CONSTRAINT myNamedConstraint_fk FOREIGN KEY (myNewColumn)
REFERENCES tableB (tableBPrimaryKeyID)
小开

Since it's also common to land here from the question how to add a column with a constraint in MySQL, I'll add an answer for that here too:

CREATE TABLE tableA (id BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id));
CREATE TABLE tableB (id BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id));
ALTER TABLE tableA ADD
myNewColumn BIGINT DEFAULT 42 NOT NULL,
ADD CONSTRAINT myNamedConstraint_fk FOREIGN KEY (myNewColumn) REFERENCES tableB (id);

I think there is no way to also name the DEFAULT constraint in MySQL as it's done for sql-server in the other answer, correct me if I'm wrong.


提交答案