[php] How to create a foreign key in phpmyadmin

I want to make doctorid a foreign key in my patient table.

So I have all of my tables created - the main problem is that when I go to the table > structure > relation view only the primary key comes up that I can create a foreign key (and it is already the primary key of the certain table that I want to keep - i.e Patient table patient is enabled to be changed but the doctor Id -I have a doctor table also- is not enabled).

I have another table with two composite keys (medicineid and patientid) in relation view it enables me to change both

Do I have to chance the index of doctor ID in patient table to something else? both cannot be primary keys as patient ID is the primary for the patient table - doctor is the foreign.

table

I hope anyone can help

Kind regards

This question is related to php mysql database phpmyadmin

The answer is


You can do it the old fashioned way... with an SQL statement that looks something like this

ALTER TABLE table_name
    ADD CONSTRAINT fk_foreign_key_name
    FOREIGN KEY (foreign_key_name)
    REFERENCES target_table(target_key_name);

This assumes the keys already exist in the relevant table


The key must be indexed to apply foreign key constraint. To do that follow the steps.

  1. Open table structure. (2nd tab)
  2. See the last column action where multiples action options are there. Click on Index, this will make the column indexed.
  3. Open relation view and add foreign key constraint.

You will be able to assign DOCTOR_ID as foreign now.


To be able to create a relation, the table Storage Engine must be InnoDB. You can edit in Operations tab. Storage Engine Configuration

Then, you need to be sure that the id column in your main table has been indexed. It should appear at Index section in Structure tab.

Index list

Finally, you could see the option Relations View in Structure tab. When edditing, you will be able to select the parent column in foreign table to create the relation.

enter image description here

See attachments. I hope this could be useful for anyone.


When you create table than you can give like follows.

CREATE TABLE categories(
cat_id int not null auto_increment primary key,
cat_name varchar(255) not null,
cat_description text
) ENGINE=InnoDB;


CREATE TABLE products(
   prd_id int not null auto_increment primary key,
   prd_name varchar(355) not null,
   prd_price decimal,
   cat_id int not null,
   FOREIGN KEY fk_cat(cat_id)
   REFERENCES categories(cat_id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT
)ENGINE=InnoDB;

and when after the table create like this

   ALTER table_name
    ADD CONSTRAINT constraint_name
    FOREIGN KEY foreign_key_name(columns)
    REFERENCES parent_table(columns)
    ON DELETE action
    ON UPDATE action;

Following on example for it.

CREATE TABLE vendors(
    vdr_id int not null auto_increment primary key,
    vdr_name varchar(255)
)ENGINE=InnoDB;

ALTER TABLE products 
ADD COLUMN vdr_id int not null AFTER cat_id;

To add a foreign key to the products table, you use the following statement:

ALTER TABLE products
ADD FOREIGN KEY fk_vendor(vdr_id)
REFERENCES vendors(vdr_id)
ON DELETE NO ACTION
ON UPDATE CASCADE;

For drop the key

ALTER TABLE table_name 
DROP FOREIGN KEY constraint_name;

Hope this help to learn FOREIGN keys works


A simple SQL example would be like this:

ALTER TABLE `<table_name>` ADD `<column_name>` INT(11) NULL DEFAULT NULL ;

Make sure you use back ticks `` in table name and column name


Questions with php tag:

I am receiving warning in Facebook Application using PHP SDK Pass PDO prepared statement to variables Parse error: syntax error, unexpected [ Preg_match backtrack error Removing "http://" from a string How do I hide the PHP explode delimiter from submitted form results? Problems with installation of Google App Engine SDK for php in OS X Laravel 4 with Sentry 2 add user to a group on Registration php & mysql query not echoing in html with tags? How do I show a message in the foreach loop? Target class controller does not exist - Laravel 8 Message: Trying to access array offset on value of type null Array and string offset access syntax with curly braces is deprecated Visual Studio Code PHP Intelephense Keep Showing Not Necessary Error How to fix "set SameSite cookie to none" warning? The POST method is not supported for this route. Supported methods: GET, HEAD. Laravel Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? dyld: Library not loaded: /usr/local/opt/icu4c/lib/libicui18n.62.dylib error running php after installing node with brew on Mac What does double question mark (??) operator mean in PHP Post request in Laravel - Error - 419 Sorry, your session/ 419 your page has expired PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client php mysqli_connect: authentication method unknown to the client [caching_sha2_password] Converting a POSTMAN request to Curl Composer require runs out of memory. PHP Fatal error: Allowed memory size of 1610612736 bytes exhausted Expected response code 250 but got code "530", with message "530 5.7.1 Authentication required Issue in installing php7.2-mcrypt Xampp localhost/dashboard How can I run specific migration in laravel How to change PHP version used by composer Laravel 5 show ErrorException file_put_contents failed to open stream: No such file or directory Artisan migrate could not find driver phpMyAdmin ERROR: mysqli_real_connect(): (HY000/1045): Access denied for user 'pma'@'localhost' (using password: NO) Ajax LARAVEL 419 POST error Laravel 5.5 ajax call 419 (unknown status) laravel 5.5 The page has expired due to inactivity. Please refresh and try again "The page has expired due to inactivity" - Laravel 5.5 How to increment a letter N times per iteration and store in an array? Can't install laravel installer via composer Only on Firefox "Loading failed for the <script> with source" Is there way to use two PHP versions in XAMPP? How to prevent page from reloading after form submit - JQuery laravel Eloquent ORM delete() method No Application Encryption Key Has Been Specified General error: 1364 Field 'user_id' doesn't have a default value How to logout and redirect to login page using Laravel 5.4? How to uninstall an older PHP version from centOS7 How to Install Font Awesome in Laravel Mix PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers Laravel - htmlspecialchars() expects parameter 1 to be string, object given How to downgrade php from 7.1.1 to 5.6 in xampp 7.1.1?

Questions with mysql tag:

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client php mysqli_connect: authentication method unknown to the client [caching_sha2_password] phpMyAdmin on MySQL 8.0 Authentication plugin 'caching_sha2_password' cannot be loaded Error loading MySQLdb Module 'Did you install mysqlclient or MySQL-python?' select rows in sql with latest date for each ID repeated multiple times How to find MySQL process list and to kill those processes? Access denied; you need (at least one of) the SUPER privilege(s) for this operation Import data.sql MySQL Docker Container PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers Hibernate Error executing DDL via JDBC Statement Your password does not satisfy the current policy requirements MySql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Laravel: PDOException: could not find driver Default password of mysql in ubuntu server 16.04 #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’ Job for mysqld.service failed See "systemctl status mysqld.service" Laravel Migration Error: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by MySQL Error: : 'Access denied for user 'root'@'localhost' Unable to start the mysql server in ubuntu How to turn on/off MySQL strict mode in localhost (xampp)? How to store Emoji Character in MySQL Database ERROR 1698 (28000): Access denied for user 'root'@'localhost' What is the meaning of <> in mysql query? The Response content must be a string or object implementing __toString(), "boolean" given after move to psql Xampp-mysql - "Table doesn't exist in engine" #1932 #1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by MySQL fails on: mysql "ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded" How to insert TIMESTAMP into my MySQL table? How to create a foreign key in phpmyadmin JPA Hibernate Persistence exception [PersistenceUnit: default] Unable to build Hibernate SessionFactory PHP: Inserting Values from the Form into MySQL #1292 - Incorrect date value: '0000-00-00' WooCommerce: Finding the products in database ERROR 1067 (42000): Invalid default value for 'created_at' SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost' SQL query to check if a name begins and ends with a vowel MySQL: When is Flush Privileges in MySQL really needed? Error in MySQL when setting default value for DATE or DATETIME

Questions with database tag:

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str IN vs ANY operator in PostgreSQL How to execute raw queries with Laravel 5.1? How should I tackle --secure-file-priv in MySQL? How to use multiple databases in Laravel Why does Oracle not find oci.dll? How do I create a local database inside of Microsoft SQL Server 2014? How to change the default port of mysql from 3306 to 3360 Laravel 5 error SQLSTATE[HY000] [1045] Access denied for user 'homestead'@'localhost' (using password: YES) How to change Oracle default data pump directory to import dumpfile? Using COALESCE to handle NULL values in PostgreSQL What is Hash and Range Primary Key? How to get item count from DynamoDB? Find duplicate records in MongoDB Django Model() vs Model.objects.create() Mongodb: Failed to connect to 127.0.0.1:27017, reason: errno:10061 How can I rename column in laravel using migration? There is already an object named in the database How to list all databases in the mongo shell? Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user 'root'@'localhost'(using password:YES) Partial Dependency (Databases) Warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given... what I do wrong? ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM: How to extend? Laravel: Error [PDOException]: Could not Find Driver in PostgreSQL Warning: mysqli_connect(): (HY000/1045): Access denied for user 'username'@'localhost' (using password: YES) The backend version is not supported to design database diagrams or tables Difference between partition key, composite key and clustering key in Cassandra? Backup/Restore a dockerized PostgreSQL database Can't connect to MySQL server on '127.0.0.1' (10061) (2003) copy from one database to another using oracle sql developer - connection failed Best way to check for "empty or null value" ORA-12528: TNS Listener: all appropriate instances are blocking new connections. Instance "CLRExtProc", status UNKNOWN getting error HTTP Status 405 - HTTP method GET is not supported by this URL but not used `get` ever? How do I to insert data into an SQL table using C# as well as implement an upload function? Postgres - Transpose Rows to Columns How to update multiple columns in single update statement in DB2 How to increase MySQL connections(max_connections)? What are OLTP and OLAP. What is the difference between them? The ALTER TABLE statement conflicted with the FOREIGN KEY constraint Reset identity seed after deleting records in SQL Server How can I get all sequences in an Oracle database?

Questions with phpmyadmin tag:

phpMyAdmin on MySQL 8.0 phpmyadmin - count(): Parameter must be an array or an object that implements Countable Error loading MySQLdb Module 'Did you install mysqlclient or MySQL-python?' phpMyAdmin ERROR: mysqli_real_connect(): (HY000/1045): Access denied for user 'pma'@'localhost' (using password: NO) phpMyAdmin access denied for user 'root'@'localhost' (using password: NO) mysqli_real_connect(): (HY000/2002): No such file or directory How to create a foreign key in phpmyadmin #1292 - Incorrect date value: '0000-00-00' MySQL error - #1932 - Table 'phpmyadmin.pma user config' doesn't exist in engine phpMyAdmin Error: The mbstring extension is missing. Please check your PHP configuration #1273 - Unknown collation: 'utf8mb4_unicode_ci' cPanel phpmyadmin "Not Found" after install on Apache, Ubuntu How to get phpmyadmin username and password http://localhost/phpMyAdmin/ unable to connect Warning: mysqli_connect(): (HY000/1045): Access denied for user 'username'@'localhost' (using password: YES) phpMyAdmin - config.inc.php configuration? Change MySQL root password in phpMyAdmin phpMyAdmin allow remote users Forbidden :You don't have permission to access /phpmyadmin on this server PHP: maximum execution time when importing .SQL data file XAMPP MySQL password setting (Can not enter in PHPMYADMIN) Delete a database in phpMyAdmin PhpMyAdmin not working on localhost phpmysql error - #1273 - #1273 - Unknown collation: 'utf8mb4_general_ci' New xampp security concept: Access Forbidden Error 403 - Windows 7 - phpMyAdmin phpmyadmin #1045 Cannot log in to the MySQL server. after installing mysql command line client phpmyadmin.pma_table_uiprefs doesn't exist MySQL Daemon Failed to Start - centos 6 How to Import 1GB .sql file to WAMP/phpmyadmin Fix Access denied for user 'root'@'localhost' for phpMyAdmin Forbidden: You don't have permission to access / on this server, WAMP Error MySQL: How to set the Primary Key on phpMyAdmin? This table does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available How to set my phpmyadmin user session to not time out so quickly? MySQL CREATE TABLE IF NOT EXISTS in PHPmyadmin import phpMyAdmin mbstring error #1142 - SELECT command denied to user ''@'localhost' for table 'pma_table_uiprefs' Delete all records in a table of MYSQL in phpMyAdmin How to log in to phpMyAdmin with WAMP, what is the username and password? Remove privileges from MySQL database how to get login option for phpmyadmin in xampp How to Set user name and Password of phpmyadmin "Logging out" of phpMyAdmin? #1045 - Access denied for user 'root'@'localhost' (using password: YES) How to access remote server with local phpMyAdmin client? How to solve time out in phpmyadmin? MySQL: How to reset or change the MySQL root password? How to get back Lost phpMyAdmin Password, XAMPP php/mySQL on XAMPP: password for phpMyAdmin and mysql_connect different? display data from SQL database into php/ html table