AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of perdoname_
perdoname_
 asked on

foreign key

Hello Experts,

I need your assistance how can i create a new attribute which serves as a foreign key in order to associate each employee in the employees table with one of the rows in the job_roles  table.

Thanks in advance for any help ! 

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
 
public class createTable 
{
	public static void main(String[] args) 
	{
		DB db = new DB();
		Connection conn=db.dbConnect(
				"jdbc:qed:mynewdb://localhost:1925", "p");
		db.createTables(conn);
	}
}//createTable
 
class DB
{
	public DB() {}
 
	public Connection dbConnect(String db_connect_string,
			String db_userid)
	{
		try
		{
			Class.forName("com.quadcap.jdbc.JdbcDriver");
			java.util.Properties p = new java.util.Properties();
			p.setProperty("create", "true");
			java.sql.Connection conn =
				java.sql.DriverManager.getConnection("jdbc:qed:mynewdb", p);
			return conn;
 
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return null;
		}
	} //Connection dbConnect
 
 
	//Employee table
	public void createTables(Connection conn)
	{
 
		String query;
		Statement stmt;
 
		try
		{
			query="create table cust_profile " +
			"(name varchar(32), " +
			"id varchar(50), " +
			"telephoneNo varchar(50), " +
			"email varchar(50))";
			stmt = conn.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	} //createTables
 
 
 
	public void createJobRoles(Connection conn)
	{
 
		String query;
		Statement stmt;
 
		try
		{
			query="create table Job_roles " +
			"(JOB_ID INTEGER NOT NULL, " +
			"job_title varchar(32), " +
			"salary_class varchar(50), " +
			"job_description varchar(50), " +;
			"primary key(JOB_ID))";
			stmt = conn.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
 
	public void EmployeeNameSearch(Object args) {
 
		List names =  Arrays.asList(args);
 
		Collections.sort(names); //sort ArrayList required for binarySearch
 
		BufferedReader stdin = new BufferedReader(new InputStreamReader(System.in));
		while (true) {
			String option = stdin.readLine();
 
			if (cust_profile.binarySearch(names.toArray(),option.toLowerCase())== -1)
			{	
				System.out.println("Not found in database: ");
				System.out.println("Name: " + option );
 
			}
 
		}
 
 
	}; //namesSearch
 
 
};

Open in new window

JavaProgramming Languages-OtherMySQL Server
Avatar of undefined
Last Comment
JesterToo
8/22/2022 - Mon
JesterToo
Replace your definition of "cust_profile" with this one.  Also, your question stated the Employees table... you may need to change the table name in the code I supplied.  I assumed you're using INNODB for the engine.  You probably should add that last line to the Job_roles table as well (just in case you didn't set it as the default when you installed MySQL..  Don't forget to populate this new column when you insert/update the table.  If you have existing rows in this table you will need to make the new column "nullable"... you may want to do that anyway if there are cust_profile (aka Employees) rows for which the Job_Id is unknown.

Hopefully, I didn't leave any syntax errors in the code... it is untested.

Regards,
Lynn
query="create table cust_profile " +
"(name       varchar(32), " +
"id          varchar(50), " +
"telephoneNo varchar(50), " +
"email       varchar(50), " +
"JOB_ID      integer,     " +
"foreign key (JOB_ID) references Job_roles (JOB_ID) " +
") engine = innodb" ;

Open in new window

JesterToo
Ignore my comment and code regarding "engine = innodb"...that's a MySQL thing and I see you are using QED.

Lynn
perdoname_
ASKER
In the case of removing ""engine = innodb" cant be used in QED ?
query="create table cust_profile " +
"(name       varchar(32), " +
"id          varchar(50), " +
"telephoneNo varchar(50), " +
"email       varchar(50), " +
"JOB_ID      integer,     " +
"foreign key (JOB_ID) references Job_roles (JOB_ID);

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SOLUTION
CEHJ
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
JesterToo
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
perdoname_
ASKER
Okay thats the code with the foreign key
Each employee is going to have just one job_role
But I still cant understand what shall i do as the John Doe, for example, will have the role of Garden worker  
Shall i add an extra field in the current stmt.executeUpdate() or is there any sql statement which can do that job ??
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
 
public class createTable 
{
	public static void main(String[] args) 
	{
		DB db = new DB();
		Connection conn=db.dbConnect(
				"jdbc:qed:mynewdb://localhost:1925", "p");
		db.createTables(conn);
	}
}
 
class DB
{
	public DB() {}
 
	public Connection dbConnect(String db_connect_string,
			String db_userid)
	{
		try
		{
			Class.forName("com.quadcap.jdbc.JdbcDriver");
			java.util.Properties p = new java.util.Properties();
			p.setProperty("create", "true");
			java.sql.Connection conn =
				java.sql.DriverManager.getConnection("jdbc:qed:mynewdb", p);
			return conn;
 
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return null;
		}
	} 
 
 
	//Employee table
	public void createTables(Connection conn)
	{
 
		String query;
		Statement stmt = null;
 
		try
		{
			query="create table employees " +
			"(name       varchar(32), " +
			"id          varchar(50), " +
			"telephoneNo varchar(50), " +
			"email       varchar(50), " +
			"JOB_ID      integer,     " +
			"foreign key (JOB_ID) references Job_roles (JOB_ID))";
 
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('John Doe', '1', '5555999944', 'Something@mail.com')");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Neil Mayhem', '2', '555588888', 'sth@mail.com')");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Alice Lovelace', '3', '6666999944', 'alice@mail.com')");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Jay Something', '4', '666669999', 'mail@mail.com')");
 
 
			stmt = conn.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	} //createTables
 
 
 
	public void createJobRoles(Connection conn)
	{
 
		String query;
		Statement stmt = null;
 
		try
		{
			query="create table Job_roles " +
			"(JOB_ID INTEGER NOT NULL, " +
			"job_title varchar(32), " +
			"salary_class varchar(50), " +
			"job_description varchar(50), " +
			"primary key(JOB_ID))";
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (1, 'Garden Worker', '4', 'Cultivates and cares for plants')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (2, 'Security Guard', '4', 'Takes care of security')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (3, 'Stage Hypnotist', '2', 'Hypnotizes people on stage')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (4, 'Amusement Park Guide', '3', 'Guides people through an amusement park')");
 
 
 
			stmt = conn.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
 
	public void EmployeeNameSearch(Object args) {
 
		List names =  Arrays.asList(args);
		
		List not = new ArrayList();
		
		//Collections.sort(names); 
 
		PreparedStatement ps = null;
 
		BufferedReader stdin = new BufferedReader(new InputStreamReader(System.in));
		while (true) {
			String option = stdin.readLine();
 
			for (String name : names) {
				ps.setString(1, name);
				ResultSet rs = ps.executeQuery();
				if (rs.next()) {
					// name in db
				}
				else
				{
					System.out.println(name + "not in db!");
					not.add(name);
				
				}
			}
		}
	}
 
 
 
 
};

Open in new window

CEHJ
You need to insert into *both* tables. Bear in mind that 'garden worker' must exist before John Doe can get put in
perdoname_
ASKER
So what do you mean is something like that ?

or shall it be references                   "foreign key (JOB_ID) references employees (JOB_ID))";
	query="create table Job_roles " +
			"(JOB_ID INTEGER NOT NULL, " +
			"job_title varchar(32), " +
			"salary_class varchar(50), " +
			"job_description varchar(50), " +
			"primary key(JOB_ID), "  +
			"foreign key (JOB_ID) references Job_roles (JOB_ID))";

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
CEHJ
You should really be creating these tables with a sql script. You need to get rid of that foreign key - at the moment it has a foreign key to itself
perdoname_
ASKER
So what do you mean is the following thing or not ? ?? 
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
 
public class createTable 
{
	public static void main(String[] args) 
	{
		DB db = new DB();
		Connection conn=db.dbConnect(
				"jdbc:qed:mynewdb://localhost:1925", "p");
		db.createTables(conn);
	}
}
 
class DB
{
	public DB() {}
 
	public Connection dbConnect(String db_connect_string,
			String db_userid)
	{
		try
		{
			Class.forName("com.quadcap.jdbc.JdbcDriver");
			java.util.Properties p = new java.util.Properties();
			p.setProperty("create", "true");
			java.sql.Connection conn =
				java.sql.DriverManager.getConnection("jdbc:qed:mynewdb", p);
			return conn;
 
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return null;
		}
	} 
 
 
	//Employee table
	public void createTables(Connection conn)
	{
 
		String query;
		Statement stmt = null;
 
		try
		{
			/*
			query="create table employees " +
			"(name       varchar(32), " +
			"id          varchar(50), " +
			"telephoneNo varchar(50), " +
			"email       varchar(50), " +
			"JOB_ID      integer,     " +
			"foreign key (JOB_ID) references Job_roles (JOB_ID))";
			 */
			
			CREATE TABLE employees (
					name               		   TEXT NOT NULL,
					id	    	               TEXT NOT NULL,
					telephone_no			   TEXT NOT NULL,
					email        	           TEXT NOT NULL
			);
			
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('John Doe', '1', '5555999944', 'Something@mail.com')");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Neil Mayhem', '2', '555588888', 'sth@mail.com')");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Alice Lovelace', '3', '6666999944', 'alice@mail.com')");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Jay Something', '4', '666669999', 'mail@mail.com')");
 
 
			stmt = conn.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	} //createTables
 
 
 
	public void createJobRoles(Connection conn)
	{
 
		String query;
		Statement stmt = null;
 
		try
		{
 
			/*
			query="create table Job_roles " +
			"(JOB_ID INTEGER NOT NULL, " +
			"job_title varchar(32), " +
			"salary_class varchar(50), " +
			"job_description varchar(50), " +
			"primary key(JOB_ID), "  +
			"foreign key (JOB_ID) references Job_roles (JOB_ID))";
			 */
 
			CREATE TABLE Job_roles (
					EMPLOYEE_ID                BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
					JOB_TITLE	               TEXT NOT NULL,
					SALARY CLASS			   BIGINT NOT NULL,
					JOB_DESCRIPTION	           TEXT NOT NULL
			);
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (1, 'Garden Worker', '4', 'Cultivates and cares for plants')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (2, 'Security Guard', '4', 'Takes care of security')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (3, 'Stage Hypnotist', '2', 'Hypnotizes people on stage')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (4, 'Amusement Park Guide', '3', 'Guides people through an amusement park')");
 
 
 
			stmt = conn.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
 
	public void EmployeeNameSearch(Object args) {
 
		List names =  Arrays.asList(args);
 
		List not = new ArrayList();
 
		//Collections.sort(names); 
 
		PreparedStatement ps = null;
 
		BufferedReader stdin = new BufferedReader(new InputStreamReader(System.in));
		while (true) {
			String option = stdin.readLine();
 
			for (String name : names) {
				ps.setString(1, name);
				ResultSet rs = ps.executeQuery();
				if (rs.next()) {
					// name in db
				}
				else
				{
					System.out.println(name + "not in db!");
					not.add(name);
 
				}
			}
		}
	}
 
 
 
 
};

Open in new window

CEHJ
:-)
Your help has saved me hundreds of hours of internet surfing.
fblack61
JesterToo
In my last comment I stated  <<<...you effectively create a many-to-one relationship...>>>

That was a typo... my fingers don't always type what my brain tells it to!.  It should have said:
...you effective creae a many-to-many relationship...

This clarification is for the benefit of any readers who view this question/solution.

Regards,
Lynn
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent