If we set a constraint in DB, do we still have to write the code for the same action?

masterlayouts · May 9th, 2012, 12:10 AM

I would like to ask what may sound like a silly question. We have a database well designed that prevents, for example, duplicate entries. If we set the FK to "No Action" it will prevent duplicate entries without throwing any error. If we set it to "Restrict" will throw an error. In the first case we do not need to write any kind of code in addition to what we have. In the second example we may write the code to catch this error. The third way, probably the proper way, it will be to write the actual code. But this is a costly action, we will have to query every time to check if the record is found or not in the database before actually insert it. I guess we may go away with any of those, but what constitutes the best practice? Thank you.

Rod Stephens · May 9th, 2012, 09:47 AM

I don't think I understand. Do you mean you want it to behave the way you describe: disallow duplicates if the field is No Action and throw an error if it is Restrict?

Why don't you just throw the error instead of writing code to set the field to Restrict? I think I don't understand.

A primary key or uniqueness constraint would prevent duplicates.

You might have to write a field-level check constraint (or trigger if the database doesn't support check constraints ;-) to watch for the value restrict.

Let me know if I'm misunderstanding.

masterlayouts · May 9th, 2012, 12:40 PM

I am sorry I wrote everything in a hurry and I've not been clear enough. I have 4 tables:

Code:

Jobs
----
JobID
EmployerID

Employers
---------
EmployerID

Employees
----------
EmployeeID

JobEmployees
-------------
JobID
EmployeeID

Now let's say one employer is logged in, its UserID will be stored in the session (like $_SESSION['uid']).
Now if the employer want to see the employees that applied to a job he/she posted, will be able to see this by changing the jobID that will be passed as parameter.

If the employer change the parameter the select query that take the data from JobsEmployees will run with undesirable results, as we do not check the EmployerID that posted the job (Jobs.EmployerID). As result any Employer logged in will be able to see all jobs applicants for a job (even if the job posted do not belong to them) by changing the parameter in the URL (jobID).

I was thinking that I may prevent this in several ways and not sure what it will be the best practice:
To make a join between the JobsEmployees and Jobs and check the EmployerID against the session ID.
To add another field to the JobsEmployees table to record the EmployerID so the select query will account the EmployerID.
Or to make a new query that will check the EmployerID against the session ID, than moving to the query that select data from JobsEmployees.

Rod Stephens · May 10th, 2012, 04:06 PM

Ah. I get it.

I think most sites would make the employer log in and then they would validate the employer's credentials to make sure they're not doing this. I'm not sure what the best way to do that. This is more a web site security issue than a database design issue.

But I think looking up the session ID would probably work.