Adding a foreign key into an existing table

[JTan]

May I know how should I do that?

The SQL statement should be:

CREATE TABLE USER_SYSTEM_SECURITY (
USERID VARCHAR(32),
HOSTID VARCHAR(32),
HOME_DIRECTORY VARCHAR(32) NOT NULL,
ACCESS_RIGHTS INT(3) NOT NULL,
PASSWORD_EXPIRY_DATE DATE,
ROLES VARCHAR(32) NOT NULL,
PRIMARY KEY (USERID, HOSTID),
FOREIGN KEY (USERID) REFERENCES MEMBER (USERID) ON DELETE CASCADE,
FOREIGN KEY (HOSTID) REFERENCES SYSTEM (HOSTID) ON DELETE CASCADE);


But I forgot to add in the last row during the table creation.

 

[TonyGroves]

How about:
[tt]
ALTER TABLE user_system_security
ADD FOREIGN KEY (hostid) REFERENCES system (hostid) ON DELETE CASCADE;
[/tt]

 

[JTan]

Thanks, TonyGroves!

How abt deletion?

 

[TonyGroves]

You mean dropping a foreign key?

First, you have to get the name of the constraint. Do a SHOW CREATE TABLE for the table, and the name of the key will be shown after the CONSTRAINT term. Then do:[tt]
ALTER TABLE tablename DROP FOREIGN KEY constraintname;[/tt]

If you want to use a particular constraint name, you can specify this when adding the foreign key:[tt]
ALTER TABLE tablename ADD CONSTRAINT constraintname FOREIGN KEY ...[/tt]