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
For MS SQL Server: SCOPE_IDENTITY() will return you the last generated identity value within your current scope: SELECT SCOPE_IDENTITY() AS NewID
For example, here’s a table that has a primary key but is not AUTO_INCREMENT: mysql> CREATE TABLE foo ( id INT NOT NULL, PRIMARY KEY (id) ); mysql> INSERT INTO foo VALUES (1), (2), (5); You can MODIFY the column to redefine it with the AUTO_INCREMENT option: mysql> ALTER TABLE foo MODIFY COLUMN id INT … Read more
A better way is to use SHOW KEYS since you don’t always have access to information_schema. The following works: SHOW KEYS FROM table WHERE Key_name=”PRIMARY” Column_name will contain the name of the 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
Actually, according to the JPA specification it is forbidden to change a primary key: The application must not change the value of the primary key[8]. The behavior is undefined if this occurs.[9] (from EJB 3 persistence (JPA) specification, paragraph 2.1.4)
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.
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
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
If you need to allow NULL values, use a UNIQUE constraint (or index) instead of a PRIMARY KEY (and add a surrogate PK column – I suggest a serial or IDENTITY column in Postgres 10 or later). Auto increment table column A UNIQUE constraint allows columns to be NULL: CREATE TABLE distributor ( distributor_id GENERATED … Read more