Re: Introduce integerity in the database - Database Discussion Boards

Well this would be interesting if I can do what you mention. I can use it for other purposes and will be helpful to me.

But in my situation, I can not make a combination of columns unique. Things are like

1. Customer are renewed so the data can be very similar to each other.
2. Some honest mistakes can occur where the same data is added twice. The user can figure it out but I want to stop it from the db end.

I am thinking I should change an existing field unique or add a new field which would be unique.

sharp_k wrote:
But in my situation, I can not make a combination of columns unique.

The table represents a "customer". There are going to be columns in there, not all 80, which defines what a unique "customer" is.

You CANNOT procede until you determine which columns make it unique.

If there a few columns then you can add a uniqueness constraint.

But lets say you have a 'lot' of columns, like 50 columns, then you are probably out of luck for easy solutions because it is unlikely that you can add a uniqueness constraint for that many columns.

In that case you would need to wrap ALL access to table in a proc. The proc would verify, via a query, that no other record existed with those 50 columns before the insert. Views can often help with this.

You also need to consider exactly how those records get added. Because now the system is going to start producing errors where it didn't produce errors before.

Additionally if you have a 'lot' of columns which make it unique then for something called a "customer" I would think that there is a design problem.

Like others have said, you need to normalize your database.

When a customer is renewed you don't get a new customer and should therefore not get a new entry in the customer table, but rather a new entry in the subscription table or customer history table or whatever table makes most sense to your system.

As we don't know much about you database we can only give you generalized advice. Mine would be to read this[^] article.
It describes in an easy to understand way how to, and why, you normalize your database.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

One way to stop duplicate records being inserted is to check and see if a record for a customer already exists before inserting the record.

For example, something along the lines of:

if (not exists(select top 1 1 from Customers where Field1 = @Param1 and Field2 = @Param2 ... and so on))
begin
    insert into Customer (...)
    values (...)
end

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

..should be wrapped in a transaction to prevent race-conditions, wouldn't it? Bastard Programmer from Hell if you can't read my code, try converting it here[^]
Sign In·View Thread·Permalink

Indeed it could. "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
Sign In·View Thread·Permalink

sharp_k wrote: it happens duplicate records are inserted How do you determine that? To go along with what the others said, you can query the table for similar entries and present them to the user, e.g.: You entered "John Smith at 123 Main St", is this the same customer as "John Smith at 123 North Main Street"?
Sign In·View Thread·Permalink

Learn to normalize. You want one artificial key for quick access, that's the identity. Normalization gets you the combination of fields that make the record unique, which depends on the data being described. Bastard Programmer from Hell if you can't read my code, try converting it here[^]
Sign In·View Thread·Permalink

I am getting the above error when I upload my website to host but it's working perfectly on my local PC although both are connected to the same database!

here is the error followed by the code:

Object reference not set to an instance of an object
 
[NullReferenceException: Object reference not set to an instance of an object.]
   rmconline.salary_slip.btnGenerate_Click(Object sender, EventArgs e) in C:\Users\Jassim\documents\visual studio 2010\Projects\rmconline\rmconline\salary_slip.ascx.cs:70
   DevExpress.Web.ASPxEditors.ASPxButton.OnClick(EventArgs e) +96
   DevExpress.Web.ASPxEditors.ASPxButton.RaisePostBackEvent(String eventArgument) +540
   DevExpress.Web.ASPxClasses.ASPxWebControl.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

and here is my code:

protected void btnGenerate_Click(object sender, EventArgs e)
{
    sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
    sql_connection.Open();
    sql_command = new MySqlCommand("sp_get_salary_slip", sql_connection);
    sql_command.CommandType = CommandType.StoredProcedure;
    sql_command.Parameters.Add("param_employee_number", MySqlDbType.Int32).Value = Convert.ToInt32(Session["employee_number"]);
    sql_command.Parameters.Add("param_salary_slip_month", MySqlDbType.Int32).Value = Convert.ToInt32(cboMonth.Value);
    sql_command.Parameters.Add("param_salary_slip_year", MySqlDbType.Int32).Value = Convert.ToInt32(txtYear.Value);
    sql_command.Parameters.AddWithValue("param_is_exist", MySqlDbType.Bit).Direction = ParameterDirection.Output;
    sql_reader = sql_command.ExecuteReader();
    sql_reader.Read();
 
    if (sql_reader.HasRows)
    {
        lblMonth.Text = String.Format("{0} {1:0000}", cboMonth.Text, txtYear.Value);
        lblSalary.Text = "BD " + Convert.ToDouble(sql_reader["salary_slip_salary"]).ToString("#,###,##0.000");
 
        panelNoSalarySlip.Visible = false;
        panelSalarySlip.Visible = true;
 
        sql_connection.Close();
 
        populate_salary_slip_details();
    }
    else
    {
        panelSalarySlip.Visible = false;
        panelNoSalarySlip.Visible = true;
    }
}
 
private void populate_salary_slip_details()
{
    data_table = new DataTable();
 
    sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
    sql_connection.Open();
    sql_command = new MySqlCommand("sp_populate_salary_slip_details", sql_connection);
    sql_command.CommandType = CommandType.StoredProcedure;
    sql_command.Parameters.Add("param_employee_number", MySqlDbType.Int32).Value = Convert.ToInt32(Session["employee_number"]);
    sql_command.Parameters.Add("param_salary_slip_month", MySqlDbType.Int32).Value = Convert.ToInt32(cboMonth.Value);
    sql_command.Parameters.Add("param_salary_slip_year", MySqlDbType.Int32).Value = Convert.ToInt32(txtYear.Value);
    sql_adapter = new MySqlDataAdapter(sql_command);
    sql_adapter.Fill(data_table);
    dataSalarySlip.DataSource = data_table;
    dataSalarySlip.DataBind();
}

jrahma wrote:
sql_adapter = new MySqlDataAdapter(sql_command);

the above you should change to include you connection object i.e.

sql_adapter = new MySqlDataAdapter(sql_command, sql_connection);

		9,892,043 members (35,948 online) Visit CodeProject.TV Discuss CodeProject.TV Sign in Email Password Forgot your password? Sign in using