1 2 Previous Next 20 Replies Latest reply: Apr 25, 2012 10:23 AM by rp0428 RSS

    Question about constraints

    878451
      Hello there guys, i´m studying to try a OCA certification, practing about 300 questions of SQL.
      I have this question that i don´t understood very well. Please, someone help me with this issue:


      "which constraint can be define only at the column level?"
      a- unique
      b- not null
      c- check
      d- primary key
      e- foreign key

      I already know the answer, but i would like to know why.

      Someone could answer and explain it to me ?

      thanks
        • 1. Re: Question about constraints
          hitgon
          Hi,

          not null
          check

          NOT NULL is constrain which you can define on column Level so while insert the values inside the table that time that constrain will take care that
          specified column values must not be NULL because we had defined the NOT NULL Constrain on that column

          Please refer
          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8906942258938

          Regards
          Hitgon

          Edited by: hitgon on Apr 25, 2012 7:20 PM

          Edited by: hitgon on Apr 25, 2012 7:21 PM
          • 2. Re: Question about constraints
            878451
            The answer is not null.

            But i don´t understood why not null if defined at column level? show it to me.

            Why check, unique, pk and fk aren´t defined in column level ?
            • 3. Re: Question about constraints
              hitgon
              Hi,

              Because check, unique, pk and fk constrain can create on table level not the column level

              You need to understand the purpose of pk,fk and unique and check constrain the same.

              Regards
              Hitgon
              • 4. Re: Question about constraints
                Mark Malakanov (user11181920)
                Not Null constraint refers one column only.
                Other constraints can refer multiple columns.
                • 5. Re: Question about constraints
                  878451
                  I understand.

                  Your explain is not working. sorry =/.


                  Why unique isn´t defined at column level?
                  ALTER TABLE tbl ADD CONSTRAINT Uqcolumn UNIQUE (column)

                  Why Check isn´t defined at column level ?
                  ALTER TABLE Persons
                  ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

                  Why Pk isn´t defined at column level ?
                  ALTER TABLE nome_tabela ADD CONSTRAINT name_constraint PRIMARY KEY (name_column)

                  Why FK isn´t define at column level?
                  ALTER TABLE distrib ADD CONSTRAINT fk_dist FOREIGN KEY (address) REFERENCES addresses(address)
                  • 6. Re: Question about constraints
                    878451
                    Hi, Thanks for answering.
                    Could you Gimme a example ?
                    • 7. Re: Question about constraints
                      Fran
                      Please check:
                      http://docs.oracle.com/javadb/10.6.1.0/ref/rrefsqlj13590.html
                      • 8. Re: Question about constraints
                        878451
                        Well, this explain the same thing that hitgon told. Still don´t got it very well.
                        That means that i could define only one not null constraint at table ? Well looks like isn´t this, i tested already, and i may create 2 not null columns in the same table.

                        Edited by: BrunoSales on 25/04/2012 07:39
                        • 9. Re: Question about constraints
                          Mark Malakanov (user11181920)
                          That means that i could define only one not null constraint at table ?
                          you can create many not null constraints at table.

                          you cannot create one not null constraint for multiple columns.
                          • 10. Re: Question about constraints
                            878451
                            Hum, good. Starts to clear my vision.
                            Some more questions: Can i create a unique constraint for multiple columns ?

                            "you cannot create one not null constraint for multiple columns." This means column level constraints?

                            thanks
                            • 11. Re: Question about constraints
                              932876
                               That means that i could define only one not null constraint at table ? 
                              Not like that, you can define NOT NULL at column level only.

                              and your original question was
                               "which constraint can be define only at the column level?" 
                              i.e., you can define other constraints PK, FK, UNIQUE at column level as well as row level. you can use multiple columns to define these constraints. but you cannot use multiple columns to define NOT NULL.
                              • 12. Re: Question about constraints
                                P.Forstmann
                                The question is not very clear.

                                What is clear is that you can use ALTER TABLE for each constraint type:
                                select * from v$version;
                                
                                BANNER
                                --------------------------------------------------------------------------------
                                Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
                                PL/SQL Release 11.2.0.2.0 - Production
                                CORE    11.2.0.2.0      Production
                                TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
                                NLSRTL Version 11.2.0.2.0 - Production
                                
                                drop table t1 purge;
                                
                                Table dropped.
                                
                                drop table t2 purge;
                                
                                Table dropped.
                                
                                --
                                create table t2(y int);
                                
                                Table created.
                                
                                create table t1(x int);
                                
                                Table created.
                                
                                alter table t1 modify (x not null);
                                
                                Table altered.
                                
                                alter table t1 modify (x check(x > 0));
                                
                                Table altered.
                                
                                alter table t1 modify (x unique);
                                
                                Table altered.
                                
                                alter table t2 modify (y primary key);
                                
                                Table altered.
                                
                                alter table t1 modify (x references t2);
                                
                                Table altered.
                                • 13. Re: Question about constraints
                                  878451
                                  Sure, the question isn´t clear.

                                  The thing is i dunno why 'NOT NULL' is defined at column level. I was thinking that could be the one constraint that would create by using alter table, but any constraint could be created by using alter command, i´ve tested.

                                  user11181920 told me that "you cannot create one not null constraint for multiple columns." And i would like to know, if this is the main reason for Not Null being the one constraint defined at column level.
                                  • 14. Re: Question about constraints
                                    Fran
                                    It's a bit hard to explain but I will try:

                                    A primary key uniquely identifies rows in a table.
                                    A unique key defines a set of columns that uniquely identify rows in a table, same like primary key but can be NOT NULL.
                                    A Foreign key is a column or group of columns within a table that references a key in some other table.
                                    A check constraint specify a range of a table.
                                    A not null key don't let hold NULL values.

                                    up to here, i think you have all clear.
                                    That means that i could define only one not null constraint at table ? 
                                    No, you can have several columns with null values:
                                    SQL> create table example (id number(1), name varchar2(25), age number(2), sex char(1));
                                    Tabla creada.
                                    SQL> alter table hr.example modify name not null;
                                    Tabla modificada.
                                    SQL> alter table hr.example modify age not null;
                                    Tabla modificada.

                                    PK, FK, Unique and check can refers to a several columns but always identified a row. Not null can't identified a row or several columns.

                                    I don't know if I explain it clearly. If not please, told me and i will try to explain it in other way.
                                    1 2 Previous Next