Here, the foreign key constraint is named fk_student_city_id. Use a query like this if you want to name a foreign key column as a constraint for an existing table. Solution 6 (existing table, foreign key constraint): Note that the table you’re modifying must exist before this command is executed. Then we have the REFERENCES clause with the name of the referenced table and the name of the primary key column in parentheses. Next, the ADD FOREIGN KEY clause is followed by the name of the column that will be used as the foreign key. The table name (in our example, student) is placed after the ALTER TABLE keyword. Here, the table is altered using an ALTER TABLE clause. It is also possible to add a new foreign key to an existing table. Solution 5 (existing table):ĪDD FOREIGN KEY (city_id) REFERENCES city(id) Then we have the REFERENCES clause followed by the name of the referenced table and column (here: id). Next, we write FOREIGN KEY and add (in parentheses) the name of the column that becomes the foreign key. In our example, we use the name fk_student_city_id, which indicates the relevant table and column. Use names that are easy to read and understand. In this code, we again have the CONSTRAINT clause with the name of this constraint. Here’s another example: Solution 4 (new table): These two foreign key columns refer to two columns in the table score_subject – score_id and subject_id. In this example, the constraint fk_student_score_subject_id is a foreign key consisting of two columns: score_id and subject_id. You can create foreign keys on more than one column, as shown below: Solution 3 (new table):įOREIGN KEY (subject_id, score_id) REFERENCES score_subject(subject_id, score_id) Next comes the REFERENCES clause along with the name of the referred table and column. In this case, after the FOREIGN KEY clause, we designate the foreign key column. Solution 2 (new table):įOREIGN KEY (city_id) REFERENCES city(id)Īnother way to define a foreign key during table creation is to use the FOREIGN KEY REFERENCES clause at the end of the column definitions. Keep in mind that you can create more than one foreign key for a table. We write FOREIGN KEY REFERENCES at the end of the definition of this column and follow it with the referenced table and column: city(id). The column city_id is the foreign key in this table and indicates the value of the ID stored in the column id in the table city. We list the columns’ names and put their respective data types in parentheses. In our example, we create the table student using a CREATE TABLE clause. Follow that with the name of the referenced table and the name of the referenced column in parentheses. To create a new table containing a foreign key column that references another table, use the keyword FOREIGN KEY REFERENCES at the end of the definition of that column. Solution 1 (new table):Ĭity_id INT FOREIGN KEY REFERENCES city(id) We would like to create a table named student that contains a foreign key that refers to the id column in the table city. You want to create a foreign key for a table in a database.
0 kommentar(er)
