Menu
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?
562
ALTER TABLE table_name MODIFY COLUMN column_name datatype; The basic syntax of an ALTER TABLE command to add a NOT NULL constraint to a column in a table is as follows. ALTER TABLE table_name MODIFY column_name datatype NOT NULL; The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows.
You cannot alter a column to specify a default value if one of the following conditions exists: The table is referenced by a view.
ALTER TABLE students ADD FOREIGN KEY (student_id) REFERENCES points(id); To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax: ALTER TABLE students ADD CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES points(id);
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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With