Does the order of columns in a PK index matter?
Yes it does.
By default, the primary key constraint is enforced in SQL Server by a unique clustered index. The clustered index defines the logical order of rows in the table. There may be a number of extra index pages added to represent the upper levels of the b-tree index, but the lowest (leaf) level of a ...
Use a compound primary key:
CREATE TABLE yourtable
PRIMARY KEY (employeeid, recordmonth, recordyear)
And if your table already exists, drop the old primary key:
ALTER TABLE yourtable
DROP PRIMARY KEY;
PostgreSQL will not try to insert duplicate values on its own, it is you (your application, ORM included) who does.
It can be either a sequence feeding the values to the PK set to the wrong position and the table already containing the value equal to its nextval() - or simply that your application does the wrong thing. The first one is easy to fix:
You can use the function pg_get_constraintdef(constraint_oid) in a query like the following:
SELECT conrelid::regclass AS table_from
WHERE contype IN ('f', 'p ')
AND connamespace = 'public'::regnamespace -- your schema here
ORDER BY conrelid::regclass::text, contype DESC;
Your design is good. If you are having a performance problem (which you can't know at design time), you should create an index on column table1.ref_field, in the same order (ASC) as the table2.id column. This will improve performance on joins between those to tables/columns. There is overhead to maintaining any index, so you want to weigh that cost against ...
You are obviously suggesting that CONSTRAINTs in a database should be enforced by the application(s) that/which access that database?
There are many reasons why this is a bad (bad, bad...) idea.
1) If you are building a "roll-your-own" constraint "engine" (i.e. within your application code), then you are merely emulating what Oracle/SQL Server/MySQL/...
Like you said. A FOREIGN KEY constraint referencing the same table is typically for a hierarchy structure and it would use another column to reference the primary key. A good example is a table of employees:
EmployeeId Int Primary Key
ManagerId Int Foreign key going back to the EmployeeId
So in this case there is a ...
how can i use multiple primary keys in postgres ?
You can't. It's an oxymoron - the definition of a primary key is that it's the primary key, singular. You can't have more than one.
You can have multiple unique constraints. You can have a primary key that contains multiple columns (a composite primary key). But you can't have more than one primary key for ...
If you were using a person's name as a primary key and their name changed you would need to change the primary key. This is what ON UPDATE CASCADE is used for since it essentially cascades the change down to all related tables that have foreign-key relationships to the primary key.
CREATE TABLE dbo.People
Identity columns and Primary Keys are two very distinct things. An Identity column provides an auto-incrementing number. That's all it does. The Primary Key (at least in SQL Server) is a unique constraint that guarantees uniqueness and is usually (but not always) the clustered key. Again in MS SQL Server it is also an index (in some RDBMS they are not as ...
A Super Key is simply a non-minimal Candidate Key, that is to say one with additional columns not strictly required to ensure uniqueness of the row.
A Primary Key is a minimal Candidate Key, which is to say all constituent columns are strictly required in order to ensure uniqueness.
As a database developer/designer of 30 years experience, I had never even ...
I have used a hybrid approach with success. Tables contain BOTH an auto-increment primary key integer id column AND a guid column. The guid can be used as needed to globally uniquely identify the row and id can be used for queries, sorting and human identification of the row.
Now I have a SQL statement, which selects the MAX value, adds to it, and INSERTs that value into the same table -- all inside one statement. So in theory, it's not possible to get a PK violation on this statement (although I think could possibly get a deadlock). But, somehow, it IS getting one.
It definitely IS possible. This is due to your isolation level ...
Super Keys: Super Key stands for superset of a key.
A Super Key is a set of one or more attributes that are taken collectively and can identify all other attributes uniquely.
For example, consider the table:
Book (BookId, BookName, Author)
So in this table we can have
- (BookId, BookName)
- (BookId, BookName, Author)
- (BookId, Author)
As per the documentation, the precision of the CURRENT_TIMESTAMP is microseconds. Thus, the probability of a collision is low, but possible.
Now imagine a bug which happens very rarely, and causes database errors. How hard is to debug it? It is a far worser bug than one which is at least deterministic.
The more broad context: you probably want to avoid ...
You can insert into an auto-increment column and specify a value. This is fine; it simply overrides the auto-increment generator.
If you try to insert a value of NULL or 0 or DEFAULT, or if you omit the auto-increment column from the columns in your INSERT statement, this activates the auto-increment generator.
So, it's fine to INSERT INTO table1 SELECT * ...
The way I understand your question is that you have an existing table with a column that has up until now been populated with manual values, and now you want to (1) make this column an IDENTITY column, and (2) make sure that the IDENTITY starts from the most recent value in the existing rows.
First off, some test data to play with:
CREATE TABLE dbo....
Assuming by "auto-incrementing" you mean the Postgres SERIAL pseudo-type, the short answer is "not always".
SERIAL columns are implemented using standard SQL sequences, which might generate out-of-order values when used by multiple concurrent sessions if the CACHE parameter is set to something more than 1. The manual states this:
[A]lthough multiple ...
CREATE TABLE `user_mv` (id INT AUTO_INCREMENT PRIMARY KEY) SELECT `user`.`firstname` as
`user`.`lastname` as `lastname`,
`user`.`lang` as `lang`,
`user`.`name` as `user_name`,
`group`.`name` as `group_name`
inner join `user_groups` on (`user`.`user_id`=`user_groups`.`user_id`)
left join `group` on (`group`.`...
Yes, the order is critical. I highly doubt you ever query by RowNumber (eg WHERE RowNumber=1). Overwhelmingly time series are queried by date (WHERE DataDate BEWEEN @start AND @end) and such queries would require a clustered organization by DataDate.
Fragmentation in general is a red-herring. Reducing fragmentation should not be your goal here, but having a ...
Yes, absolutely there are negative consequences for using a string instead of a numeric type for a Primary Key, and even more so if that PK is Clustered (which it indeed is in your case). However, the degree to which you see the effect(s) of using a string field is a function of a) how many rows are in this table, and b) how many rows in other tables are ...
You have the hobt_id so the following query will identify the table:-
FROM sys.partitions p
INNER JOIN sys.objects o ON p.object_id = o.object_id
WHERE p.hobt_id = 72057632651542528
From that you can then run the following statement to identify the row in the table (if it still exists):-
SELECT %%LOCKRES%%, *
FROM [TABLE NAME] WITH(INDEX(...
Unless you explicitly state a desired order using an ORDER BY clause you can not guarantee the order that data will be presented in response to a query. Without an ORDER BY clause the engine is free to present data to you in any order it finds most convenient at the time, which can mean a different order for the same query you ran earlier.
If there is a ...
I'm not sure why you're using a variable, but you need to protect multiple statements with a transaction. What's happening is two users are calling the procedure at the same time, both are getting rowcount = 0, and then they're both trying to insert as a result.
set transaction isolation level serializable;
set value = ...
Acceptable? Sure. Common? No. Beneficial? Doubtful.
At my old job we inherited a system where they had a central sequence generator (this was a SQL Server system long before SEQUENCE was introduced in SQL Server 2012). It wasn't really a performance bottleneck and shouldn't be unless you're generating hundreds of thousands of values per second. But it made ...
Couple ways to skin this cat but this works fine in SQL Server 2005 and up, and I find it a pain free way to handle the problem -
The OBJECTPROPERTY() function can list various properties about objects - like tables. One of those properties is whether or not a table has a primary key.
OBJECTPROPERTY(object_id, tablehasprimarykey) = 0 would be a table ...
Can you create a database table without a primary key? Well, you just said you can in SQLite. And, I believe that holds true for almost every (if not every) major DBMS platform.
Should you create a database table without a primary key? No.
Every table should have some column (or set of columns) that uniquely identifies one and only one row. It makes it ...
You are most likely tying to insert a row in a table for which the serial column sequence value is not updated.
Consider following column in your table which is primary key defined by Django ORM for postgres
id serial NOT NULL
Whose default value is set to
The sequence is only evaluated when the id field is set as ...
This is by design - all DBMS act this was with auto-increment columns.
If they did not external referential integrity could be damaged. For a simple example of this, imagine you are storing URLs for a shortening service using an auto-increment column as the key. You don't know if the shortened URL has been given out to anyone yet, and the database certainly ...
To start with the correct primary key (single column vs multiple or artificial vs natural) is to use the primary key that is correct for the task.
Some people will tell you to always use an Artificial or Surrogate Key. This is a key that is a generated key and has nothing to do with the information it represents. For example an identity column or a GUID....