What will be index structure? I think unique index will not consist the entry for the old data, isn't it? In this case how to optimise the queries using that column? September 30, - am UTC. It is a bad objective. It will cause much confusion in the future. What if this constraint needs be dropped and recreated???? It will use a non-unique index. That was why I used defferable. Arun, October 03, - am UTC. Thanks Tom, I know the idea is really bad, I donot even try to do this, but need to get my point clear and get my spine strong to make understand some others too.
Thanks for your help. Still now the question remains open. Please suggest any alternative strategy to deal. October 03, - am UTC. A reader, October 04, - am UTC. Anything for a single-column unique index with NULLs? Last time I checked for them which I'll admit was a few years ago, now , they hardly had any.
Is it just a throw-back to a questionable design decision made years and years ago? Some point to the Oracle eBS data model as some sort of reference data model -- a reference of how things "should" be done -- since it was delivered by Oracle itself. There is the thought that maybe Oracle knows something that we don't. Any comments? Thanks, Matt. July 22, - am UTC.
When you install, you spend the first large chunk of time "finishing" the code - which involves in many cases you implementing your idea of what the constraints should be.
When we write our own applications - we know what the business rules are. Suppose I inherited a large table where someone decided that a not-null unique constraint was "better" than a primary key. And this table is used by several tables for referential integrity. Can I change the constraint from unique to primary key without dropping and re-building the integrity constraints? I'm using 10gR2. March 30, - pm UTC. Ok, this error message made me laugh.
Even if you could, the child tables are hardwired to the existing constraint - they'd have to drop and recreate too. You'll probably just want to live with it.
That's pretty much what I guessed. I decided to rename it to indicate that it is the intended PK until, in my "copious spare time", I want deal with dropping constraints, re-validating, etc etc. To get more knowledge shekhar p. But, I hv found that all the 3 cols same as pk b'coz all 3 pk cols recognises all 3 fk as child and vice versa. July 26, - am UTC. I have no idea a what you are saying b what you are asking really. But, I hv found that all the 3 cols same as pk b'coz all 3 pk cols recognises all 3 fk as child and all the 3 fk cols recognises all 3 pk as parent.
Will I be wrong to say? Sinha " Dear Sir, I have a pleasure to mail to you. To my surprise in all the books of whether Codd's Rdbms or RDBMS-Oracle it is written that only a single primary key can be defined and the software of Oracle also follows the same method of single primary key. I hope that I will get response fromyour kind end. My research was very very successfully lab tested done on 4th July,saturday, Thanking you, with regards, Shekhar P.
Sinha, Guwahati, Assam, India. No, you have not. You have other attributes that are: a unique when considered together b not null but they are not a primary key. No idea why you think you have 'invented' or even discovered anything?? A reader, October 30, - pm UTC. Also do you think any maintenance overhead if primary key is use and business want to add or remove the key in the future? Are you telling me nothing has changed in 15 years? Isn't it true that 15 years ago a gb Oracle database was considered large, in they thought a terabyte was frightenly large so much so, they put out press releases telling us you could actually do it - but today databases of 10's to 's of terabytes are common, normal - do you not think that things might have changed over time.
And, just because you did something in the past does NOT mean it was right back then either " They can do that, but only as long as they also add the primary key constraint as well we'll use that index and not create another one And the work would be the same.
A reader, December 21, - pm UTC. Hi Tom, You have given two different answers for redundant null constraints. I am finding it difficult to conclude the reason being a novice oracle developer. Please shed some light on this with an example.
Question 1 Is it a bad idea to place constraints pk and unique on non unique indexes. Should we use this freely? Followup October 3, - 11am Central time zone: It is a bad idea to ask the database to use a non-unique index to enforce a unique or primary key?
In general - no, it is not a bad idea, if you have deferrable constraints, it would be mandatory in fact. I would not I do not make all constraints deferrable however especially primary keys - if you do that, make sure to add a redundant NOT NULL constraint!!
Question 2: Tom, I sometimes come across DDL like create table t1 c1 number 10 not null, c2 varchar2 30 not null, c3 date Thanks Followup July 13, - 1pm Central time zone: it is somewhat redundant.
Thanks for providing such a excellent service to oracle community. May 10, - pm UTC. There, the point was: "hey, we have a non-unique on something. My answer is and has been and will be consistent and never ending. DO put a primary key constraint on a column, then you may call it a primary key Do not confuse a discussion about how Oracle enforces a constraint we can use a unique or non-unique index - either one with whether a constraint should or should not exist!
I see nothing conflicting in neither q1 nor q2 did I say "use a primary key AND a not null". Put a constraint in place once.
Realize that if you are a primary key, you are already not null - putting not null on a primary key makes you look like you do not understand or know what you are doing. Doesn't matter if it doesn't or does affect performance, it would not be 'right' so don't do it.
Your answers are excellent as usual. Thanks for your patience in explaining the basic concepts. Many contributors should understand that there is a huge difference between being pedantic and being appropriately right. Natural primary key exists Hi Tom, I have tables where single unique row is identified by a combination of "key" columns. Looks like a natural primary key? The columns are of type "text", or rather "varchar2". Tricky part: The text can be empty. Or maybe NULL should be differ from empty string?
October 06, - am UTC. And another fact is that none of the columns in a primary key can be null - so therefore, these attributes cannot be the primary key. Hi Tom, I use surrogates almost all the time, and then I demand that some other group of columns be defined as an AK sometimes, this doesn't always happen, but Now reading Shekhar p.
Sinha's posting, I was laughing because he hasn't discovered anything, but he does actually touch on something which is that the Primary Key is many times arbitrary. There are many times a table has multiple keys to it but we choose one as a PK. That choice is arbitrary. To me, the PK could easily have been just "Key" and both choices could have been identified as the "Key". I'm not suggesting Oracle drop the PK. October 07, - pm UTC.
A primary key is a set of non-nullable attributes that are immutable. That is different from a "key", a "key" can point to many rows. A primary key points to one and exactly one row - always the same row - forever. The point is the term Primary. There are times when the choice of a primary key is arbitrary. It is not arbitrary, there might be times when you have more than one choice for the primary key because someone FORCED a surrogate on you for example when you had a perfectly good natural key - but it is not arbitrary, it is something you chose when implementing your model.
Give me an example otherwise. Followup: Natural primary key exists Some of the fields can contain null values as long as the combination of values is unique. Oracle does not permit you to create both a primary key and unique constraint with the same columns. While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy.
Please re-enable javascript in your browser settings. What is a unique constraint in Oracle? Note In Oracle, a unique constraint can not contain more than 32 columns.
0コメント