How to create foreign key in WordPress database?

2 Views

I’m trying to make 3 database tables in WordPress

1 of them I want to have relations from the other..but I cant find how to make reference keys and stuff in wordpress.

Read More

With this code I create on table.. but how to make references and keys cause 1 table will contain the relation between two tables. 

    global $wpdb;

    $table_name = $wpdb -> prefix . "Person";

    if($wpdb ->get_var("SHOW TABELS LIKE '$table_name'") != $table_name)
    {
        $sql = "CREATE TABLE " . $table_name . " (
        `id` INTEGER(9) NOT NULL AUTO_INCREMENT,
        `titel` varchar(255) NOT NULL,
        `content` text NOT NULL,
         UNIQUE KEY id (id)
         );";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);

        add_option('createTBL_version', '1.0'); 
    }
Post Views: 2

Related posts

Leave a Reply

2 comments

  1. Foreign keys work like this:

    FOREIGN KEY(table_field_name) REFERENCES [table_to_be_referred](referred_field)

    The field must exist in the referring table and must match exactly the definition of the referrer. Usually only primary keys are referred, due to their uniqueness.

    Here’s an example:

    CREATE TABLE A (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(10)
);

CREATE TABLE B (
    idB INT NOT NULL PRIMARY KEY,
    idA INT NOT NULL,
    name VARCHAR(10),
    FOREIGN KEY (idA) REFERENCES A(id)
);

  2. Here is the code which helps you in creating the foreign key
    i had the same problem and it got solved

    also instead of dbDelta($sql);
    you can use $wpdb->query(sql);
    also you have to make sure that the type of the foreign key and the references key have same datatype
    in the below example location_id is of type int(11), so id which is the primary key of TEST2_TABLE should also be of type int(11)

    $sql = "CREATE TABLE ".TEST_TABLE." (
    id INT( 11 ) NOT NULL AUTO_INCREMENT,
    title VARCHAR( 100 ) NOT NULL,
    description TEXT DEFAULT NULL,
    location_id INT( 11 ) NOT NULL,
    PRIMARY KEY  (id),
    FOREIGN KEY  (location_id) REFERENCES ".TEST2_TABLE." (id)
);";