If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
allowing only 3 records to be related to another table
Hi again Jamie,
I've been checking out the links below, and trying to add a constraint as described (Access 2003, so Jet 4.0), with no success... I execute this successfully: CREATE TABLE Table1 (Field1 DOUBLE); Then I keep getting a "Syntax error in CONSTRAINT clause" error message, when I try to execute this: ALTER TABLE Table1 ADD CONSTRAINT (Field1 = 0); Am I doing something wrong? Have you actually succeeded in doing this on a native Jet table? Thanks, Nikos Jamie Collins wrote: On Jun 6, 2:47 pm, Nikos Yannacopoulos wrote: Suggestion 2 is simply impossible in Access, as no such thing as table-level check exists in Jet. Nikos, Haven't we been here before, my friend g? It usually goes like this: I post a few links to articles about CHECK constraints being introduced into Jet, I draw your attention that this event occurred three versions of Access and at least SEVEN YEARS ago, then you post, "%$£!, your right!" in reply ;-) ACC2000: How to Create a Jet CHECK Constraint http://support.microsoft.com/kb/201888/EN-US/ Description of the new features that are included in Microsoft Jet 4.0 http://support.microsoft.com/default...b;en-us;275561 "One new feature added to the Jet CREATE TABLE syntax is Check Constraints. This new SQL grammar allows the user to specify business rules that can span more than one table..." Jamie. -- |
#12
|
|||
|
|||
allowing only 3 records to be related to another table
On Jun 7, 1:13 pm, Nikos Yannacopoulos
wrote: trying to add a constraint as described (Access 2003, so Jet 4.0), with no success... I execute this successfully: CREATE TABLE Table1 (Field1 DOUBLE); Then I keep getting a "Syntax error in CONSTRAINT clause" error message, when I try to execute this: ALTER TABLE Table1 ADD CONSTRAINT (Field1 = 0); Am I doing something wrong? Have you actually succeeded in doing this on a native Jet table? To execute your SQL DDL from the SQL View of a Query object, you must first put the database into 'ANSI-92 Query Mode'. See: About ANSI SQL query mode (MDB) http://office.microsoft.com/en-gb/ac...704831033.aspx (I recommend you click 'Show All'.) Alternatively, use an OLE DB connection, which natively uses ANSI-92 Query Mode e.g. in VBA: CurrentProject.Connection.Execute "ALTER TABLE Table1 ADD CONSTRAINT (Field1 = 0);" Jamie. -- |
#13
|
|||
|
|||
allowing only 3 records to be related to another table
Jamie,
Thanks again, but still no luck... neither method worked, I still get the same error message. Regards, Nikos Jamie Collins wrote: On Jun 7, 1:13 pm, Nikos Yannacopoulos wrote: To execute your SQL DDL from the SQL View of a Query object, you must first put the database into 'ANSI-92 Query Mode'. See: About ANSI SQL query mode (MDB) http://office.microsoft.com/en-gb/ac...704831033.aspx (I recommend you click 'Show All'.) Alternatively, use an OLE DB connection, which natively uses ANSI-92 Query Mode e.g. in VBA: CurrentProject.Connection.Execute "ALTER TABLE Table1 ADD CONSTRAINT (Field1 = 0);" Jamie. -- |
#14
|
|||
|
|||
allowing only 3 records to be related to another table
On Jun 7, 2:12 pm, Nikos Yannacopoulos
wrote: Thanks again, but still no luck... neither method worked, I still get the same error message. Sorry, I didn't check you syntax. It should be --either-- ALTER TABLE Table1 ADD CONSTRAINT constraint_name_here CHECK (Field1 = 0); --or-- ALTER TABLE Table1 ADD CHECK (Field1 = 0); HTH, Jamie. -- |
#15
|
|||
|
|||
allowing only 3 records to be related to another table
Hi Jamie,
Thanks again, this did it (both ways)! A couple of points on this process, for anyone following this thread: The advantage of creating constraints programatically, is the ability to extend those over several tables. On the flip side, there are some disadvantages vs. using validation rules on fields in table design, when there is no need to involve any other table: 1) Constraints are not as obvious to see; one has to query system table MSysObjects, filtering on Type = 9; 2) Constraints can only be changed / deleted programatically; 3) Validation rules added to an already populated field will perform an integrity check and raise a red flag in case of violations; creation of a constraint will not, it will simply ignore violations in existing data. Therefore, I'll stick with validation rules for single-table criteria, and consider constraints for multi-table ones only. Regards, Nikos PS. The example in MS KB article 275561 is wrong! quote ALTER TABLE TableName2 ADD CONSTRAINT (FieldName4 = (SELECT SUM (FieldName) FROM TableName1)); quote No name for the constraint in the expression. Jamie Collins wrote: On Jun 7, 2:12 pm, Nikos Yannacopoulos wrote: Thanks again, but still no luck... neither method worked, I still get the same error message. Sorry, I didn't check you syntax. It should be --either-- ALTER TABLE Table1 ADD CONSTRAINT constraint_name_here CHECK (Field1 = 0); --or-- ALTER TABLE Table1 ADD CHECK (Field1 = 0); HTH, Jamie. -- |
#16
|
|||
|
|||
allowing only 3 records to be related to another table
On Jun 11, 10:58 am, Nikos Yannacopoulos
wrote: The advantage of creating constraints programatically, is the ability to extend those over several tables. On the flip side, there are some disadvantages vs. using validation rules on fields in table design, when there is no need to involve any other table: 1) Constraints are not as obvious to see; one has to query system table MSysObjects, filtering on Type = 9; 2) Constraints can only be changed / deleted programatically; 3) Validation rules added to an already populated field will perform an integrity check and raise a red flag in case of violations; creation of a constraint will not, it will simply ignore violations in existing data. Therefore, I'll stick with validation rules for single-table criteria, and consider constraints for multi-table ones only. Here are some more for 'the flip side': CHECK constraint usage: http://groups.google.com/group/micro...271b75c7d9fe31 CHECK constraints tested on left-to-right table basis rather than at end of SQL statement http://groups.google.com/group/micro...c3f233ba3336cc It's not that CHECK constraints offer an 'advantage' as such; rather, they are *required* to maintain data integrity in some designs, even some simple and oft encountered ones. The example I usually cite is a history table (called a valid-time state table in the SQL literature), say a 'Payroll' table consisting of columns employee_ID, salary_amount, start_date, end_date. The candidate keys include: (employee_ID, start_date) (employee_ID, end_date) (employee_ID, start_date, end_date) Pick one for PRIMARY KEY (SQL keyword in uppercase) and use UNIQUE for the others. However, none of the above prevent duplicate data e.g. they do not prevent this: (1, 12000, #2001-01-01 00:00:00#, #2001-12-31 23:59:59#) (1, 14000, #2002-01-01 00:00:00#, #2002-12-31 23:59:59#) (1, 15000, #2002-12-01 00:00:00#, #2003-12-31 23:59:59#) The above shows two salary values for the employee as at #2002-12-15 00:00:00#. Clearly, a constraint is required to prevent overlapping periods. If you believe that every table should have a primary key (lowercase), being something that prevents duplicates, they you need CHECK constraints because this cannot be achieved with PRIMARY KEY alone. PS. The example in MS KB article 275561 is wrong! quote ALTER TABLE TableName2 ADD CONSTRAINT (FieldName4 = (SELECT SUM (FieldName) FROM TableName1)); quote No name for the constraint in the expression Good spot! My favourite is this: http://office.microsoft.com/en-gb/ac...314411033.aspx CREATE [TEMPORARY] TABLE? Named multiple-field NOT NULL constraint?? Jamie. -- |
#17
|
|||
|
|||
allowing only 3 records to be related to another table
Wow.
and without me being here at all, other than to start the posts. i kinda figured that there wasnt an easy way to do it. i have yet to try to implement any of the ideas here. will let you all know what happens. thanks! "DawnTreader" wrote: hello i have a very specific need to have only 3 records in my "block serial" table to be related to one record in my "product serial" table. the company i work for makes a product where the compressor products that we manufacture have upto 3 blocks in them. there is information that we relate through the blocks to the compressor, but each block has individual information. i want to limit how many blocks can be given the same product ID number. tblProduct ProductID - Primary Key - Autonumber field ProductSerial - Number given to compressor - Manual text field tblBlockList ProductID - Foreign Key - Number field BlockID - Primary Key - Autonumber field BlockSerial - Number given to block - Manual text field. tblProduct.ProductID 1 -- Many tblBlockList.ProductID thats the structure of the relationship, now how do you tell it to only allow for 3 blocks on the many side? |
|
Thread Tools | |
Display Modes | |
|
|