Create Table with multi-column constraint(unique)
in [Help]
|
Prev: Reading the Value or Caption From an OPtion Group Button using Select Case??
Next: Reading the Value or Caption From an OPtion Group Button usingSelect Case??
From: Ron on 1 Aug 2010 12:02 Hi. a couple of questions about creating an access (2007) table with a multi-column unique constraint. I know how do the above using an SQL statement within the access program. i.e. CREATE TABLE testTBL ( myID int NOT NULL myLastName varchar(25) NOT NULL myOtherField1 int myOtherField2 int CONSTRAINT my2FieldsUniqueConstraint UNIQUE (myID, myLastName) ) question 1... using access 2007 and when in table DESIGN VIEW mode... can you create the same type table as above (without using an SQL statement)? question 2... after creating the table above successfully, how can you tell that it has the two field unique constraint on the table. I checked the fields properties info section of the two constrained fields and I don't see anything that would tell me about the Unique two field constraint. Of course if you try and enter the same data in these two fields on two different rows you get an error (as expected) but is there some way of showing the table schema which will tell me this info in case I forgot that the table was originally created with the unique constraint across the two fields? TIA Ron
From: Marshall Barton on 1 Aug 2010 15:34 Ron wrote: >a couple of questions about creating an access (2007) table with a >multi-column unique constraint. >I know how do the above using an SQL statement within the access program. >i.e. >CREATE TABLE testTBL >( >myID int NOT NULL >myLastName varchar(25) NOT NULL >myOtherField1 int >myOtherField2 int >CONSTRAINT my2FieldsUniqueConstraint UNIQUE (myID, myLastName) >) > >question 1... using access 2007 and when in table DESIGN VIEW mode... can >you create the same type table as above (without using an SQL statement)? > >question 2... after creating the table above successfully, how can you tell >that it has the two field unique constraint on the table. I checked the >fields properties info section of the two constrained fields and I don't see >anything that would tell me about the Unique two field constraint. Of >course if you try and enter the same data in these two fields on two >different rows you get an error (as expected) but is there some way of >showing the table schema which will tell me this info in case I forgot that >the table was originally created with the unique constraint across the two >fields? Index information is not a property of a field. An index does have a Fields collection, so you were looking at a 1 to Many type structure the wrong way around. Look at the table's Indexes window to see the indexes you set using the table's design UI. The Indexes window does not display the indexes that were automatically created via enforced referential intrigrity in the Relationships window. Using DAO, you can get to the TableDef object's Indexes property that is the table's collection of indexes (Constraints). Then you can check each index's Fields collection to see which fields and their properties make up the index. (I've never tried it, but I suppose there is a way to do something similar using ADOX.) It is a good idea to keep a routine that does that around so you can check if you have duplicate indexes as you approch the limit on the number of constraints. -- Marsh
From: Phil on 1 Aug 2010 17:06 On 01/08/2010 20:34:39, Marshall Barton wrote: > Ron wrote: >>a couple of questions about creating an access (2007) table with a >>multi-column unique constraint. >>I know how do the above using an SQL statement within the access program. >>i.e. >>CREATE TABLE testTBL >>( >>myID int NOT NULL >>myLastName varchar(25) NOT NULL >>myOtherField1 int >>myOtherField2 int >>CONSTRAINT my2FieldsUniqueConstraint UNIQUE (myID, myLastName) >>) >> >>question 1... using access 2007 and when in table DESIGN VIEW mode... can >>you create the same type table as above (without using an SQL statement)? >> >>question 2... after creating the table above successfully, how can you tell >>that it has the two field unique constraint on the table. I checked the >>fields properties info section of the two constrained fields and I don't see >>anything that would tell me about the Unique two field constraint. Of >>course if you try and enter the same data in these two fields on two >>different rows you get an error (as expected) but is there some way of >>showing the table schema which will tell me this info in case I forgot that >>the table was originally created with the unique constraint across the two >>fields? > > Index information is not a property of a field. An index > does have a Fields collection, so you were looking at a 1 to > Many type structure the wrong way around. Look at the > table's Indexes window to see the indexes you set using the > table's design UI. The Indexes window does not display the > indexes that were automatically created via enforced > referential intrigrity in the Relationships window. > > Using DAO, you can get to the TableDef object's Indexes > property that is the table's collection of indexes > (Constraints). Then you can check each index's Fields > collection to see which fields and their properties make up > the index. (I've never tried it, but I suppose there is a > way to do something similar using ADOX.) It is a good idea > to keep a routine that does that around so you can check if > you have duplicate indexes as you approch the limit on the > number of constraints. > > -- > Marsh > I may be missing the point but you can have an index called say IDName consisting of the 2 fields MyID and MyLastName and make that unique. So you can have 1 Smith 2 Smith but not a secon 1 smith Phil
From: Ron on 1 Aug 2010 17:55 Thanks Marsh... got it, missed that Index dialog box that u pointed out. "Marshall Barton" <marshbarton(a)wowway.com> wrote in message news:n5ib56hvhnoskq9en5s98cqpnt1jbfgev2(a)4ax.com... > Ron wrote: >>a couple of questions about creating an access (2007) table with a >>multi-column unique constraint. >>I know how do the above using an SQL statement within the access program. >>i.e. >>CREATE TABLE testTBL >>( >>myID int NOT NULL >>myLastName varchar(25) NOT NULL >>myOtherField1 int >>myOtherField2 int >>CONSTRAINT my2FieldsUniqueConstraint UNIQUE (myID, myLastName) >>) >> >>question 1... using access 2007 and when in table DESIGN VIEW mode... can >>you create the same type table as above (without using an SQL statement)? >> >>question 2... after creating the table above successfully, how can you >>tell >>that it has the two field unique constraint on the table. I checked the >>fields properties info section of the two constrained fields and I don't >>see >>anything that would tell me about the Unique two field constraint. Of >>course if you try and enter the same data in these two fields on two >>different rows you get an error (as expected) but is there some way of >>showing the table schema which will tell me this info in case I forgot >>that >>the table was originally created with the unique constraint across the two >>fields? > > Index information is not a property of a field. An index > does have a Fields collection, so you were looking at a 1 to > Many type structure the wrong way around. Look at the > table's Indexes window to see the indexes you set using the > table's design UI. The Indexes window does not display the > indexes that were automatically created via enforced > referential intrigrity in the Relationships window. > > Using DAO, you can get to the TableDef object's Indexes > property that is the table's collection of indexes > (Constraints). Then you can check each index's Fields > collection to see which fields and their properties make up > the index. (I've never tried it, but I suppose there is a > way to do something similar using ADOX.) It is a good idea > to keep a routine that does that around so you can check if > you have duplicate indexes as you approch the limit on the > number of constraints. > > -- > Marsh
From: Ron on 1 Aug 2010 17:59
"Phil" <phil(a)stantonfamily.co.uk> wrote in message news:FOKdnc2tL4wgfMjRnZ2dnUVZ8sCdnZ2d(a)brightview.co.uk... > On 01/08/2010 20:34:39, Marshall Barton wrote: >> Ron wrote: >>>a couple of questions about creating an access (2007) table with a >>>multi-column unique constraint. >>>I know how do the above using an SQL statement within the access program. >>>i.e. >>>CREATE TABLE testTBL >>>( >>>myID int NOT NULL >>>myLastName varchar(25) NOT NULL >>>myOtherField1 int >>>myOtherField2 int >>>CONSTRAINT my2FieldsUniqueConstraint UNIQUE (myID, myLastName) >>>) >>> >>>question 1... using access 2007 and when in table DESIGN VIEW mode... can >>>you create the same type table as above (without using an SQL statement)? >>> >>>question 2... after creating the table above successfully, how can you >>>tell >>>that it has the two field unique constraint on the table. I checked the >>>fields properties info section of the two constrained fields and I don't >>>see >>>anything that would tell me about the Unique two field constraint. Of >>>course if you try and enter the same data in these two fields on two >>>different rows you get an error (as expected) but is there some way of >>>showing the table schema which will tell me this info in case I forgot >>>that >>>the table was originally created with the unique constraint across the >>>two >>>fields? >> >>> I may be missing the point but you can have an index called say IDName > consisting of the 2 fields MyID and MyLastName and make that unique. So > you > can have 1 Smith 2 Smith > but not a secon 1 smith > > Phil Thanks Phil for your response... my table works like you say and that is the way it was designed, just wanted to know how to look at the table say 6 months from now and if I forgot that it had that double field unique index I wanted to know a way to RE-DISCOVER that fact from within the Access program... Marsh pointed the way for me.... |