Tag Info

New answers tagged

2

As you don't know if there's any logic in the keys you can't tell if a huge or negative number will lead to a key violation. Without knowledge of the application I'd suggest importing the previous pupils in the new software to get a regular id is the way to go.


5

Imagine you are out with a group of friends and the conversation turns to movies. Someone asks, "What did you think of 'The Three Musketeers'?" You respond, "Which one?" What additional information would you need to be absolutely certain you are both thinking of the same movie? The director's name? The production studio? The year it was released? One of the ...


4

Your table definition looks reasonable all over now. With all columns NOT NULL the UNIQUE constraint will work as expected - except for typos and minor differences in spelling, which may be rather common I am afraid. Consider @a_horse's comment. Alternative with functional unique index The other option would be a functional unique index (similar to what ...


1

As your API is build around the UUID being the key for these rows you are stuck with that as your functional primary key at this point, though that in itself is not a bad thing. Clustering on a UUID column is not recommended though due to the randomness of the incoming values - this causes a lot of page splits and fragmentation on the clustered index as you ...


3

The ID column has no advantage at all when it comes to the uniqueness you want/need to enforce. Uniqueness of whatever combination of attributes is never going to be enforced by adding a meaningless ID. Its "advantage" only shows when you ever get to the point where you'd need a new table that needs a foreign key to this one. In that case, and IF you have ...


1

There are many different ways of defining a primary key and I think its there to match with standard SQL. Below are 3 different ways of creating a primary key: create table dept (deptno decimal(2,0) PRIMARY KEY, dname varchar(14), loc varchar(13)); create table dept (deptno decimal(2,0), dname varchar(14), loc varchar(13), constraint pk_dept primary key ...


2

A primary key is an object that lives in the meta data of the database. It will have a name no matter how it is created. The question is will it have a meaningful name that matches your coding standards and that your team can understand at a glance or will it have a meaningless name the the system generates and everyone will continually have to look up? ...


4

The is a supplement to the answers already posted by DBAFromTheCold and Aaron Bertrand. Microsoft has still left %%lockres%% as undocumented feature. Below is the script that will help you : declare @databaseName varchar(100) = 'yourdatabaseName' --CHANGE HERE ! declare @keyValue varchar(100) = 'KEY: 9:72057632651542528 (543066506c7c)' --Output from ...


5

Sorry, was already working on this answer and about to post when the other appeared. Adding as community wiki only because it's a slightly different approach and adds a bit of other info. The 543066506c7c is essentially a hash of the primary key, and you can retrieve that row (and potentially any rows with a hash collision) using this dynamic SQL: -- ...


8

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


4

If I understood you correctly, you have two sorts of entities to manage: Players (human beings that participate in your game) Characters (fictional entities that the players use/manipulate in your game) Once you look at your problem like that, you could model it easily like this: players table, with a player_id as PK and information about your real ...


1

Properly normalized it will have 3 tables as follows: Kid kid_id (pk) classroom_id (fk to Classroom) Teacher teacher_id (pk) name Classroom classroom_id (pk) teacher_id (fk to teacher) This way you have no duplication and no update anomalies.


6

I'll add one aspect to the existing excellent answers: Documentation. Often it is important to see what kinds of keys you can use to identify an entity. Any combination of unique columns is a candidate key. The primary key tends to be an especially useful concept in practice. Whether you enforce a key or not (you probably should) the documentation is ...


6

When you create a key in a database the DBMS engine enforces a uniqueness constraint on the key attributes. This serves at least three related purposes: Data integrity: duplicate data cannot be entered into key attributes. Any dependencies on the keys are therefore guaranteed. Identification: users are able to rely on keys as a means of identifying and ...


19

You are obviously suggesting that CONSTRAINTs in a database should be enforced by the application(s) that 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/ have spent ...


0

I will be using MS SQL terms below but you can translate them into the DBMS that you will be using. I also made some assumptions that seemed logical, so you can drop those if they do not fit your model. I would setup the tables in the following manner: Person Table PersonID int PK FName Varchar(50) LName varchar(50) DoB Date FK Name Table Name ...


0

I'm guessing a bit on what your query is supposed to do. I assume you are looking for all users that share 5 or more items with a certain user: SELECT a.userid FROM ratings AS a WHERE EXISTS ( SELECT 1 FROM ratings AS b WHERE b.userid = :user AND a.userid <> b.userid AND a.showid = b.showid ) GROUP BY a.userid HAVING ...


4

Well it's quite simple. You have enabled the safe update mode (as the error stated). Your statement has a where clause, right. But it's interpreted as a JOIN not as a WHERE as you provide a syntax like a INNER JOIN. Your code: UPDATE table_a a, table_b b SET a.update_me = b.update_from_me WHERE a.a_id = b.b_id; Is technically the same (and interpreted ...



Top 50 recent answers are included