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 |
|
hlrsr
Starting Member
2 Posts |
 Posted - 2007-11-28 : 02:36:13
|
| Hello all, here is my problem.I have an Account table as well as a Bank table. The bank table has a total assets field. There is a foreign key in Account referencing Bank.What I need is a check constraint that verifies that the total sum of the account balances for a particular bank is less than that banks total assets.I've been thinking about this one for a while but it's just confusing me. How do I create the expression in the constraint for this? Currently I am using SQL Server Management Studio Express so I'm creating this constraint with the gui.Any help is appreciated. Thanks! |
|
|
georgev
Posting Yak Master
122 Posts |
Posted - 2007-11-28 : 03:40:02
|
CHECK Constraints- A column can have any number of CHECK constraints, and the condition can include multiple logical expressions combined with AND and OR. Multiple CHECK constraints for a column are validated in the order they are created.
- The search condition must evaluate to a Boolean expression and cannot reference another table.
- A column-level CHECK constraint can reference only the constrained column, and a table-level CHECK constraint can reference only columns in the same table.
- CHECK CONSTRAINTS and rules serve the same function of validating the data during INSERT and DELETE statements.
- When a rule and one or more CHECK constraints exist for a column or columns, all restrictions are evaluated.
Source: http://doc.ddart.net/mssql/sql70/create_7.htm
George<3Engaged! |
 |
|
|
hlrsr
Starting Member
2 Posts |
Posted - 2007-11-28 : 03:57:20
|
| well now that's strange, my lecture notes don't seem to agree with that :)SQL has no problem with me referencing another table, it complains about me using an aggregate.Am I forced to use an assertion or trigger in this case and not a constraint? |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2007-11-28 : 15:20:48
|
| Use books online and look at DML Triggers they are able to look at more that one table, unlike a check constraint that can only look at the table that it is linked to |
|
|
|
georgev
Posting Yak Master
122 Posts |
Posted - 2007-11-28 : 16:37:26
|
I was thinking about this earlier, and I don't think the stuff I posted above is strictly true either - I'm fairly sure I've referenced other tables in my check constraints...So yeah, ignore that post ;)
George<3Engaged! |
|
|
|
|
|
|
check constraints