Pk Constraint Oracle
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Pk Constraint Oracle as PDF for free.
More details
- Words: 1,414
- Pages: 4
Primary and Unique Key Constraints
Administration Tips
Primary and Unique Key Constraints Any time you declare a Primary or Unique Key constraint on a table, Oracle will automatically create an index for you on the constrained column (unless you already created your own one). For example, following this command: CREATE TABLE CON_TEST
(
COL1 NUMBER CONSTRAINT CONTEST_COL1_PK PRIMARY KEY, COL2 CHAR(5));
...you can immediately issue this query: SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME FROM DBA_INDEXES WHERE INDEX_NAME LIKE
'CON%';
... and get this sort of report: INDEX_NAME -------------------CONTEST_COL1_PK
UNIQUENESS TABLE_NAME ----------------------- -----------UNIQUE CON_TEST
...and you'll notice immediately two key things. First, the index name is the same as the constraint name specified during the "create table" statement (and that's an excellent reason for getting into the habit of naming your constraints properly whenever you define them). And secondly, Oracle created a unique index for us. Both these things happen when you declare unique constraints, too, not just primary key ones. The last fact, that the index was unique, might not be so surprising, if you think about it: after all, the very definition of a primary key is that every entry is unique. And unique key constraints will also be unique (!) -so its blindingly obvious that Oracle will create a unique index. Isn't it? Well, it might be blindingly obvious, (it also happens not always to be true, but more on that later) but it has one unfortunate side effect. If I disable my primary key constraint (perhaps because I am going to do some maintenance work, or a million row insert), I do it like this: ALTER TABLE CON_TEST DISABLE CONSTRAINT CONTEST_COL1_PK;
If I then immediately re-submit my query about the indexes associated with this table, I get this: Copyright © Howard Rogers 2001
10/18/2001
Page 1 of 4
Primary and Unique Key Constraints
Administration Tips
SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME FROM DBA_INDEXES WHERE INDEX_NAME LIKE
'CON%';
NO ROWS SELECTED
In other words, disabling the constraint caused the index to be dropped. This behaviour is potentially appalling. There you are, intending to disable the constraint for two minutes' maintenance work, and woosh! An entire 10Gb index disappears -which means that when I re-enable the constraint, the entire thing has to be rebuilt from scratch, necessitating exclusive table locks until the entire 10Gb edifice is reconstructed (and this has actually happened to me!) So there's the first thing to learn about both primary key and unique key constraints: if enforced with a unique index, the index is dropped when the constraint is disabled, and re-built when re-enabled. Is there anything to be done about this? Well, not in Oracle 7 there isn't. But in Oracle 8.0, the idea of a 'deferred' constraint was introduced. A deferred constraint is one that is not checked as each insert is performed, but is checked when all the inserts have finished, and the User attempts a commit. If at that point, a record that violates the constraint is detected, the complete set of inserts is rolled back. One of the main ideas behind deferring constraints in this way is that it allows bulk loads of data to proceed much faster than if the constraint is "immediate" -there's one mammoth check at the end of the load, rather than 10,000,000 little immediate checks as each row of new data is inserted. But think about it... If we are inserting data which might temporarily violate the constraint, fair enough that Oracle will sort that out when we come to commit. But in the meantime, where does that bit of bad data have to reside? Well, in the table, of course (since data can't just float around in the ether!). So, when the constraint is deferred, records which violate the constraint must be allowed to get into the table (although they'll be kicked out when we commit). But how can a non-unique record reside in the table, however temporarily, if there's a unique index on that table? The answer is it can't -the index will protest at the inclusion of non-unique leaf entries, and will throw an error. So how then can we ever defer a constraint if there is a unique index backing it up, as the example shown above makes clear there will be? Well, the answer is that we can't. If the index is unique, the constraint can not be deferred. But put it the other way around: if the constraint is deferred, the index can not Copyright © Howard Rogers 2001
10/18/2001
Page 2 of 4
Primary and Unique Key Constraints
Administration Tips
be unique! Even more importantly, the determinant is whether the constraint is deferrable, not whether it is actually deferred or not. A deferrable constraint can be 'initially immediate' or 'initially deferred'. It's the fact that it might one day be deferred that is important to Oracle. Therefore, if I issue the following command: CREATE TABLE CONTEST2
(
COL1 NUMBER CONSTRAINT CONTEST2_COL1_PK PRIMARY KEY DEFERRABLE INITIALLY IMMEDIATE, COL2 CHAR(5));
...and then re-run my query for the index details, we see this: INDEX_NAME UNIQUENES TABLE_NAME ------------------------ --------- --------------CONTEST2_COL1_PK NONUNIQUE CONTEST2 ...and we discover that a primary key constraint is now being enforced with a NON-unique index. So what happens when we disable the constraint now? SQL>
ALTER TABLE CONTEST2 DISABLE CONSTRAINT CONTEST2;
TABLE
ALTERED.
SQL> SELECT INDEX_NAME, UNIQUENESS, 2 FROM DBA_INDEXES 3 WHERE INDEX_NAME LIKE 'CON%';
TABLE_NAME
INDEX_NAME UNIQUENES TABLE_NAME ------------------------------ --------- -------------CONTEST2 NONUNIQUE CONTEST2 ...and now you'll notice that the index is retained after the constraint is disabled. Summing all that up, we get a very simple rule: the index associated with a primary or unique key constraint is dropped when the constraint is disabled if it is unique, but is retained if it is non-unique. And that translates into a simple rule of thumb for most DBAs: always make your unique and primary key constraints 'deferrable'. Whether you choose then to make them actually deferred or initially immediate is up to you. But by making them deferrable, you put
Copyright © Howard Rogers 2001
10/18/2001
Page 3 of 4
Primary and Unique Key Constraints
Administration Tips
yourself in control over when indexes are dropped, not let Oracle silently make the decision for you. Before buying into that piece of advice, you might reasonably ask whether the fact that a constraint is using a unique index makes it faster than if it using a non-unique index. Are there any performance penalties in making constraints deferrable? The answer is no. It's just as quick to use a non-unique index as a unique one. That might surprise you, but the reason for it isn't hard to find. Although the index is created as nonunique, its contents will actually be unique, because even when deferrable, the constraint IS enforced at commit time. Any violating records found at that point are rolled back out of the table (and hence out of the index) -so the index contents are indeed going to be unique in practice. The optimiser is smart enough to know this, and therefore it treats a non-unique index which it knows to be enforcing a primary or unique key constraint in exactly the same way as it would a unique index. In effect, it totally ignores the 'uniqueness' attribute of an index when working out how best to answer a query or perform some DML. One final point which many DBAs seem to overlook. The indexes that are created when unique and primary key constraints are defined need to be housed in a tablespace somewhere. In none of the examples I've shown above have I mentioned that: and accordingly, all my indexes would have been created in the default tablespace of the User who was issuing the 'create table' commands. That's not usually what you want: indexes are normally sent to their own tablespace. That needs to be specified when you define the constraint like this: CREATE TABLE CONTEST2
(
COL1 NUMBER CONSTRAINT CONTEST2_COL1_PK PRIMARY KEY DEFERRABLE INITIALLY IMMEDIATE USING INDEX TABLESPACE
INDX01,
COL2 CHAR(5));
The 'using index' clause is the one that does the deed. You can also stick a storage clause in there if you really want to (my advice is never to specify storage clauses at the segment level -that's why there's a tablespace 'default storage' clause!). In summary: primary and unique key constraints should always be declared deferrable, and should always include a 'using index' clause to house the associated index in an appropriate tablespace.
Copyright © Howard Rogers 2001
10/18/2001
Page 4 of 4
Administration Tips
Primary and Unique Key Constraints Any time you declare a Primary or Unique Key constraint on a table, Oracle will automatically create an index for you on the constrained column (unless you already created your own one). For example, following this command: CREATE TABLE CON_TEST
(
COL1 NUMBER CONSTRAINT CONTEST_COL1_PK PRIMARY KEY, COL2 CHAR(5));
...you can immediately issue this query: SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME FROM DBA_INDEXES WHERE INDEX_NAME LIKE
'CON%';
... and get this sort of report: INDEX_NAME -------------------CONTEST_COL1_PK
UNIQUENESS TABLE_NAME ----------------------- -----------UNIQUE CON_TEST
...and you'll notice immediately two key things. First, the index name is the same as the constraint name specified during the "create table" statement (and that's an excellent reason for getting into the habit of naming your constraints properly whenever you define them). And secondly, Oracle created a unique index for us. Both these things happen when you declare unique constraints, too, not just primary key ones. The last fact, that the index was unique, might not be so surprising, if you think about it: after all, the very definition of a primary key is that every entry is unique. And unique key constraints will also be unique (!) -so its blindingly obvious that Oracle will create a unique index. Isn't it? Well, it might be blindingly obvious, (it also happens not always to be true, but more on that later) but it has one unfortunate side effect. If I disable my primary key constraint (perhaps because I am going to do some maintenance work, or a million row insert), I do it like this: ALTER TABLE CON_TEST DISABLE CONSTRAINT CONTEST_COL1_PK;
If I then immediately re-submit my query about the indexes associated with this table, I get this: Copyright © Howard Rogers 2001
10/18/2001
Page 1 of 4
Primary and Unique Key Constraints
Administration Tips
SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME FROM DBA_INDEXES WHERE INDEX_NAME LIKE
'CON%';
NO ROWS SELECTED
In other words, disabling the constraint caused the index to be dropped. This behaviour is potentially appalling. There you are, intending to disable the constraint for two minutes' maintenance work, and woosh! An entire 10Gb index disappears -which means that when I re-enable the constraint, the entire thing has to be rebuilt from scratch, necessitating exclusive table locks until the entire 10Gb edifice is reconstructed (and this has actually happened to me!) So there's the first thing to learn about both primary key and unique key constraints: if enforced with a unique index, the index is dropped when the constraint is disabled, and re-built when re-enabled. Is there anything to be done about this? Well, not in Oracle 7 there isn't. But in Oracle 8.0, the idea of a 'deferred' constraint was introduced. A deferred constraint is one that is not checked as each insert is performed, but is checked when all the inserts have finished, and the User attempts a commit. If at that point, a record that violates the constraint is detected, the complete set of inserts is rolled back. One of the main ideas behind deferring constraints in this way is that it allows bulk loads of data to proceed much faster than if the constraint is "immediate" -there's one mammoth check at the end of the load, rather than 10,000,000 little immediate checks as each row of new data is inserted. But think about it... If we are inserting data which might temporarily violate the constraint, fair enough that Oracle will sort that out when we come to commit. But in the meantime, where does that bit of bad data have to reside? Well, in the table, of course (since data can't just float around in the ether!). So, when the constraint is deferred, records which violate the constraint must be allowed to get into the table (although they'll be kicked out when we commit). But how can a non-unique record reside in the table, however temporarily, if there's a unique index on that table? The answer is it can't -the index will protest at the inclusion of non-unique leaf entries, and will throw an error. So how then can we ever defer a constraint if there is a unique index backing it up, as the example shown above makes clear there will be? Well, the answer is that we can't. If the index is unique, the constraint can not be deferred. But put it the other way around: if the constraint is deferred, the index can not Copyright © Howard Rogers 2001
10/18/2001
Page 2 of 4
Primary and Unique Key Constraints
Administration Tips
be unique! Even more importantly, the determinant is whether the constraint is deferrable, not whether it is actually deferred or not. A deferrable constraint can be 'initially immediate' or 'initially deferred'. It's the fact that it might one day be deferred that is important to Oracle. Therefore, if I issue the following command: CREATE TABLE CONTEST2
(
COL1 NUMBER CONSTRAINT CONTEST2_COL1_PK PRIMARY KEY DEFERRABLE INITIALLY IMMEDIATE, COL2 CHAR(5));
...and then re-run my query for the index details, we see this: INDEX_NAME UNIQUENES TABLE_NAME ------------------------ --------- --------------CONTEST2_COL1_PK NONUNIQUE CONTEST2 ...and we discover that a primary key constraint is now being enforced with a NON-unique index. So what happens when we disable the constraint now? SQL>
ALTER TABLE CONTEST2 DISABLE CONSTRAINT CONTEST2;
TABLE
ALTERED.
SQL> SELECT INDEX_NAME, UNIQUENESS, 2 FROM DBA_INDEXES 3 WHERE INDEX_NAME LIKE 'CON%';
TABLE_NAME
INDEX_NAME UNIQUENES TABLE_NAME ------------------------------ --------- -------------CONTEST2 NONUNIQUE CONTEST2 ...and now you'll notice that the index is retained after the constraint is disabled. Summing all that up, we get a very simple rule: the index associated with a primary or unique key constraint is dropped when the constraint is disabled if it is unique, but is retained if it is non-unique. And that translates into a simple rule of thumb for most DBAs: always make your unique and primary key constraints 'deferrable'. Whether you choose then to make them actually deferred or initially immediate is up to you. But by making them deferrable, you put
Copyright © Howard Rogers 2001
10/18/2001
Page 3 of 4
Primary and Unique Key Constraints
Administration Tips
yourself in control over when indexes are dropped, not let Oracle silently make the decision for you. Before buying into that piece of advice, you might reasonably ask whether the fact that a constraint is using a unique index makes it faster than if it using a non-unique index. Are there any performance penalties in making constraints deferrable? The answer is no. It's just as quick to use a non-unique index as a unique one. That might surprise you, but the reason for it isn't hard to find. Although the index is created as nonunique, its contents will actually be unique, because even when deferrable, the constraint IS enforced at commit time. Any violating records found at that point are rolled back out of the table (and hence out of the index) -so the index contents are indeed going to be unique in practice. The optimiser is smart enough to know this, and therefore it treats a non-unique index which it knows to be enforcing a primary or unique key constraint in exactly the same way as it would a unique index. In effect, it totally ignores the 'uniqueness' attribute of an index when working out how best to answer a query or perform some DML. One final point which many DBAs seem to overlook. The indexes that are created when unique and primary key constraints are defined need to be housed in a tablespace somewhere. In none of the examples I've shown above have I mentioned that: and accordingly, all my indexes would have been created in the default tablespace of the User who was issuing the 'create table' commands. That's not usually what you want: indexes are normally sent to their own tablespace. That needs to be specified when you define the constraint like this: CREATE TABLE CONTEST2
(
COL1 NUMBER CONSTRAINT CONTEST2_COL1_PK PRIMARY KEY DEFERRABLE INITIALLY IMMEDIATE USING INDEX TABLESPACE
INDX01,
COL2 CHAR(5));
The 'using index' clause is the one that does the deed. You can also stick a storage clause in there if you really want to (my advice is never to specify storage clauses at the segment level -that's why there's a tablespace 'default storage' clause!). In summary: primary and unique key constraints should always be declared deferrable, and should always include a 'using index' clause to house the associated index in an appropriate tablespace.
Copyright © Howard Rogers 2001
10/18/2001
Page 4 of 4
Related Documents
Pk Constraint Oracle
May 2020 3
Constraint
November 2019 15
Pk
June 2020 34
Pk
June 2020 34
Budget Constraint
June 2020 3