| Groups | Blog | Home |
sql server (alternate) : Does the absence of PK constraint cause deletion problem ?
[quoted text, click to view]
Anita <anonymous@devdex.com> wrote in message news:<405a4a42$0$199$75868355@news.frii.net>... > Hi All, > > I have just read the book ("Troubleshooting SQL" > by Forrest Houlette) about the importance of > primary key (PK) in a table. Forrest says: > > "Duplicate rows can be fiendish. You can > encounter situations in which the database > will refuse to delete a duplicate because > it can not uniququely identify the row. > Normally, DELETE will delete anything > that matches its criteria, both single > rows and multiple rows" > > Have you ever met the above problem ? > Have you ever experienced other problems caused > by the absence of PK also ? > > Currently, there are about 80% tables with unique > keys and 20% tables contain duplicate keys in > my database. General journal table is one of the > tables that contains duplicate keys. Users may > enter duplicate rows in this table. > I also do not put a PK constraint on each table. > The reason for not putting the constraint : > > 1. Users access database via an application program > only. Database integrity is controlled by this program. > There are no attributes (like foreign key constraints) > that link the tables. All tables are independent. > > 2. Almost all keys are made up of more than one column. > Without PK constraint, efficient indexes can be > created. For example: > > CREATE TABLE Stock_card ( > Year char(4) NOT NULL , > Prefix char(3) NOT NULL , > TransactionNo char(20) NOT NULL , > Date datetime NOT NULL , > Description char(50) NOT NULL , > ProductNo char(15) NOT NULL , > WarehouseNo char(8) NOT NULL , > BatchNo char(15) NOT NULL , > Quantity decimal(15, 2) NOT NULL , > Code char(1) NOT NULL ) > > The unique key of Stock_card table is - > (Year,Prefix,TransactionNo,ProductNo, > WarehouseNo,BatchNo) > If PK constraint is set then a PK_index is > automatically created on 6 columns. This index is too > wide and almost useless. Accessing individual row > of this table is not needed. > > Then I create index A on (Date, Prefix, TransactionNo) > and cluster index B on (ProductNo,WarehouseNo,Date) > All the queries benefit from these indexes. > Column year and BatchNo are not included in these > indexes because they contain many duplicate values. > > My application does delete rows by criteria quite often. > So far, I have no problem. But, after reading Forrest book, I worry. > Will I also find the similar problem in the future ? > > Anyone information and opinion about this post > will be greatly appreciated. > > Thanks in advance > > Anita Hery > > > > > > > > > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it! At least in MSSQL, the database engine will not stop you deleting rows in a table with no primary key. However, a common issue is that certain tools (eg. Enterprise Manager) will not allow deletions or updates on a table without a primary key, because they cannot guarantee that the row you selected in the interface is really the row that will be deleted. In a relational database, all tables should have a primary key. Your first reason above is slightly suspect - can you guarantee that no one will ever modify data except through your application? If you can, then the approach may work, but frequently developers, DBAs etc. make ad hoc changes to data - without referential integrity (and CHECK constraints for domain integrity etc.), you can't ensure that their changes will respect the data model. And then you also have to import/export data, run reports etc. - many tools try to be 'intelligent' based on the foreign key information they can read from the database. You also have the issue that you need to rely on documentation to see how to code in the database, and technical documentation is often not maintained well, but if your constraints and relations are explicit in the database, then developers can immediately see how tables are related, what values are acceptable in a certain column etc. For what it's worth, I currently work in an environment where the data architect decided on a similar approach to you - no foreign keys, no constraints, all columns NULLable etc., and it is extremely difficult to be productive. As for your second reason, multi-column keys are fine, but if you have many of them, it suggests you may not have fully normalized your data model. It's not possible to say for sure, of course, without detailed knowledge of your environment and business. Surrogate (artificial) keys are often used for tables with very wide natural primary keys.
Hi All,
I have just read the book ("Troubleshooting SQL" by Forrest Houlette) about the importance of primary key (PK) in a table. Forrest says: "Duplicate rows can be fiendish. You can encounter situations in which the database will refuse to delete a duplicate because it can not uniququely identify the row. Normally, DELETE will delete anything that matches its criteria, both single rows and multiple rows" Have you ever met the above problem ? Have you ever experienced other problems caused by the absence of PK also ? Currently, there are about 80% tables with unique keys and 20% tables contain duplicate keys in my database. General journal table is one of the tables that contains duplicate keys. Users may enter duplicate rows in this table. I also do not put a PK constraint on each table. The reason for not putting the constraint : 1. Users access database via an application program only. Database integrity is controlled by this program. There are no attributes (like foreign key constraints) that link the tables. All tables are independent. 2. Almost all keys are made up of more than one column. Without PK constraint, efficient indexes can be created. For example: CREATE TABLE Stock_card ( Year char(4) NOT NULL , Prefix char(3) NOT NULL , TransactionNo char(20) NOT NULL , Date datetime NOT NULL , Description char(50) NOT NULL , ProductNo char(15) NOT NULL , WarehouseNo char(8) NOT NULL , BatchNo char(15) NOT NULL , Quantity decimal(15, 2) NOT NULL , Code char(1) NOT NULL ) The unique key of Stock_card table is - (Year,Prefix,TransactionNo,ProductNo, WarehouseNo,BatchNo) If PK constraint is set then a PK_index is automatically created on 6 columns. This index is too wide and almost useless. Accessing individual row of this table is not needed. Then I create index A on (Date, Prefix, TransactionNo) and cluster index B on (ProductNo,WarehouseNo,Date) All the queries benefit from these indexes. Column year and BatchNo are not included in these indexes because they contain many duplicate values. My application does delete rows by criteria quite often. So far, I have no problem. But, after reading Forrest book, I worry. Will I also find the similar problem in the future ? Anyone information and opinion about this post will be greatly appreciated. Thanks in advance Anita Hery *** Sent via Developersdex http://www.developersdex.com ***
[quoted text, click to view]
>> Have you ever met the above problem? Have you ever experienced other problems caused by the absence of PK also? <<I made a six figure income from fixing databases like that, so I don't know if they are a problem to me :) [quoted text, click to view] >> Currently, there are about 80% tables with unique keys and 20% tables contain duplicate keys in my database. <<You really have no idea what you are doing! A "unique key" is redundant and "duplicate key" is an oxymoron. [quoted text, click to view] >> 1. Users access database via an application program only. Database integrity is controlled by this program.Application program by definition cannot enforce Database integrity; only the database can. But let's assume you have worked that magic; nobody has access to Query Analyzer or other tools, ALL your programs were proven to be consistent in the QA audits you conducted on them, etc. Now, by what further magic have you absolutely guaranteed that all future programs are also absolutely correct? [quoted text, click to view] >> There are no attributes (like foreign key constraints) that link the tables. All tables are independent. <<You are using an RDBMS like a 1950's file system. The **whole** RDBMS is the unit of work; magnetic tape files are independent, decks of punch cards are independent. [quoted text, click to view] >> Almost all keys are made up of more than one column. << So what? Is the data model correct? [quoted text, click to view] >> Without PK constraint, efficient indexes can be created. << So what? Is the data model correct? Hey, if I don't have to get the **right** answers, I can write really fast code!! Why were all the columns in your StockCard table of CHAR(n) datatype? No numerics, no temporal data? Why are the names a mix of reserved words (year, date, etc.) or so vague as to be useless (code for what?, year of what?) You've never seen ISO-11179 data element naming rules, have you? [quoted text, click to view] >> The unique key of Stock_card table is (Year, Prefix, TransactionNo, ProductNo, WarehouseNo, BatchNo) <<I would have thought that the transaction number would be unique and that it owuldhav occured on a date; but if this is a valid data model, so what? You have to enforce unique to have a consistent database. If you don't, this nightmare will be useless in about one year. It is probably out of balance now, but nobody knows it yet. You obviously have no background in RDBMS; make your boss send you to school for a year to get the basics, then apprentice under someone for
Hi Simon,
Thank you very much for your good explanation. When you pointed to updates and deletions on table without PK using EM, I soon realized that I had also found the same problem. Using artificial keys on my tables (like Stock_card) is a good idea from you. Anita Hery *** Sent via Developersdex http://www.developersdex.com ***
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
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 |