52

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 ...


48

Use a compound primary key: CREATE TABLE yourtable ( employeeid INT, blahblah VARCHAR(255), blahblah2 VARCHAR(255), recordmonth DATE, recordyear DATE, PRIMARY KEY (employeeid, recordmonth, recordyear) ) And if your table already exists, drop the old primary key: ALTER TABLE yourtable DROP PRIMARY KEY; And ...


38

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: ...


32

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 ...


32

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/...


31

You can use the function pg_get_constraintdef(constraint_oid) in a query like the following: SELECT conrelid::regclass AS table_from , conname , pg_get_constraintdef(oid) FROM pg_constraint WHERE contype IN ('f', 'p ') AND connamespace = 'public'::regnamespace -- your schema here ORDER BY conrelid::regclass::text, contype DESC; Result: ...


29

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 EmployeeName String ManagerId Int Foreign key going back to the EmployeeId So in this case there is a ...


26

Many people have heard guidance that "sequential scans are bad" and seek to eliminate them from their plans, but it isn't so simple. If a query is going to cover every row in a table, a sequential scan is the fastest way to get those rows. This is why your original join query used seq scan, because all rows in both tables were required. When planning a ...


26

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 ...


25

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. For example: USE tempdb; GO CREATE TABLE dbo.People ( PersonKey ...


24

A table can have at most one PRIMARY KEY constraint but it can have as many as you want UNIQUE KEY constraints. Columns that are part of the PRIMARY KEY must be defined as NOT NULL. That is not required for columns that are part of UNIQUE KEY constraints. If the columns are not Nullable, then there is no difference between Unique and Primary Keys. Another ...


24

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 ...


23

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 ...


23

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 ...


22

One more advice - never use GUIDs as part of clustered index. GUIDs are not sequential, thus if they are part of clustered index, every time you insert new record, database would need to rearrange all its memory pages to find the right place for insertion, in case with int(bigint) auto-increment, it would be just last page. Now if we look to some db ...


18

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.


18

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, We are having table Book (BookId, BookName, Author) So in this table we can have  (BookId)  (BookId,BookName)  (BookId, BookName, Author)  (BookId, Author) ...


18

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 ...


17

If your primary key is of the UNIQUEIDENTIFIER, make sure to specify that it's NONCLUSTERED. If you make it clustered, every insert will have to do a bunch of shuffling of records to insert the new row in the correct position. This will tank performance.


16

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 ...


15

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 ...


14

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 ...


14

You have the hobt_id so the following query will identify the table:- SELECT o.name 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(...


14

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....


13

For a non partitioned table I get the following plan There is a single seek predicate on Seek Keys[1]: Prefix: DeviceId, SensorId = (3819, 53), Start: Date < 1339225010. Meaning that SQL Server can perform an equality seek on the first two columns and then begin a range seek starting at 1339225010 and ordered FORWARD (as the index is defined with [Date]...


13

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 ...


13

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 ...


13

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 * ...


13

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 ...


13

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; begin transaction; update dbo.table1 set value =...


Only top voted, non community-wiki answers of a minimum length are eligible