foreign key
-
i tried to add records to my database table but cant due to foreign key problem.. how do i solve tat? i tried deleting all the relationships and i was able to add successfully but not when i add in the relationships. 2 tables involved: tblcompany - accountnumber (primary key) - companyname - address tblcustomer - customerid (primary key, auto generated) - companyname [foreign key] - username - password - name - contactnumber are these tables linked correctly? can someone help? thanks in advance Laine
-
i tried to add records to my database table but cant due to foreign key problem.. how do i solve tat? i tried deleting all the relationships and i was able to add successfully but not when i add in the relationships. 2 tables involved: tblcompany - accountnumber (primary key) - companyname - address tblcustomer - customerid (primary key, auto generated) - companyname [foreign key] - username - password - name - contactnumber are these tables linked correctly? can someone help? thanks in advance Laine
Using string fields as keys is most of the time A Bad Idea. Its not really clear from your table synopsis which is the "parent" and which is the "child" in your data logic. So i'll ignore what you've done and use a fictional example. tblCompanies ------------ CompanyId (primary key, autonumber) CompanyName CompanyAddress Company PhoneNumber tblEmployees ------------ EmployeeId (primary key, autonumber) CompanyId (foreign key) EmployeeName EmployeeInsideLegMeasurement I hope that example makes it more clear.
#include <beer.h>
-
Using string fields as keys is most of the time A Bad Idea. Its not really clear from your table synopsis which is the "parent" and which is the "child" in your data logic. So i'll ignore what you've done and use a fictional example. tblCompanies ------------ CompanyId (primary key, autonumber) CompanyName CompanyAddress Company PhoneNumber tblEmployees ------------ EmployeeId (primary key, autonumber) CompanyId (foreign key) EmployeeName EmployeeInsideLegMeasurement I hope that example makes it more clear.
#include <beer.h>
thanks for ur reply jon.. the company table is the parent and the customer/employee table is the child. the company data muz exist before the employee can sign up to be a member.. the data field for the companyID is alphanumeric.. and employees have problem remembering their company account number.. that's why i choose that they enter their company name instead.. this is to identify which employee from which company logs on. any idea on how to go about designing a better database than the one i did earlier? any comments, suggestions will be deeply appreciated.. :-D Laine
-
i tried to add records to my database table but cant due to foreign key problem.. how do i solve tat? i tried deleting all the relationships and i was able to add successfully but not when i add in the relationships. 2 tables involved: tblcompany - accountnumber (primary key) - companyname - address tblcustomer - customerid (primary key, auto generated) - companyname [foreign key] - username - password - name - contactnumber are these tables linked correctly? can someone help? thanks in advance Laine
First of all: 1. What DB do you use? Oracle, SQL-Server, Access, DB2? 2. Post your error message (or error code) 3. Post your table-definitons 4. Post the INSERTs you tried. Some things you should check: 1. Most DBs are case sensitive. So 'comp1' != 'Comp1' => master-key won't be found 2. Even if it sounds silly -> Check for typing errors! 3. Be sure to create a company before creating a customer for it. 4. You can only delete a record from tblcompany if no record of tblcustomer is referencing to it. The included example should work (assuming accountnumber, customerid and contactnumber to be NUMBERS, the others VARCHAR)
INSERT INTO tblcompany VALUES(1, 'comp1', 'someaddress'); INSERT INTO tblcompany VALUES(2, 'comp2', 'someaddress'); INSERT INTO tblcustomer (companyname, username, password, name, contactnumber) VALUES ('comp1', 'uname', '***', 'name', 1);-- karl -
First of all: 1. What DB do you use? Oracle, SQL-Server, Access, DB2? 2. Post your error message (or error code) 3. Post your table-definitons 4. Post the INSERTs you tried. Some things you should check: 1. Most DBs are case sensitive. So 'comp1' != 'Comp1' => master-key won't be found 2. Even if it sounds silly -> Check for typing errors! 3. Be sure to create a company before creating a customer for it. 4. You can only delete a record from tblcompany if no record of tblcustomer is referencing to it. The included example should work (assuming accountnumber, customerid and contactnumber to be NUMBERS, the others VARCHAR)
INSERT INTO tblcompany VALUES(1, 'comp1', 'someaddress'); INSERT INTO tblcompany VALUES(2, 'comp2', 'someaddress'); INSERT INTO tblcustomer (companyname, username, password, name, contactnumber) VALUES ('comp1', 'uname', '***', 'name', 1);-- karli am using sql server2000. when i try to add a record to the database, the following error occurred: "INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_tblCustomer_tblCompany'. The conflict occurred in database 'myDatabase', table 'tblCompany', column 'accountnumber'. The statement has been terminated." i am sure that all the spellings are correct and there are no typing mistakes anywhere. the tables involved are: tblcompany - accountnumber (primary key) : varchar - companyname : varchar - address : varchar tblcustomer - customerid (primary key, auto generated) : int - companyname [foreign key] : varchar - username : varchar - password : varchar - name : varchar - contactnumber : int the parent would be tblcompany whereas the customer table as the child. i am using a stored procedure: ALTER Procedure AddCustomer ( @CompanyName varchar(50), @Username varchar(50), @Password varchar(50), @Name varchar(50), @PhoneNo varchar(50) ) AS INSERT INTO tblCustomer (CompanyName, Username, Password, Name, PhoneNo) VALUES (@CompanyName, @Username, @Password, @Name, @PhoneNo) RETURN this is how i call the stored procedure: Public Sub processRegistration(ByVal sender As System.Object, ByVal e As System.EventArgs) Dim CompanyName As String = txtCompany.Text Dim Username As String = txtUsername.Text Dim Password As String = txtPassword.Text Dim Name As String = txtName.Text Dim PhoneNo As String = txtContact.Text Dim ocm As SqlClient.SqlCommand ocm = New SqlClient.SqlCommand("AddCustomer", SqlConnection1) ocm.CommandType = CommandType.StoredProcedure Dim pcompanyname As SqlClient.SqlParameter pcompanyname = New SqlClient.SqlParameter("@CompanyName", SqlDbType.VarChar, 50) pcompanyname.Value = CompanyName ocm.Parameters.Add(pcompanyname) Dim pusername As SqlClient.SqlParameter pusername = New SqlClient.SqlParameter("@Username", SqlDbType.VarChar, 50) pusername.Value = Username ocm.Parameters.Add(pusername) . . SqlConnection1.Open() ocm.ExecuteNonQuery() SqlConnection1.Close() End Sub i have the company data in the tblcompany, but i juz couldnt add a customer to the customer table. can anyone tell me wat is wrong? Laine
-
i am using sql server2000. when i try to add a record to the database, the following error occurred: "INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_tblCustomer_tblCompany'. The conflict occurred in database 'myDatabase', table 'tblCompany', column 'accountnumber'. The statement has been terminated." i am sure that all the spellings are correct and there are no typing mistakes anywhere. the tables involved are: tblcompany - accountnumber (primary key) : varchar - companyname : varchar - address : varchar tblcustomer - customerid (primary key, auto generated) : int - companyname [foreign key] : varchar - username : varchar - password : varchar - name : varchar - contactnumber : int the parent would be tblcompany whereas the customer table as the child. i am using a stored procedure: ALTER Procedure AddCustomer ( @CompanyName varchar(50), @Username varchar(50), @Password varchar(50), @Name varchar(50), @PhoneNo varchar(50) ) AS INSERT INTO tblCustomer (CompanyName, Username, Password, Name, PhoneNo) VALUES (@CompanyName, @Username, @Password, @Name, @PhoneNo) RETURN this is how i call the stored procedure: Public Sub processRegistration(ByVal sender As System.Object, ByVal e As System.EventArgs) Dim CompanyName As String = txtCompany.Text Dim Username As String = txtUsername.Text Dim Password As String = txtPassword.Text Dim Name As String = txtName.Text Dim PhoneNo As String = txtContact.Text Dim ocm As SqlClient.SqlCommand ocm = New SqlClient.SqlCommand("AddCustomer", SqlConnection1) ocm.CommandType = CommandType.StoredProcedure Dim pcompanyname As SqlClient.SqlParameter pcompanyname = New SqlClient.SqlParameter("@CompanyName", SqlDbType.VarChar, 50) pcompanyname.Value = CompanyName ocm.Parameters.Add(pcompanyname) Dim pusername As SqlClient.SqlParameter pusername = New SqlClient.SqlParameter("@Username", SqlDbType.VarChar, 50) pusername.Value = Username ocm.Parameters.Add(pusername) . . SqlConnection1.Open() ocm.ExecuteNonQuery() SqlConnection1.Close() End Sub i have the company data in the tblcompany, but i juz couldnt add a customer to the customer table. can anyone tell me wat is wrong? Laine
ylaine wrote: tblcompany - accountnumber (primary key) : varchar - companyname : varchar - address : varchar tblcustomer - customerid (primary key, auto generated) : int - companyname [foreign key] : varchar - username : varchar - password : varchar - name : varchar - contactnumber : int The foreign key in tblcustomer references to the PRIMARY KEY of tblcompany. So it references to accountnumber and not to companyname. If you want to set a foreign key to companyname you must define it as UNIQUE in tblcompany and change the fk-constraint to point to companyname. -- karl
-
ylaine wrote: tblcompany - accountnumber (primary key) : varchar - companyname : varchar - address : varchar tblcustomer - customerid (primary key, auto generated) : int - companyname [foreign key] : varchar - username : varchar - password : varchar - name : varchar - contactnumber : int The foreign key in tblcustomer references to the PRIMARY KEY of tblcompany. So it references to accountnumber and not to companyname. If you want to set a foreign key to companyname you must define it as UNIQUE in tblcompany and change the fk-constraint to point to companyname. -- karl
-
thanks for ur reply jon.. the company table is the parent and the customer/employee table is the child. the company data muz exist before the employee can sign up to be a member.. the data field for the companyID is alphanumeric.. and employees have problem remembering their company account number.. that's why i choose that they enter their company name instead.. this is to identify which employee from which company logs on. any idea on how to go about designing a better database than the one i did earlier? any comments, suggestions will be deeply appreciated.. :-D Laine