No, if you do not use “order by” you are not guaranteed any ordering whatsoever. In fact, you are not guaranteed that the ordering from one query to the next will be the same. Remember that SQL is dealing with data in a set based fashion. Now, one database implementation or another may happen to … Read more
Technically yes, but if a string makes sense to be the primary key then you should probably use it. This all depends on the size of the table you’re making it for and the length of the string that is going to be the primary key (longer strings == harder to compare). I wouldn’t necessarily … Read more
A clustered index on GUID is not a good design. The very nature of GUID is that it’s random, while a clustered index physically orders the records by the key. The two things are completely at odds. For every insert SQL has to reorder the records on disk! Remove clustering from this index! The time … Read more
Yes you can. You just need to make that column be an index. CREATE TABLE `test` ( `testID` int(11) NOT NULL, `string` varchar(45) DEFAULT NULL, `testInc` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`testID`), KEY `testInc` (`testInc`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; insert into test( testID, string ) values ( 1, ‘Hello’ ); insert into test( … Read more
So I was finally able to find out how to get this to work. I wish I hadn’t lost the link to the blog I read last night as I didn’t write the code. public T GetByPrimaryKey<T>(int id) where T : class { return (T)_db.GetObjectByKey(new EntityKey(_db.DefaultContainerName + “.” + this.GetEntityName<T>(), GetPrimaryKeyInfo<T>().Name, id)); } string GetEntityName<T>() … Read more
I would use a generated PK myself, just for the reasons you mentioned. Also, indexing and comparing by integer is faster than comparing by strings. You can put a unique index on the name field too without making it a primary key.
A cursory search of Google for ‘hibernate custom id generator tutorial’ turned up the following possibilities. I’ve excluded those that don’t look useful and summarized the content of each. http://www.devx.com/Java/Article/30396 – covers the issues of generating an ID before the data is persisted (and hence does not yet have a business key). http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html_single/#mapping-declaration – the … Read more
Simply add the primary key after uploading the table with pandas. group_export.to_sql(con=engine, name=example_table, if_exists=”replace”, flavor=”mysql”, index=False) with engine.connect() as con: con.execute(‘ALTER TABLE `example_table` ADD PRIMARY KEY (`ID_column`);’)
Converting an int to a serial more or less only means adding a sequence default to the value, so to make it a serial; Pick a starting value for the serial, greater than any existing value in the table SELECT MAX(id)+1 FROM mytable Create a sequence for the serial (tablename_columnname_seq is a good name) CREATE … Read more
(Updated – Thanks to the people who commented) Modern Versions of PostgreSQL Suppose you have a table named test1, to which you want to add an auto-incrementing, primary-key id (surrogate) column. The following command should be sufficient in recent versions of PostgreSQL: ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY; Older Versions of PostgreSQL … Read more