|
|
Hi,
I have a Location table. I am wondering what should be its primary key.
LocationTbl
ID identity (1,1) not null
LocationCode not null
LocationName null
LocationDescrption null
Thanks
|
|
|
|
|
Is this a trick question? Obviously the PK is the identity column. I know the language. I've read a book. - _Madmatt
|
|
|
|
|
ID seems like an obvious choice but it won't have any meaningful info to end users. If LocationCode is unique and is meaningful to users, I would choose it as the primary key.
|
|
|
|
|
I think you're missing the point with the primary key. It should never be visible to the end users! "When did ignorance become a point of view" - Dilbert
|
|
|
|
|
I do not agree that the primary key should never be visible to the end user but it is not a must have.
|
|
|
|
|
One of the key requirements for a primary key is that it must not change over time, and one of the best ways to achieve this is to use a surrogate key. Preferably an integer as most databases are tuned for queries on integers.
As it's only a part of the plumbing, the surrogate key has no need to ever be visible outside the DB. In particular, it should never be revealed to the user. This allows the DB administrator to change the representation of the keys at will if necessary.
I can think of very few circumstances when there's a sense in making a key visible to the end user. For example when it's used as a parameter in webpage request."When did ignorance become a point of view" - Dilbert
|
|
|
|
|
I agree.
|
|
|
|
|
Well said. I know the language. I've read a book. - _Madmatt
|
|
|
|
|
As stated the ID should be your primary key with indexing on the LocationCode and possibly LocationName depending on criteria used to query the data.
|
|
|
|
|
Thanks fellows for replying to my post. I had no intenions to trick you. It just sometimes little things create a confusion. I think, I am going to keep LocationCode unique.
So, would you say i shouldn't keep ID (an identity column) in my Location table at all?
|
|
|
|
|
The ID column is a good idea. I would suggest you keep it.
|
|
|
|
|
If locationCode gets its values assigned by someone external to the system, then it should NOT be used as the PK of your table. Primary Keys must be yours, and yours alone. So nobody can entice you to suddenly change their values.
|
|
|
|
|
Hi all,
I'm running a .NET CF 2 application on WinCE, accessing a SQL Compact 3.5 database, and calling SqlCeDataAdapter.Fill() raises an error:
ExceptionCode: 0xC0000005
ExceptionAddress: 0x01a7438C
Reading 0x00000000
It seems to be caused by the underlying native DLLs but I cannot trap the error using C# exceptions. Any suggestions as to the likely cause/possible solution please?
TIA!
|
|
|
|
|
0xC0000005 means Access Violation while reading address 0x00000000 (null).
Take a look at your code and try to find out if you set some null parameter
or something else.Greetings
Covean
|
|
|
|
|
I'm calling it from C# using allocated variables, and the database is being opened without error. Accessing it using totally different code also raises the same error.
|
|
|
|
|
Without some code it will be hard to say where the error lies.
Especially access violations are not that rare and can have a wide range of causes.
It also can be a bug in the underlaying system (what I don't believe), but in most cases the programmer causes this problem.Greetings
Covean
|
|
|
|
|
Thanks for the reply, this is the code - it crashes on the last line. It also crashes when filling a DataTable .
SqlCeConnection conn = new SqlCeConnection( connectionString );
conn.Open();
SqlCeCommand selectCmd = conn.CreateCommand();
selectCmd.CommandText = @"Select Value From MyTable where ColumnOne=101";
SqlCeDataAdapter adp = new SqlCeDataAdapter( selectCmd );
System.Data.DataSet ds = new System.Data.DataSet();
adp.Fill( ds );
|
|
|
|
|
Your code looks good and should execute without problems.
Is any stack trace of this error available?
(The complete trace from adp.Fill(...) to the access violation would be nice)Greetings
Covean
|
|
|
|
|
Respected,
i want one requirement we maintain the web site,so every 1 month my company newsletter will be send to the mailID's automatically ,requirement is one textbox and one button ,when u subbmit u r mailid the newsletter will send that time and every 1 month this is my requirement plse any body helpme sample application
Thanks,
|
|
|
|
|
Try rentacoder, they sell their services really cheap, people here are trying to learn to rather than asking for codez.
If you are going to do the job look into SQL server scheduled jobs and database email.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I know it is possible to use SSIS and other tools to translate data from one schema to another but does anyone know of any well-known patterns or code examples of doing this?
One wrinkle to the problem is that the original constraints, foreign keys, unique keys, etc., were not enforced at the database level and so translation has to consider this. In other words, fix things up as best as possible by doing things like inserting rows into parent tables when foreign keys are missing.
The data in question isn't very large; a max of around 200MB.
The spec would like the solution to be in two phases, one to translate into the new schema with data written out to the file system and another phase where the data is loaded into the new database, MS SQL Server.
Unless there is a free tool that can do this and do it well, the solution will need to be hand coded. Someone had already looked at using a mapping tool with poor results.
|
|
|
|
|
Having done this job a number of times over the years, I doubt there could be a tool to do this. So many of the decisions are a judgement call that an automated tool could not cut it.
treefirmy wrote: doing things like inserting rows into parent tables
This typical example - insert the parent or delete the child - is typical.
These type of jobs are where we really earn our money, if they ever automate it I'll be astonished but would like to meet the AI that does it.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the info.
Are there any online articles, etc. that might be helpful for me to read?
Since you have done this many times, is there any wisdom you might impart? This will be my first time. What things should I expect to run into other than memory issues?
Unfortunately, the data is coming in as a DataSet. Is that even reasonable? I’ve been told that DataSet is space inefficient. Are there other problems with DataSet to watch out for?
My idea was to work with the data in place:
1. Scrub the data so that it passes the value constraints of the new schema (rules come from an XML file)
2. Rename tables and columns
3. Create new tables, filling them with columns and rows of data (not sure this will even work - maybe have to create a new set and move data into it)
4. Fix up foreign key constraints
5. Either drop data for rows that have duplicate primary keys or somehow, make them unique. Both of these options seem very expensive time-wise.
6. Binary serialize the data to a file
For the upload, I was going to use the BulkCopy class. I considered just writing out table data in a format that bcp.exe could understand but bcp.exe only allows 263 rows. (Wow, that limit is weird, http://msdn.microsoft.com/en-us/library/ms162802.aspx).
|
|
|
|
|
Whenever I do this I do it at the database level, I would not even consider doing through any other interface. My usual process is something like:
Take a copy of the production DB
Create a target DB
Create as much of the data structure as you understand based on the current crap and what the business wants to do
Starting with the static tables (Country, City, ###Type etc) and script them into the new database
When all the static stuff is over take a backup of your target DB - this is your new start point
Now start with the ugly stuff, working through your data structures. Create additional tables to hold any new records created to support the new data integrity.
EVERY step requires a script, a reset script and an endless acceptance that NOTHING is final till the next start point. If you get a structure in and are happy with it take more backups as a new point. Always be prepared to trash a start point if you get it wrong.
NEVER accept that a a prior point is sacrosanct if it is wrong scrap it and fix the problem - the other guy probably didn't do this.
If you do a single action in the entire process that is not scripted and repeatable you are screwed and should quit now, before you start. Do not let your PM/boss push you into accepting a wrong decision.
Once you have the whole thing completed script out your target database. Create a new database with the script and run your transfer script using the latest production data, now go fix the NEW problems raised by the latest data, this is a diminishing return problem and needs to be repeated till it... well diminishes.Never underestimate the power of human stupidity
RAH
|
|
|
|