| Groups | Blog | Home |
sql server msde : how to create primary keys on compound fields?
Hi everyone,
I am now getting seriously into VB.Net development as a front end to SQL server. After working about 20 yrs with DBF's and various xBase programs (dBase, FoxPro, Clipper, etc), I am now convinced VB/SQL is the way to go :) I have a few questions: I've successfully imported DBF files into SQL server (MSDE) but I can't seem to create a data adapter (in VB) unless I have a primary key defined. Do tables require primary keys be defined prior to dragging them onto a forms and working with datagrids? How can I create a primary key on a compound field? For a simple key (one field), I know to open the server explorer, go into design mode, then right click the field and set primary key. This works well ... but .... what about a 'details' table where the only unique key is a combination of many fields (might be mixed type too), ex: customer (char) + item (numeric) + color (char). How does one do that? Thanks in advance
Hi,
Thanks for your reply. I never noticed the gold key icon, I was using the right click popup menu :) While it appears to work, when I try to save the changes to the file, I get the same error message... 'PRODUCT' table - Unable to modify table. ADO error: Cannot insert the value NULL into column 'PREF', table 'sterilite.dbo.Tmp_PRODUCT'; column does not allow nulls. INSERT fails. The statement has been terminated. I imported the DBF files via SQL servers' tools. I thought everything was ok as in the server explorer, I see the database with all the tables (foxpro files) inside it. I can also browse the data. I did notice the 'allow nulls' column was checked. I tried unchecking it but I get the same error. The foxpro file originally had a 'pref' C4 (for the 'prefix' code, a character field, width 4) and I don't know what to try next. In DBF syntax, the unique fields are: pref, C4 (prefix of item) item, C4 (item code) color, C2 (color code) cu_code, C4 (customer code) All these fields get translated into 'varchar' type with the appropriate widths and the allow nulls checked. Any ideas? Thanks Richard [quoted text, click to view] MGFoster wrote:
> -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > In the table design high-light each column name you want to make PK and > click the PK icon (a gold key symbol) in the toolbar. You high-light > each column by holding down the control key and clicking the square to > the left of the column name. > > You could also use a DDL statement to create a PK: > > ALTER TABLE table_name ADD CONSTRAINT PK_table_name PRIMARY KEY (col1, > col2, col3) > > The coln are the column names that will comprise the PK.
That must be it! Yes, some values of Pref are null. Strange, in the
DBF format it was a blank character field, but when it was imported in SQL, it became <null> While I might be able to get around this by excluding 'pref' from the key, I'll have that problem for other fields as some items don't have colours and they also appear with <null> in SQL format. They would still be unique as if an item only comes in one colour, the DBF file had a blank. Can I globally change all <null> to " " (blank characters) to fix this. Thanks for figuring out what the problem was! Richard [quoted text, click to view] MGFoster wrote:
> -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > In RDBMS (SQL dbs) each row's Primary Key columns require data, i.e., > they cannot be NULL. The error you're getting means that the PREF > column has NULL values in some, or all, of the imported rows. This > means the columns you've selected for PKs are not a good choice, or you > need to fix the data so there is a value in the PREF column for each > row. > > It would seem that the columns item, color, cu_code, would be good > candidates for PK if they always have values per row, and, the 3 column > values, taken together, represent a unique value in the table. > > Is the PREF column useful as part of a unique identifier (PK)? If so, > then it should have data.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1 In the table design high-light each column name you want to make PK and click the PK icon (a gold key symbol) in the toolbar. You high-light each column by holding down the control key and clicking the square to the left of the column name. You could also use a DDL statement to create a PK: ALTER TABLE table_name ADD CONSTRAINT PK_table_name PRIMARY KEY (col1, col2, col3) The coln are the column names that will comprise the PK. -- MGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQbTkE4echKqOuFEgEQI7uACg3KvX6c2lJy7M9loRVsXQbs732YwAnRKD 6CSubCv+n/TA2EUvULyWke2E =qfj3 -----END PGP SIGNATURE----- [quoted text, click to view] Richard Fagen wrote:
> Hi everyone, > > I am now getting seriously into VB.Net development as a front end to SQL > server. After working about 20 yrs with DBF's and various xBase > programs (dBase, FoxPro, Clipper, etc), I am now convinced VB/SQL is the > way to go :) > > I have a few questions: > > I've successfully imported DBF files into SQL server (MSDE) but I can't > seem to create a data adapter (in VB) unless I have a primary key > defined. Do tables require primary keys be defined prior to dragging > them onto a forms and working with datagrids? > > How can I create a primary key on a compound field? For a simple key > (one field), I know to open the server explorer, go into design mode, > then right click the field and set primary key. This works well ... but > ... what about a 'details' table where the only unique key is a > combination of many fields (might be mixed type too), ex: customer
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1 In RDBMS (SQL dbs) each row's Primary Key columns require data, i.e., they cannot be NULL. The error you're getting means that the PREF column has NULL values in some, or all, of the imported rows. This means the columns you've selected for PKs are not a good choice, or you need to fix the data so there is a value in the PREF column for each row. It would seem that the columns item, color, cu_code, would be good candidates for PK if they always have values per row, and, the 3 column values, taken together, represent a unique value in the table. Is the PREF column useful as part of a unique identifier (PK)? If so, then it should have data. -- MGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQbUGaoechKqOuFEgEQK43gCg4pund4SFw+VpPdFrYlcLoGiwV5wAnjzV aFvBbWaz/Xm1Y1XKxbpzG7wQ =rrhn -----END PGP SIGNATURE----- [quoted text, click to view] Richard Fagen wrote:
> Hi, > > Thanks for your reply. I never noticed the gold key icon, I was using > the right click popup menu :) While it appears to work, when I try to > save the changes to the file, I get the same error message... > > 'PRODUCT' table > - Unable to modify table. > ADO error: Cannot insert the value NULL into column 'PREF', table > 'sterilite.dbo.Tmp_PRODUCT'; column does not allow nulls. INSERT fails. > The statement has been terminated. > > I imported the DBF files via SQL servers' tools. I thought everything > was ok as in the server explorer, I see the database with all the tables > (foxpro files) inside it. I can also browse the data. I did notice the > 'allow nulls' column was checked. I tried unchecking it but I get the > same error. > > The foxpro file originally had a 'pref' C4 (for the 'prefix' code, a > character field, width 4) and I don't know what to try next. > > In DBF syntax, the unique fields are: > pref, C4 (prefix of item) > item, C4 (item code) > color, C2 (color code) > cu_code, C4 (customer code) > > All these fields get translated into 'varchar' type with the appropriate > widths and the allow nulls checked. > > Any ideas? > > Thanks > > Richard > > > > > MGFoster wrote: > >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> In the table design high-light each column name you want to make PK and >> click the PK icon (a gold key symbol) in the toolbar. You high-light >> each column by holding down the control key and clicking the square to >> the left of the column name. >> >> You could also use a DDL statement to create a PK: >> >> ALTER TABLE table_name ADD CONSTRAINT PK_table_name PRIMARY KEY (col1, >> col2, col3) >> >> The coln are the column names that will comprise the PK.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1 You can change all values of one column, but that will not fix your problem. For the PK, what you need to do is pick one, or more, columns whose values uniquely identify the row. Each of these columns MUST have data for every row. There cannot be rows where these columns have equal values. E.g. (the 3 columns that make up the PK): item colour cu_code 1 2 9988 1 3 9988 1 2 9988 <- PK constraint violation 'cuz equals 1st row. It would seem that this table would have a Date column. You could include the date column in the PK & have something like this: item colour cu_code order_date 1 2 9988 20040115 1 3 9988 20040115 1 2 9988 20040120 <- now OK 'cuz order_date makes row different from 1st row. If the columns you pick as the PK have rows w/o data then you will have to do, what is known as, "Data Clean Up." Which should be done by your client since they are the ones who created this mess to begin with. I suggest you get a good book on database design to help you figure out construction of primary keys, foreign keys, indexes, etc. I've recommended _Database Design for Mere Mortals_ by Hernandez. -- MGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQbVhPIechKqOuFEgEQLJGQCgjejMWSmlyiNAELht8FvPb2BluRsAoORW 6ofwJ8Gby1KYNurkiHCb1NM+ =Ode3 -----END PGP SIGNATURE----- [quoted text, click to view] Richard Fagen wrote:
> That must be it! Yes, some values of Pref are null. Strange, in the > DBF format it was a blank character field, but when it was imported in > SQL, it became <null> > > While I might be able to get around this by excluding 'pref' from the > key, I'll have that problem for other fields as some items don't have > colours and they also appear with <null> in SQL format. > > They would still be unique as if an item only comes in one colour, the > DBF file had a blank. Can I globally change all <null> to " " (blank > characters) to fix this. > > Thanks for figuring out what the problem was! > > Richard > > MGFoster wrote: > >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> In RDBMS (SQL dbs) each row's Primary Key columns require data, i.e., >> they cannot be NULL. The error you're getting means that the PREF >> column has NULL values in some, or all, of the imported rows. This >> means the columns you've selected for PKs are not a good choice, or you >> need to fix the data so there is a value in the PREF column for each >> row. >> >> It would seem that the columns item, color, cu_code, would be good >> candidates for PK if they always have values per row, and, the 3 column >> values, taken together, represent a unique value in the table. >> >> Is the PREF column useful as part of a unique identifier (PK)? If so, >> then it should have data.
Hi,
Thanks for your prompt reply. I will definitely check out that book. I have collected quite a library over the years and I'm always reading many tech books at once :) I'm aware of the requirement of a unique value for each record, it is just some records, when imported from DBF (Foxpro/DOS) have null values. In DBF format, spaces were stored. I'm not sure why importing the files into SQL tables cause them to change to <nulls> Maybe I didn't check off some option during the import process? Ex: Item Colour Cu_code ... more fields here 1 1 Walmart 1 2 Walmart 1 3 Walmart 2 <null> Walmart <- problem here, it was blank in the DBF, some items have a single record that is blank, it will still be unique, just one " " value for that cu_code/item combination 3 1 Walmart 3 2 Walmart Your idea to incorporate another field is a great idea. You are right, I will have the same problem elsewhere unless I add an orderdate (or invoice #) to the key. I don't know how to get rid of the <nulls> for the blank DBFs that were imported into SQL/ I was thinking of replacing the blanks in the DBF with a dummy symbol (say *) before importing into SQL format. Then I'd use OSQL to replace the dummy with " ". Is there an easier way? Thanks again. Richard [quoted text, click to view] MGFoster wrote:
> -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > You can change all values of one column, but that will not fix your > problem. For the PK, what you need to do is pick one, or more, columns > whose values uniquely identify the row. Each of these columns MUST have > data for every row. There cannot be rows where these columns have equal > values. E.g. (the 3 columns that make up the PK): > > item colour cu_code > 1 2 9988 > 1 3 9988 > 1 2 9988 <- PK constraint violation 'cuz equals 1st > row. > > It would seem that this table would have a Date column. You could > include the date column in the PK & have something like this: > > item colour cu_code order_date > 1 2 9988 20040115 > 1 3 9988 20040115 > 1 2 9988 20040120 <- now OK 'cuz order_date makes > row different from 1st row. > > If the columns you pick as the PK have rows w/o data then you will have > to do, what is known as, "Data Clean Up." Which should be done by your > client since they are the ones who created this mess to begin with. > > I suggest you get a good book on database design to help you figure out > construction of primary keys, foreign keys, indexes, etc. I've > recommended _Database Design for Mere Mortals_ by Hernandez.
Can't a blank value (" ") be valid if it is part of a unique compound
key? There must be some OSQL statement that I can use. In anycase, is there a simple way to change all the <null> values to blank spaces? I know I can use the server explorer to browse and edit values, but certain tables have 1000's of <null> values in certain columns. Thanks Richard [quoted text, click to view] MGFoster wrote:
> -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > It would seem that replacing the "*" with a space would be redundant, > since you're just inserting a "holding character" until you get valid > data, and that holding character can be an asterisk as easily as a > space.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1 It would seem that replacing the "*" with a space would be redundant, since you're just inserting a "holding character" until you get valid data, and that holding character can be an asterisk as easily as a space. -- MGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQbYol4echKqOuFEgEQIcRACg0oDX4sr3G7MWGLBjVSDoTU2tUjgAn3UM Cg//imB7G3nJKMWBWBZRffvf =Is8Z -----END PGP SIGNATURE----- [quoted text, click to view] Richard Fagen wrote:
> Hi, > > Thanks for your prompt reply. I will definitely check out that book. I > have collected quite a library over the years and I'm always reading > many tech books at once :) > > I'm aware of the requirement of a unique value for each record, it is > just some records, when imported from DBF (Foxpro/DOS) have null values. > In DBF format, spaces were stored. I'm not sure why importing the > files into SQL tables cause them to change to <nulls> Maybe I didn't > check off some option during the import process? > > Ex: > > Item Colour Cu_code ... more fields here > 1 1 Walmart > 1 2 Walmart > 1 3 Walmart > 2 <null> Walmart <- problem here, it was blank in the DBF, > some items have a single record that is blank, > it will still be unique, just one " " value > for that cu_code/item combination > 3 1 Walmart > 3 2 Walmart > > Your idea to incorporate another field is a great idea. You are right, > I will have the same problem elsewhere unless I add an orderdate (or > invoice #) to the key. I don't know how to get rid of the <nulls> for > the blank DBFs that were imported into SQL/ > > I was thinking of replacing the blanks in the DBF with a dummy symbol > (say *) before importing into SQL format. Then I'd use OSQL to replace > the dummy with " ". Is there an easier way? > > Thanks again. > > Richard > > MGFoster wrote: > >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> You can change all values of one column, but that will not fix your >> problem. For the PK, what you need to do is pick one, or more, columns >> whose values uniquely identify the row. Each of these columns MUST have >> data for every row. There cannot be rows where these columns have equal >> values. E.g. (the 3 columns that make up the PK): >> >> item colour cu_code >> 1 2 9988 >> 1 3 9988 >> 1 2 9988 <- PK constraint violation 'cuz equals 1st >> row. >> >> It would seem that this table would have a Date column. You could >> include the date column in the PK & have something like this: >> >> item colour cu_code order_date >> 1 2 9988 20040115 >> 1 3 9988 20040115 >> 1 2 9988 20040120 <- now OK 'cuz order_date makes >> row different from 1st row. >> >> If the columns you pick as the PK have rows w/o data then you will have >> to do, what is known as, "Data Clean Up." Which should be done by your >> client since they are the ones who created this mess to begin with. >> >> I suggest you get a good book on database design to help you figure out >> construction of primary keys, foreign keys, indexes, etc. I've >> recommended _Database Design for Mere Mortals_ by Hernandez.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1 You can change NULLs to another character(s) using an update like this: UPDATE table_name SET column_name = '*' WHERE column_name IS NULL I'd use an actual character, 'cuz some query results thru other applications (MS Access) will seem to be a space (blank), but, in fact, will be a NULL. What I was trying to get at is this may not enable a PK to be established on the columns you want to make a PK, because, putting a character in the column may not make a unique PK. E.g.: BEFORE: item colour cu_code NULL 1 9998 2 NULL 9998 NULL 1 9998 AFTER: item colour cu_code * 1 9998 2 * 9998 * 1 9998 <- violates PK constraint 'cuz equals row 1. -- MGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQbY6KoechKqOuFEgEQKgvACfeROn2fzUzR88HCv9HxPmvYPl3YcAoMM6 lDykLHrwNeYAAUzf6vFHbpmb =r+De -----END PGP SIGNATURE----- [quoted text, click to view] Richard Fagen wrote:
> Can't a blank value (" ") be valid if it is part of a unique compound > key? > > There must be some OSQL statement that I can use. > > In anycase, is there a simple way to change all the <null> values to > blank spaces? I know I can use the server explorer to browse and edit > values, but certain tables have 1000's of <null> values in certain columns. > > Thanks > > Richard > > > MGFoster wrote: > >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> It would seem that replacing the "*" with a space would be redundant, >> since you're just inserting a "holding character" until you get valid >> data, and that holding character can be an asterisk as easily as a >> space.
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |