Page 1 of 2 12 LastLast
Results 1 to 20 of 37

Thread: about sql

  1. #1
    Junior Member
    Join Date
    Jan 2007
    Answers
    23

    about sql

    1.how can i add constraint on existing table?
    2.how can remove constraint from existing table?

    please,reply quickly asap with querry.

    thank you.............


  2. #2
    Expert Member
    Join Date
    Dec 2006
    Answers
    204

    Re: about sql

    use alter table command to do that.

    alter table add constraint const_name .......


  3. #3
    Expert Member
    Join Date
    Sep 2006
    Answers
    130

    Re: about sql

    Hi Aarusa,

    The constraint can be dropped using ALTER TABLE command,
    ALTER TABLE DROP CONSTRAINT

    The above command is the generic command for adding a constraint to an existing table, but one small correction in the previous add constraint command, table_name is missing. Just add table_name to the command & try to add different types of constraints.

    *** Innila ***

    Last edited by Innila; 03-20-2007 at 03:09 AM.

  4. #4
    Junior Member
    Join Date
    Jan 2007
    Answers
    23

    Re: about sql

    hi folks,

    thanks for your reply..........

    but i could not proceed that . i wil tell my clear position.

    i have created one table with two columns (name ,id) without any constraint .now i want to create constraint for any one . already i have used your querry but did not work . the follwing message i got ''by using alter command you can't create or add column with constrint ''.

    please ,very this quickly..................


  5. #5
    Expert Member
    Join Date
    Sep 2006
    Answers
    130

    Re: about sql

    But Aarusa, u havent mentioned which type of constraint u wanted to add, either primary key or foreign key, etc...
    Different Constraints has different alter commands.
    Rather than giving all the commands by myself here, I feel u should understand the constraint types, which will help u a lot.
    The following link gives simple & easy to understand explanation about SQL Server Constraints.
    SQL Server Constraints

    Hopefully it helps u, for further doubts, feel free to ask :-)

    *** Innila ***

    Last edited by Innila; 03-21-2007 at 04:27 AM. Reason: Corrected the URL Format

  6. #6
    Contributing Member
    Join Date
    Jan 2007
    Answers
    31

    Re: about sql

    hi,

    well i will give the example:

    SQL> create table d1( id number(10),
    2 name varchar2(20));

    this will create table d1 with columns id and name without any constraint. now for adding constraint:

    SQL> alter table d1
    2 add constraint id_primary_key primary key (id);

    Table altered.

    this adds the primary key constraint on column id, and constraint name is id_primary_key. for dropping this constraint, use the following:

    SQL> alter table d1 drop constraint id_primary_key;

    Table altered.


    and one more thing, for creating constraints we use ALTER command but for not null we have to use MODIFY. like this:

    SQL> ALTER TABLE d1 MODIFY (id number NOT NULL);

    Table altered.

    and go through, what are table and column level constraint, that will help u. and one more thing never create a constraint without giving them some name. because without any constraint name it will be difficult to drop the constraint.

    cheers,

    RSY


  7. #7
    Junior Member
    Join Date
    Jan 2007
    Answers
    23

    Re: about sql

    Quote Originally Posted by raghav_sy View Post
    hi,

    well i will give the example:

    SQL> create table d1( id number(10),
    2 name varchar2(20));

    this will create table d1 with columns id and name without any constraint. now for adding constraint:

    SQL> alter table d1
    2 add constraint id_primary_key primary key (id);

    Table altered.

    this adds the primary key constraint on column id, and constraint name is id_primary_key. for dropping this constraint, use the following:

    SQL> alter table d1 drop constraint id_primary_key;

    Table altered.


    and one more thing, for creating constraints we use ALTER command but for not null we have to use MODIFY. like this:

    SQL> ALTER TABLE d1 MODIFY (id number NOT NULL);

    Table altered.

    and go through, what are table and column level constraint, that will help u. and one more thing never create a constraint without giving them some name. because without any constraint name it will be difficult to drop the constraint.

    cheers,

    RSY

    --------------

    hi thanks for your reply.......

    again i could not proceed that .

    the same problem occur.

    the error message i got

    ''Cannot define PRIMARY KEY constraint on nullable column in table 'd1'.

    and what is the meaning for 2 on querry?

    and which version you r using myself is 2000.

    i am waiting for your reply................


  8. #8
    Expert Member
    Join Date
    Sep 2006
    Answers
    477

    Re: about sql

    Well, aarusa this is the conceptual error done by you.
    Primary Key Column can not be NULL. That is not allowed in any RDBMS.

    Now use this command to make your column NOT NULL first.
    Code:
    ALTER TABLE table_name
     MODIFY column_name column_type;
    
    Eg:  ALTER TABLE emp
           MODIFY id varchar(25) NOT NULL;
    Then try creating the PRIMARY KEY constraint.

    Cheers!
    Kalayama

    [COLOR="Blue"][SIZE="2"]"If you are not living on the edge of your life, you are wasting space"[/SIZE][/COLOR]

    Someone says "Impossible is nothing". The man next him says "Let me see you licking your elbow tip!"

  9. #9
    Contributing Member
    Join Date
    Jan 2007
    Answers
    31

    Re: about sql

    hi friend,

    do not pay attention to that 2 in the query i wrote, actually i run that command on sql plus and copied from there, so that 2 is the line number. so neglect that 2.

    i think some data is present in the columns on which u want to put primary key constraint and may be some value in that column is a NULL value.check this thing.

    ok
    RSY

    Last edited by raghav_sy; 02-13-2007 at 02:31 AM.

  10. #10
    Junior Member
    Join Date
    Jan 2007
    Answers
    23

    Re: about sql

    hi folks,

    i got answer for that .

    if we have to create constraint on exiting table for that the ecxat column should be not null.this is mondatory. then only we have add constraint.

    but now my question is how to modify one column null to not null?

    because,as a default if we creates the any table it will be nullable.so that we can't do create constraint.

    but if we know about how to modify null to not null we can apply constrint.

    and my request is that your querryu about modify did n't work..........

    please,update your view.............?


  11. #11
    Expert Member
    Join Date
    Sep 2006
    Answers
    477

    Re: about sql

    Well aarusa, please chek my previous post in this thread. I have already told the problem as well as the solution.
    Well, aarusa this is the conceptual error done by you.
    Primary Key Column can not be NULL. That is not allowed in any RDBMS.

    Now use this command to make your column NOT NULL first.
    Code:
    ALTER TABLE table_name
    MODIFY column_name column_type;

    Eg: ALTER TABLE emp
    MODIFY id varchar(25) NOT NULL;
    Then try creating the PRIMARY KEY constraint.

    Cheers!
    Kalayama

    Moreover, it was a very nice link posted by mangaivarma which explained the basics of constraints. Kindly read ALL the replies for your question and then if your question is still unanswered, only then post your new question.
    -Kalayama

    Last edited by kalayama; 02-13-2007 at 02:49 AM.
    [COLOR="Blue"][SIZE="2"]"If you are not living on the edge of your life, you are wasting space"[/SIZE][/COLOR]

    Someone says "Impossible is nothing". The man next him says "Let me see you licking your elbow tip!"

  12. #12
    Junior Member
    Join Date
    Jan 2007
    Answers
    23

    Re: about sql

    hi

    i got some idea but the modify querry not working well .when use that i got the follwing message.

    guerry, ''alter table modify name varchar(20) not null''
    error message, ''Incorrect syntax near 'varchar'.''


  13. #13
    Contributing Member
    Join Date
    Jan 2007
    Answers
    31

    Re: about sql

    hi friend,

    that is not the case, the null values in columns, create problem depending upon the type of constraint we are going to create.

    if u are creating unique or check constraint on the column then null value in that case will not affect.

    u faced the problem because there was a null value in the column and tried to create prmary key on that. so i will tell one thing,

    if we create a primary key, it will automatically has a NOT NULL and unique constraint, so that NOT NULL feature of primary key was creating the problem.

    now abt that modify query, it works, which quey i send in replies, first i test them then only i post them.

    for u its not working because a NULL value is already present in that column. so u have to delete that value. then u can use the modify command. then it will work.

    and coming to NOT NULL constraint, remember its a column level constraint.

    ok
    cheers,
    RSY


  14. #14
    Expert Member
    Join Date
    Sep 2006
    Answers
    477

    Re: about sql

    guerry, ''alter table modify name varchar(20) not null''
    Well, your query is wrong because you haven't specified the table name there. The problem is because of that.

    try like,
    alter table table_name modify name varchar(20) not null

    It will have to work.

    -Kalayama

    [COLOR="Blue"][SIZE="2"]"If you are not living on the edge of your life, you are wasting space"[/SIZE][/COLOR]

    Someone says "Impossible is nothing". The man next him says "Let me see you licking your elbow tip!"

  15. #15
    Expert Member
    Join Date
    Sep 2006
    Answers
    130

    Re: about sql

    Aarusa, the table name is missing in the above command, i think u've missed while posting.
    u can use the command mentioned by Kalayama.
    u can also try the Alter table command with or without the datatype,
    ALTER TABLE MODIFY NOT NULL
    This will modify the column constraint.

    I have another doubt, have u inserted any null value(s) in the table.

    *** Innila ***

    Last edited by Innila; 03-21-2007 at 04:28 AM.

  16. #16
    Contributing Member
    Join Date
    Jan 2007
    Answers
    31

    Re: about sql

    hi,

    the data type will not have any affect on query, both will work.

    the only problem is that, in the query table name is missing. and even after using table name, the query is failing then please check the column values, it should not have any NULL values.

    RSY


  17. #17
    Junior Member
    Join Date
    Jan 2007
    Answers
    23

    Re: about sql

    hi ragav

    thanks for your reply,

    but my table haven't any null values.

    but i could not proceed ,..............

    please one time your sellf and then give solution ..............

    because i am trying a lot of way..............

    please...........if is it possible please...........


  18. #18
    Expert Member
    Join Date
    Sep 2006
    Answers
    477

    Re: about sql

    Dude! Please check the other posts too dude! Check my previous post and mangaivarma's post.

    Your previous ALTER TABLE MODIFY command was WRONG.

    Your question is already answered. Please check the previous posts in this thread!

    -Kalayama

    [COLOR="Blue"][SIZE="2"]"If you are not living on the edge of your life, you are wasting space"[/SIZE][/COLOR]

    Someone says "Impossible is nothing". The man next him says "Let me see you licking your elbow tip!"

  19. #19
    Junior Member
    Join Date
    Jan 2007
    Answers
    23

    Re: about sql

    hi

    thank for your reply.........

    i have only one data in this table .then where is possible to be null?

    i confused about that.

    if i use your querry i got the following message........

    q : ''alter table b modify name not null''
    m : ''Line 1: Incorrect syntax near 'name'.''


  20. #20
    Contributing Member
    Join Date
    Jan 2007
    Answers
    31

    Re: about sql

    hi friend,

    i do not know why u are getting the error, i did a lot of experiment but not able to get your error. for me all the queries are working.

    its very difficult to understand where u have done the mistake. its better u create new table with specific constraints and then insert your data. drop the old table, and then rename the new table to old table name.

    i know its not a good idea, but in real time there is not so much of time, and we have to work smarter rather then working hard without any ouput.

    sorry for giving a bad suggestion, but sometimes its the reality.

    Cheers,
    RSY


Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact