So i managed to set up all my Primary keys without AUTO_INCREMENT, i cannot change a primary key, if it is also a foreign key. So i am trying to remove the foreign keys(and then re-add them after). however it doesnt seem to recognise the Foreign key.
mysql> ALTER TABLE Teacher
-> DROP FOREIGN KEY Credentials_ID;
ERROR 1091 (42000): Can't DROP 'credentials_ID'; check that column/key exists
mysql> DESCRIBE TEACHER;
+----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+----------------+
| Teacher_ID | int(11) | NO | PRI | NULL | auto_increment |
| Class_ID | int(11) | NO | MUL | NULL | |
| Credentials_ID | int(11) | NO | MUL | NULL | |
| Teacher_Name | varchar(20) | YES | | NULL | |
+----------------+-------------+------+-----+---------+----------------+
As you can see, the Column/Field Credentials_ID exists in the table.
The commands used to make the table Teacher:
mysql> CREATE TABLE Teacher(
-> Teacher_ID int NOT NULL,
-> Class_ID int NOT NULL,
-> Credentials_ID int NOT NULL,
-> Teacher_Name varchar(20),
-> PRIMARY KEY (Teacher_ID),
-> FOREIGN KEY (Class_ID) REFERENCES Class(Class_ID),
-> FOREIGN KEY (Credentials_ID) REFERENCES Credentials(Credentials_ID)
-> );
The parameter to DROP FOREIGN KEY is the name of the foreign key, not the name of the column(s) in the key. When you created the table you didn't give a name to the FK, so MySQL assigned a name automatically; this default name is derived by concatenating the referencing table, the referenced table, the referenced column names, and _fk. If you want to see the name assigned, use
mysql> SHOW CREATE TABLE Teacher;
The result should include:
CONSTRAINT `Teacher_Credentials_Credentials_ID_fk` FOREIGN KEY (`Credentials_ID`) REFERENCES `Credentials` (`Credentials_ID`)
To remove the FK, you need to give this full lname:
mysql> ALTER TABLE Teacher DROP FOREIGN KEY Teacher_Credentials_Credentials_ID_fk;
When you're creating the table you can assign a shorter name to your keys to avoid these verbose names, e.g.
mysql> CREATE TABLE Teacher(
-> Teacher_ID int NOT NULL,
-> Class_ID int NOT NULL,
-> Credentials_ID int NOT NULL,
-> Teacher_Name varchar(20),
-> PRIMARY KEY (Teacher_ID),
-> FOREIGN KEY class_ID_fk (Class_ID) REFERENCES Class(Class_ID),
-> FOREIGN KEY cred_ID_fk (Credentials_ID) REFERENCES Credentials(Credentials_ID)
-> );
Note, however, that foreign key names have to be unique across the whole database, so you can't have two tables with foreign keys to Credentials(Credentials_ID) and use the same name cred_ID_fk for both of them. This is why the default name includes the table names in them, it ensures that you never get conflicts among these names.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments