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 |
|
John Norris
Starting Member
3 Posts |
 Posted - 2011-06-12 : 07:32:23
|
| Hi allI have a table A and a table BTable A posts a foreign key into BHow can I limit the number of rows in table B that references a single row in Table AWhat I’m after is sort of not “one to many” – but “one to a few”I’ve looked at constraints but it does not seem possible. Can this be done with a trigger?I am trying to limit the total number of drawings that can be attached to an electrical project in an old sql 2000 database. Many thanks for your time |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-06-12 : 07:40:29
|
Is it possible to handle that in the front end instead of bending the database?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
John Norris
Starting Member
3 Posts |
Posted - 2011-06-12 : 07:54:21
|
| Alas No – we have no access to the source code – which is why I was wondering about the Db (which we do)Best RegardsJohn |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-06-12 : 10:34:59
|
| People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it. Let's make this more real An apartment can have no more than four renters. CREATE TABLE Apartments(apartment_nbr INTEGER NOT NULL PRIMARY KEY, .);CREATE TABLE Renters(apartment_nbr INTEGER NOT NULL REFERENCES Apartments (apartment_nbr), renter_nbr INTEGER NOT NULL CHECK(renter_nbr BETWEEN 1 to 4),UNIQUE (apartment_nbr, renter_nbr), ..);--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
John Norris
Starting Member
3 Posts |
Posted - 2011-06-12 : 11:17:23
|
| OK - jcelko, I'll take you comments on board, and thank for the exampleJohn |
|
|
|
|
|
|
|
|
Triggers - limit one to many (to be one to few)