Mysql – Syntax error, MySQL alter column

MySQL

I'm trying to add referential integrity to a column. However, I can't know the proper syntax for an alter statement. I've attempted the below code. Can someone tell me what is wrong with this query?

alter table person_address modify column address_addressID int(11) not null,
on update cascade;

Best Answer

Not null has nothing to do with referential integrity. If you want to require an address ID here, that's separate from adding a foreign key link to the Address table.

alter table person_address modify column address_addressID int(11) not null;

That statement means you MUST have a value in this column. That's all it means; nothing more or less.

To link this table to the Address table (assuming that is the table's name), you need this:

ALTER TABLE person_address 
ADD FOREIGN KEY (address_addressID)
REFERENCES address_tbl_name (addressID)
ON UPDATE CASCADE;

I've no idea what the primary key field is named or what that table looks like, so replace address_tbl_name and addressID in line 3 with appropriate field/table name.

Related Question