Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jun 2004
    Posts
    127

    Unanswered: Novice SQL questions

    hi, please can you help me, [an sql novice] to answer these [simple] questions:

    can i index a view?

    can a view have a trigger or procedure attached to it?

    can a view have a PK or FK?

    can a structured type (ADT or datatype) have an index, or trigger, or procedure, or PK, FK?
    -----------------------------------------------
    OR can indexes, unique constraints, PK's, FK's, procedures, triggers, only BE ON TABLES?

    so hence a table owns these features : (indexes, unique constraints, PK's, FK's, procedures, triggers)

    thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by FAC51
    hi, please can you help me, [an sql novice] to answer these [simple] questions:

    can i index a view?

    can a view have a trigger or procedure attached to it?

    can a view have a PK or FK?

    can a structured type (ADT or datatype) have an index, or trigger, or procedure, or PK, FK?
    -----------------------------------------------
    OR can indexes, unique constraints, PK's, FK's, procedures, triggers, only BE ON TABLES?

    so hence a table owns these features : (indexes, unique constraints, PK's, FK's, procedures, triggers)

    thanks.
    Some database engines can index a view.

    Some database engines can have triggers on views. Most (all?) of the database engines that support triggers and stored procedures can reference views within them.

    It depends on what you mean: views can contain PKs or FKs.

    Some (most) database engines will support indexes and constraints on ADTs. Yes, they can only be on tables.

    A PK can only be on a table, but depending on how the view is constructed that may be effectively the same thing as having a PK on the view.

    Yes, a table can have those features.

    -PatP

  3. #3
    Join Date
    Jun 2004
    Posts
    127
    hi pat, ty for reply.

    just one more Q:

    must every SQL based table constructed contain/have assigned a PK?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by FAC51

    must every SQL based table constructed contain/have assigned a PK?
    Must you stop for a red light at a busy intersection? No, not if you don't want to, but it's a really good idea.

    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jun 2004
    Posts
    127
    is it poss to build the table without PK, and without a uniqueconstraint also?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by FAC51
    is it poss to build the table without PK, and without a uniqueconstraint also?
    With most database engines, yes.

    -PatP

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by FAC51
    is it poss to build the table without PK, and without a uniqueconstraint also?
    .......................
    Quote Originally Posted by Teddy
    Must you stop for a red light at a busy intersection? No, not if you don't want to, but it's a really good idea.

    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    teddy, are you familiar with the "three cans of cat food" problem?

    Quote Originally Posted by Joe Celko
    The rationale for allowing duplicate rows was best defined by David Beech in an internal paper for the ANSI X3H2 Committee and again in a letter to DATAMATION ("New Life for SQL," February 1989). This is now referred to as the "cat food argument" in the literature. The name is taken from the example of a cash register slip, where you find several rows, each of which lists a can of cat food at the same price. To quote from the original article:

    "For example, the row 'cat food 0.39' could appear three times [on a supermarket checkout receipt] with a significance that would not escape many shoppers...At the level of abstraction at which it is useful to record the information, there are no value components that distinguish the objects. What the relational model does is force people to lower the level of abstraction, often inventing meaningless values to be inserted in an extra column whose purpose is to show what we knew already, that the cans of cat food are distinct."

    All cans of cat food are interchangeable, so they have no natural unique identifier. The alternative of tagging every single can of cat food in the database with a unique machine-readable identifier which is pre-printed on the can or keyed in at the register is not only expensive and time-consuming, but it adds no real information to the data model. In the real world, you collect the data as it comes in on the cash register slip, and consolidate it when you debit the count of cans of cat food in the inventory table. The cans of cat food are considered equivalent, but they are not identical.

    -- http://www.orafaq.net/usenet/comp.da...02/24/0187.htm
    see also this older thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2004
    Posts
    127
    One final Q [pls remeber that i am an absolute novice]. can i implement a forign key on a col that has no PK or unique constraint placed on it? can any DBMS accomodate this?

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by FAC51
    One final Q [pls remeber that i am an absolute novice]. can i implement a forign key on a col that has no PK or unique constraint placed on it? can any DBMS accomodate this?
    Sure, a column in a table can be an FK without being part of that table's PK, and FK columns are rarely unique. I can't think of a relational database that doesn't support foreign keys.

    -PatP

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mysql (myisam tables, not inndb tables) do not support foreign keys

    now, i suppose you're gonna come back with "but mysql isn't a relational database" but you will get an argument on that



    i think fac51's question was, can a foreign key reference a column in another table that isn't a pk or has a unique constraint, and the answer is no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jun 2004
    Posts
    127
    Pat, when you responded to my prior questions with these answers:

    *Some database engines can index a view.

    *Some database engines can have triggers on views. Most (all?) of the database engines that support triggers and stored procedures can reference views within them.

    *Some (most) database engines will support indexes and constraints on ADTs. Yes, they can only be on tables.

    Were you refering that they all can be achieved through using SQL?

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    ...and the answer is no
    In MySQL.

    -PatP

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by FAC51
    Were you refering that they all can be achieved through using SQL?
    That's true, but I was trying to answer on a much larger scale than the implementation details.

    -PatP

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    In MySQL.
    pat, are you sure you read what i wrote carefully enough?

    "can a foreign key reference a column in another table that isn't a pk or has a unique constraint"

    the answer is no in sql server, too
    Code:
    create table Pat1
    ( id smallint not null primary key identity
    , name varchar(9) not null
    , constraint youneeknames unique ( name )
    , shoesize varchar(9) null
    )
    insert into Pat1 ( name, shoesize ) values ( 'curly' , '10E'  ) 
    insert into Pat1 ( name, shoesize ) values ( 'larry' , '9'    ) 
    insert into Pat1 ( name, shoesize ) values ( 'moe'   , '9' ) 
    
    create table Pat2
    ( shoe smallint primary key identity
    , stoogesize varchar(9)
    , foreign key ( stoogesize ) references Pat1 ( shoesize )
    ) 
    
    Error: There are no primary or candidate keys in the referenced
    table 'Pat1' that match the referencing column list in the foreign key 
    'FK__Pat2__stoogesize__6324A15E'. (State:37000, Native Code: 6F0)
    Error: Could not create constraint. See previous errors. 
    (State:37000, Native Code: 6D6)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •