New answers tagged

1

Thank you @Greg for answering the question. The problem required that the SQL statement fully qualify the columns in the where clause by referencing the tables for the columns. In this case. I needed m_econ_fred_source=attrib_id. The complete solution is: SELECT [update], [value] FROM m_econ_fred_source JOIN s_econ_fred ON m_econ_fred_source.param_id = ...


0

As I stated in a comment above, you'll want to re-enable a primary key. If you already have a filled table, you may have to migrate it to a new table, as - from what I recall - Access doesn't like creating autonumber fields in existing tables that don't currently have them. So, create a new Query, ignore the design view and click over to SQL view (close ...


5

The strongest advice I can give is to keep the primary key immutable unless it is absolutely unfeasible for your use-case. You appear to be describing 2 different datasets. User-Actions taken during a given Period ( defined by the composite key of Year-Week ) User-Actions taken during an unknown Period My initial impression is that the unknown period ...


1

You can use a default date of something way out of bounds. e.g. in the table definition YEAR INTEGER DEFAULT 1776, WEEK INTEGER DEFAULT 99 That way you know that everything gets entered with some date. It will continue to not allow you to enter in duplicate data. In addition, you can create an exception report based off these values kicked off to someone ...


0

You could do a lot of stuff but integer and text do not sort the same. Text would break a search on week > 6. In text 10, 11, 12 are not > 6. I would use 0 for no date Not that size is the big of a deal but you would use tinyint for week and smallint for year one varchar is the size of a smallint


-1

A primary key is a logical concept. When you create a primary key you are telling the DB that every row will be unique based on the columns specified in the primary key. How the DB enforces this uniqueness is up to the DB vendor. Microsoft has chosen to enforce the uniqueness of a primary key by creating a unique index.


-2

When you create Index or PK, SQL never creates a "File". When you create PK, it will be clustered by default, unless you already have clustered index or specified PK as NON CLUSTERED.


1

The leaf level of the Clustered index is the table. You still have the other levels of the B-Tree (root, intermediate) as a part of the clustered index which account for why there is an index for your PK on the table.


1

Stability is a desirable property for a key but it is a relative thing and not an absolute rule. In practice it's often useful to change the values of keys. In relational terms data is identifiable only by its (super)keys. It follows that if there is only one key in a given table then the distinction between A) changing a key value, or B) replacing the set ...


3

Interestingly enough, the linked question about ROWGUID sort of provides its own use case: when you have conflicting primary keys in databases that need to be synchronized. If you have two databases that need to be reconciled and they use sequences for primary keys, you'll want one of the keys to change so it can remain unique. In an ideal world, this would ...


8

While you can use a key which is natural and/or mutable as your PK, in my experience that leads to problems, which can often be prevented by the use of a PK which meets these conditions: Guaranteed Unique, Always Exists, Immutable, and Concise. For example, many companies in the US try to use Social Security Numbers as personal ID numbers, (and PKs) in ...


5

The primary key could be altered when synchronization is involved. This could be the case when you have a disconnected client and it synchronizes the data with the server at certain intervals. A few years ago I worked on a system where all event data on the local machine had negative row Ids, like -1, -2, etc. When the data was synchronized to the server, ...


0

One possible scenario is let's say you have affiliates that have unique ID and you know they will not duplicate across affiliates as they have unique starting character. The affiliates load data to a master table. There records are processed and then assigned a master ID. Users need access to the records as soon as they are loaded even if they are not yet ...


-1

Imagine a situation like when someone chose National Insurance Number (NIN) as a Primary Key and somehow an operator inserts a row with the wrong NIN. After inserting the value, there are two ways to correct the error: Delete the mistaken record and insert a new one Update the value to the correct one and use On Update Cascade if there is a referential ...


3

Any design that involves changing the PRIMARY KEY on a regular basis is a recipe for disaster. The only good reason for changing it would be an amalgamation of two previously separate databases. As pointed out by @MaxVernon occasional changes may occur - then use ON UPDATE CASCADE, although the majority of systems nowadays use an ID as a surrogate PRIMARY ...


23

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


2

That smells a lot like a "many-to-many" relationship table. Is it? As for linking from a changelog -- space is not the issue; correctness is. A change log should have exactly what existed at the time the log was taken. What if the original table is updated? If the table in question is changed for any reason, I think you do not want the change reflected ...


0

Why do you need a PK? Why not just go with company_id as a non-clustered index? You said most searched are on all entries or by company_id Rarely update Rarely delete org_path, this is the only table in which it exists The answer from Martin Smith may get you what you need I am not familiar with automatically add a 4 byte integer uniqueifier Maybe ...


10

One thing to consider is that a Primary Key and a Clustered Index are not the same thing. A Primary Key is a constraint and deals with the rules by which the data lives (i.e. data integrity); it has nothing to do with efficiency / performance. A Primary Key requires that the key column(s) be unique (in combination) and NOT NULL (individually). A PK is ...


7

For a non-unique clustered index on comany_id alone, SQL Server will automatically add a 4 byte integer uniqueifier to all duplicate (i.e. second and subsequent for a key value) clustered index keys to make it unique. This is not exposed to the user though. The advantage of adding your own unique identifier as a secondary key column is that you can then ...



Top 50 recent answers are included