Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
egil
Starting Member
16 Posts |
 Posted - 2009-01-31 : 09:11:45
|
HelloSorry for the none descriptive subject, SQLTeam needs to allow longer subjects :)Consider this example schema:Customer ( int CustomerId pk, .... )Employee ( int EmployeeId pk, int CustomerId references Customer.CustomerId, .... )WorkItem ( int WorkItemId pk, int CustomerId references Customer.CustomerId, null int EmployeeId references Employee.EmployeeId, .... ) Basically, three tables: * A customer table with a primary key and some additional columns * A employee table with a primary key, a foreign key constraint reference to the customer tables primary key, representing an employee of the customer. * A work item table, which stores work done for the customer, and also info about the specific employee who the work was performed for. The WorkItem.EmployeeId foreign key can be null.My question is. How do I, on a database level, test if an employee is actually associated with a customer, when adding new work items.If for example Scott (employee) works at Microsoft (customer), and Jeff (employee) works at StackOverflow (customer), how do I prevent somebody from adding a work item into the database, with customer = Microsoft, and employee = Jeff, which do not make sense?Can I do it with check constraints or foreign keys or do I need a trigger to test for it manually?Should mention that I use SQL Server 2008.Regards, Egil. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-31 : 09:20:47
|
| for that you need to have a valid mapping data (Scott->Microsoft,Jeff->StackOverflow,...) stored in some table in your db. so that you can check the association before insertion to check if its valid. |
 |
|
|
egil
Starting Member
16 Posts |
Posted - 2009-01-31 : 09:42:49
|
| Those mappings are stored in the employee table, as far as I can see.Regards, Egil. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-31 : 10:15:50
|
| then use a trigger for checking this. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-02-01 : 05:39:36
|
| Maybe use a check constraint to allow only a customer or employee (either nullable but not both). Where there is just a customer then you know that right away. When there is an employee then you need to infer the customer from the employee via a join. Wrap the table with the join & select customer logic in a view then perform all the queries that need to know against that view. |
|
|
|
|
|
|
|
|
Insert into table, checking fk on other tables?