Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Unanswered: No unique or primary key error

    Please help me in this problem i've been trying but unable to solve

    Code:
    create table student(regno varchar(10) primary key,name varchar(15),major varchar(6),bdate date);
    Code:
    create table course(courseno int primary key,cname varchar(10),dept varchar(7));
    Code:
    create table enroll(regno varchar(10),courseno int,sem int,primary key(regno,courseno,sem),mar
    ks int,foreign key (regno) references student(regno),foreign key (courseno) references course(course
    no));
    Code:
    create table text(book_isbn int,book_title varchar(10),publisher varchar(10),author varchar(10
    ));
    Code:
    create table book_adoption(courseno int,sem int,book_isbn int,primary key(courseno,sem,book_is
    bn),foreign key (courseno) references course(courseno),foreign key (sem) references enroll(sem),foreign key (book_isbn) references book_adopt
    ion(book_isbn));
    When i try to create the book_adoption table i get the error.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    create table book_adoption
    (courseno int
    ,sem int
    ,book_isbn int
    ,primary key(courseno,sem,book_isbn)
    ,foreign key (courseno) references course(courseno)
    ,foreign key (sem) references enroll(sem)
    ,foreign key (book_isbn) references book_adoption(book_isbn));
    the part marked in red is invalid because enroll(sem) is not a primary or unique key

    similarly, the part marked in blue is invalid because book_adoption(book_isbn) is not a primary or unique key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2011
    Posts
    2
    the part marked in red is invalid because enroll(sem) is not a primary or unique key

    similarly, the part marked in blue is invalid because book_adoption(book_isbn) is not a primary or unique key
    I have actually done that.If you see the enroll table i have mentioned sem as primary key.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Shynash21 View Post
    If you see the enroll table i have mentioned sem as a primary key
    you have mentioned sem as one of the columns of the primary key

    there is only one primary key, and it is a composite key consisting of three columns

    a foreign key reference must match the entire key -- either a primary key or a unique key -- and not just part of it
    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
  •