Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

PL / SQL design with refs

  • 22-06-2011 4:04pm
    #1
    Call Me Jimmy
    Registered Users, Registered Users 2 Posts: 8,449 ✭✭✭


    Hi guys, I'm doing a project in pl sql and it's all about object orientated approaches to databases.

    My problem is in understanding the use of REFs.
    In plain sql ye'd have tables where you define the columns and give primary and foreign keys for relating between data.

    That's fine. But in pl /sql when I create an object type I can't put in primary or foreign keys directly into the object.

    So the way I'm approaching it is to have seperate objects (i'm modelling a book store) like Book and Author and Publisher (each in their own type table). So naturally I assumed that the object types for Book and Author might look like this: 
    create or replace type book_t as object
(
	isbn	int,		--note can't define it as a primary key here
	title	varchar2(30),
	author	ref author_t

	...etc
);

create or replace type author_t as object
(
	author_id	int;
	name		name_t;
	books_written	varray; -- havent got this far but it would be references to all books written by the author
);

    What I don't understand is if I insert a new book type into a table of books and the author that it references in the authors table isn't there, how do I create a new book?

    I've tried some procedural stuff that inserts the first object while selecting the author to reference from the authors table and if the reference is null after that to print out 'author not found' but the code seems overly complicated and if the author isn't in the list I would have to then manually insert the author and get the reference just as part of creating the book.

    I know it maybe isn't a popular language so I'd really appreciate if anyone can shed some light on REFs and how to manage them. If you need any extra information or expansion I'll post it.

    Thanks a lot


Advertisement