Define two tables in c# with foreign key

Question

I want to make 2 tables : Teams and Projects I want to make a 1 : n relationship between these two. This is my code: //Creare tabela Projects

    public void CreareTabelaProjects() {
        string query = "CREATE TABLE IF NOT EXISTS Projects" + "(" + "id_project MEDIUMINT PRIMARY KEY AUTO_INCREMENT," + "name VARCHAR(30)," +
            "description VARCHAR(30)," + "FOREIGN KEY (team_id) REFERENCES Teams(team_id)" + ");";
        if (this.OpenConnection() == true) {
            MySqlCommand cmd = new MySqlCommand(query, connection);
            cmd.ExecuteNonQuery();
            this.CloseConnection();
        }
    }

   //Creare tabela Teams

    public void CreareTabelaTeams() {
        string query = "CREATE TABLE IF NOT EXISTS Teams" + "(" + "team_id INT AUTO_INCREMENT PRIMARY KEY," + "name VARCHAR(30)" + ");";
        if (this.OpenConnection() == true)
        {
            MySqlCommand cmd = new MySqlCommand(query, connection);
            cmd.ExecuteNonQuery();
            this.CloseConnection();
        }

    }

When i run this, an error occured saying this : Key column 'team_id' doesn't exist in table. The application creates only the Team table. Any help please? Thanks!

D.S. · Accepted Answer · 2015-05-08 14:41:11Z

1

Create the second table before the first one

answered May 8, 2015 at 14:41

D.S.

2892 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

HRRO Over a year ago

I am creating the Teams first

HRRO Over a year ago

private void UserForm_Load(object sender, EventArgs e) { _dbConnect.CreareTabelaTeams(); _dbConnect.CreareTabelaProjects(); }

D.S. Over a year ago

The table being referenced needs to exist for creating the constraint. Another way would be create tables without constraints and then alter tables to add constraints in a separate query, that will be a bit more maintainable.

HRRO Over a year ago

I thought this is the problem string query = "CREATE TABLE IF NOT EXISTS Projects" + "(" + "project_id MEDIUMINT PRIMARY KEY AUTO_INCREMENT," + "name VARCHAR(30)," + "description VARCHAR(30)," + "FOREIGN KEY (project_id) REFERENCES Teams(team_id)" + ");"; I changed the team_id in Foreign key with Project_id. now it says : error 150. Cannot create Projects table

D.S. Over a year ago

project_id is mediumint and team_id is int ensure both have the same datatype

HRRO · Accepted Answer · 2015-05-08 14:51:14Z

1

This is the right query for Project Table

 string query = "CREATE TABLE IF NOT EXISTS Projects" + "(" + "project_id INT AUTO_INCREMENT PRIMARY KEY," + "team_id INT,"+"name VARCHAR(30)," +
            "description VARCHAR(30)," + "FOREIGN KEY (team_id) REFERENCES Teams(team_id)" + ");";

Thanks a lot anyway

answered May 8, 2015 at 14:51

HRRO

452 silver badges10 bronze badges

2 Comments

Panagiotis Kanavos Over a year ago

What's the difference? BTW this problems shows why you should NOT use string concatenation to create SQL statements. Why don't you create a SQL script file and execute it on the server anyway? Or just read its contents and call execute them as a single command?

Nils O Over a year ago

The difference is that this query actually create the key team_id in the Projects table, while the original tried to define a foreign key on team_id while team_id wasn't defined within Projects

Collectives™ on Stack Overflow

Define two tables in c# with foreign key

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related