"Difference between Table level and Column level constraints?", version 10.0.0
senthil raj, July 28, 2010 - 3:22 pm UTC
Here goes my question in depth....I have a table X and colmns A,B and C............... suppose if I have table level constraint like Unique constraint on A and B.. and there comes a update for a value in the C column.. in this situation how ur table elvel constraint works.????
July 29, 2010 - 8:39 am UTC
huh??
you have a unique constraint on (a,b)
so what does C have to do with anything???
"Difference between Table level and Column level constraints?", version 10.0.0
senthil raj, July 28, 2010 - 3:26 pm UTC
Why not null constraint cannot be applied at table?? and why its getting displayed when we say DESC <TABLE_NAME>
July 29, 2010 - 8:41 am UTC
because a NOT NULL constraint can only apply to one column - there is no meaning to it in any context BEYOND a column. Therefore, since you should always use a column constraint if the constraint applies just to the column (and NOT a table constraint if it only applies to a single column) - you can only do it at the column level.
Because doing it at the table level would not provide any additional functionality, it would only be wrong to do.
?
Oleksandr Alesinskyy, July 29, 2010 - 12:50 pm UTC
Is not NOT NULL constraint on the column col essentially equivalent to CHECK(col IS NOT NULL)?
BTW dictionary views represent NOT NULL constraint as check constraints.
July 29, 2010 - 1:08 pm UTC
internally a NOT NULL is recognized as "not null" - a flag. The optimizer uses this.
Currently "x int check (x is not null)" is not the same as "x int not null".
The check constraint will not be recognized as a NOT NULL constraint.
To Oleksandr Alesinskyy
Michel Cadot, July 29, 2010 - 1:13 pm UTC
Is USER_CONSTRAINTS buggy?
Oleksandr Alesinskyy, July 30, 2010 - 5:21 am UTC
It means that USER_CONSTRAINTS may be misleading:
SQL>
SQL> col search_condition form a16
SQL> col table_name form a9
SQL> col constraint_name form a15
SQL> col owner form a5
SQL>
SQL> drop table TST;
Table dropped.
SQL>
SQL> create table TST (x number not null,y number check ("Y" IS NOT NULL));
Table created.
SQL>
SQL> select
2 owner, constraint_name, constraint_type, table_name,
3 search_condition, status, deferrable, deferred, validated,
4 generated, bad, rely, last_change from USER_CONSTRAINTS uc
5 where table_name='TST';
OWNER CONSTRAINT_NAME C TABLE_NAM SEARCH_CONDITION STATUS DEFERRABLE DEFERRED VALIDATED GEN
----- --------------- - --------- ---------------- -------- -------------- --------- ------------- -
MUMU SYS_C00843334 C TST "X" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERA
MUMU SYS_C00843335 C TST "Y" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERA
SQL>
SQL> select dbms_metadata.get_ddl('TABLE','TST') from dual;
DBMS_METADATA.GET_DDL('TABLE','TST')
--------------------------------------------------------------------------------
CREATE TABLE "MUMU"."TST"
( "X" NUMBER NOT NULL ENABLE,
"Y" NUMBER,
CHECK ("Y" IS NOT NULL) ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
SQL>
As you can see select from USER_CONSTRAINT shows constraints on x and y identically (save names). While DBMS_METADATA properly distinguish them.
August 02, 2010 - 8:03 am UTC
I don't consider this to be a bug - you'd have to take this data into consideration along with user_tab_columns.nullable.
Oleksandr Alesinskyy, August 03, 2010 - 8:19 am UTC
IMHO it is at least a documentation bug Reference says for ALL_CONSTRAINTS (USER_CONSTRAINT description refers to ALL_CONSTRAINTS):
C
CONSTRAINT_TYPE VARCHAR2(1) Type of the constraint definition:
* C - Check constraint on a table
* P - Primary key
* U - Unique key
* R - Referential integrity
* V - With check option, on a view
* O - With read only, on a view
* H - Hash expression
* F - Constraint that involves a REF column
* S - Supplemental logging
So it does not mention NOT NULL in any way.
The quote is from 11.2 but 10.2 does not differ much (just few types less). BTW, what for the god sake has an supplemental logging (or hash expression for that matter) to do with constrains?
A reader, December 26, 2012 - 6:54 am UTC
which is most powerful in both table and column constraints
January 04, 2013 - 10:59 am UTC
huh???
shyam, September 24, 2013 - 8:27 am UTC