Novice SQL questions

FAC51 · #1 (**permalink**) 01-21-05, 07:21

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.

Pat Phelan · #2 (**permalink**) 01-21-05, 08:33

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

FAC51 · #3 (**permalink**) 01-21-05, 10:22

hi pat, ty for reply.

just one more Q:

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

Teddy · #4 (**permalink**) 01-21-05, 10:28

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.

FAC51 · #5 (**permalink**) 01-21-05, 10:42

is it poss to build the table without PK, and without a uniqueconstraint also?

Pat Phelan · #6 (**permalink**) 01-21-05, 10:44

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

Teddy · #7 (**permalink**) 01-21-05, 10:46

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.

r937 · #8 (**permalink**) 01-21-05, 12:45

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

FAC51 · #9 (**permalink**) 01-24-05, 08:36

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?

Pat Phelan · #10 (**permalink**) 01-24-05, 08:43

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

r937 · #11 (**permalink**) 01-24-05, 08:46

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

FAC51 · #12 (**permalink**) 01-24-05, 10:19

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?

Pat Phelan · #13 (**permalink**) 01-24-05, 11:06

Quote:

Originally Posted by r937

...and the answer is no

In MySQL.

-PatP

Pat Phelan · #14 (**permalink**) 01-24-05, 11:08

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

r937 · #15 (**permalink**) 01-24-05, 11:45

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)

Internet Services

Web Development

Digital Marketing

Consumer Tech