Tag Info

New answers tagged

1

Store the tenant_id first. When you do this you can enable index key compression. See http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#i1106790 for the syntax and http://docs.oracle.com/cd/B28359_01/server.111/b28318/schema.htm#i14618 for the concepts. In your case, you can do it like this: create unique index mytable_idx on ...


1

One thing you need to keep in mind is what you are using PK for. At the logical level (sometimes called the conceptual level), the primary key is simply one of the candidate keys, chosen somewhat arbitrarily. The purpose is to guarantee that each row is unique, that each row has an identifier, and that no part of an identifier is left out (NULL). For ...


6

Yes. It's in the MSDN documentation pages: Foreign Key relationships A FOREIGN KEY constraint specified at the table level must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list. That page does not ...


0

In create table you have set teams as primary key, and also you are aware that primary key does not allow duplicate values. mysql> create table tblShowteam( -> SetId int, -> datum date, -> teams int, -> primary key (teams)); Query OK, 0 rows affected (0.13 sec) Check the ...


0

Your idea isn't that crazy. Ordering the primary key DOES reduce fragmentation, but this can be achieved with an IDENTITY column. However, ordered keys also has a drawback, namely the one described here: http://kejser.org/clustered-indexes-vs-heaps. Now, I can see how you would benefit from knowing WHEN the key was generated and that you therefore want pack ...


0

My preference is to use an identity column for primary key and if required add a unique index on other fields if you need to enforce uniqueness. In my opinion, a primary keys job is to uniquely identify a single row of data. Integers do this more efficiently without risk of ambiguity. I dislike using varchars as pkeys or in joins as they can be affected by ...


1

Hmm, I agree with you, almost, and not with the folks who suggest using an arbitrary integer to identify your rows. Your primary key should be tid char(2), asof datetime, sequence smallint where you control the sequence. That's a perfectly fine primary key because it identifies the row. The burden is on the surrogate crowd to explain the ...


4

Sure you CAN do it, but why would you? To save a few CAST expressions? That seems a bit weak. The reasons to have auto incrementing surrogate int PK's are many: You pretty much never have to manage them with normal databases. Ever. Unless you are rebuilding tables or inserting data where you have to turn the identity off and on. But operationally it works ...


2

This would most definitely not be preferred to an auto-increment integer field. For starters: Index width would be degraded significantly with your proposal. Just how do you propose to manage all those random 2-digit integers to enforce uniqueness. Have you thought of how much code would have to be written and maintained to implement this scheme. Won't ...



Top 50 recent answers are included