Search code examples
mysqlhyperlinkforeign-keysrowcreate-table

Creating a column of row data when building a table mysql

What I am trying to do is in two parts.

Sample table:

CREATE TABLE DEMO(
p_id int AUTO_INCREMENT NOT NULL, 
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
Coworker Data Link????????
PRIMARY KEY(p_id)
);

My goal is to add a fourth column to link data from other users when there is a relationship between users (the relationship would not be generated automatically).

So if Bob Smith works with Joe Doe, the third column (Coworker) would link the first user to the information of the second user.

I want to do something similar across tables. So, the second table would be like so:

CREATE TABLE CONTACT(
a_id int AUTO_INCREMENT NOT NULL,
p_id int NOT NULL, 
Address varchar(100),
City varchar(50),
State varchar(50),
USER DATA LINK????????
PRIMARY KEY(p_id)
);

In the second instance, I want to link the users name information with their contact information within the table.

I know how to use queries to recover this information if they are in separate tables, but I am being asked to create single tables with the links within the table. Unfortunately, my internet searching has only helped me find information about queries.

Solution

  • You can make a self referencing table since MySQL supports foreign key references between one column and another within a table; i.e, by making Coworker referencing the p_id of same DEMO table like below

    CREATE TABLE DEMO(
p_id int AUTO_INCREMENT NOT NULL, 
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
Coworker int, 
PRIMARY KEY(p_id),
foreign key(Coworker) references DEMO(p_id)
);

    Then while fetching the data you can do a self join and get the related data. probably you can do the same for second instance in your posted code sample on related column(s).