Foreign Key Problem in SQL

I just wanna ask coz I created a table with a column with two primary keys as shown below:

CREATE TABLE Parent(
Mother varchar(50) NOT NULL,
Father varchar(50) NOT NULL,
PRIMARY KEY(Mother, Father),
Organization varchar(50) NOT NULL
);

Then I am trying to create another table which will have a foreign key to the table shown above:

CREATE TABLE Child(
Name varchar(50) NOT NULL PRIMARY KEY,
Child_Mother varchar(50) REFERENCES Parent(Mother),
Child_Father varchar(50) REFERENCES Parent(Father),
Sibling varchar(50) NOT NULL
);

So I basically am trying to references two columns on the Child Table to a single column from the Parent table with two primary keys. Is this even possible? Thank you very much! :)

*Sample Tables开发者_如何学编程 only. But the actual are similar to this.

You haven't created a "table with a column with two primary keys", you've created one with a single composite primary key.

And what you are doing is indeed possible but not in the way you are doing it.

The problem is that you can have two Parent rows with the same Mother and different Father. But your foreign key relationship on Child_Mother will need a single instance of Mother in the Parent table otherwise it won't know which row it's referring to.

You can either have a combined column in the child which references the conbined primary key as you have it, or you can separate the Parent rows.

By that, I mean a parent table wouldn't normally put two people in a single row, it would normally have one row per person and the child would simply reference two separate rows in the Parent table, one for the mother and one for the father.

In terms of your actual tables:

create table Subject(
    Subject_ID varchar(50) NOT NULL,
    Subject_Description varchar(50) NOT NULL,
    PRIMARY KEY(Subject_ID, Subject_Description),
    Subject_SID int references Student(Student_ID),
    Unit varchar(50) NOT NULL
)

This is not actually normalised since the subject description almost invariable depends on the subject ID. I think the primary key should probably just be on subject ID. By all means have another index on subject description but it shouldn't be part of the primary key.

I'm still not sure that you understand what I'm saying so I'll try to clarify. The line:

PRIMARY KEY(Subject_ID, Subject_Description)

does not make two primary keys, it makes a single primary key made up of the two columns (id and description). Because of that, it is possible to have two rows with the same ID provided that the description is different.

So a foreign key reference to the ID column is not possible. Let's say you had two rows:

ID  Subject  Other
--  -------  ---------
 7  Maths    blah blah
 7  Physics  yada yada

and then tried to add to the schedule table a row with Schedule_SID set to 7. That will choke because the DBMS wouldn't know which 7 you're referring to. So it shouldn't even let you set up that foreign key constraint because the target column isn't unique.

Try this one -

CREATE TABLE Child(
    Name varchar(50) NOT NULL PRIMARY KEY,
    Child_Mother varchar(50),
    Child_Father varchar(50),
    Sibling varchar(50) NOT NULL,
    Foreign Key (Child_Mother, Child_Father) references Parent(Mother, Father)
);

Since you are creating a primary key which consists of two columns, you need to refer to them together.

What paxdiablo said is true. You are creating a composite primary key which consists of two columns. I would suggest you to use a Surrogate Primary Key.

Edit

CREATE TABLE Child(
    id bigint NOT NULL auto_increment,  // an "id" column
    Name varchar(50) NOT NULL unique,
    age int not null,
    primary key (id)    // "id" column is being declared as surrogate primary key
);

Second Edit

create table Subject(

    id int not null,
    Subject_ID varchar(50) NOT NULL,
    Subject_Description varchar(50) NOT NULL,
    PRIMARY KEY(id),
    Subject_SID int references Student(Student_ID),
    Unit varchar(50) NOT NULL
);

ALTER TABLE Subject ADD CONSTRAINT subject_unique UNIQUE (Subject_id, Subject_Description);

create table Schedule(
    Day_MTWTHF varchar(50) NOT NULL,
    TIME_HH varchar(50) NOT NULL,
    subject_id int not null,                   -- my newly added column
    Schedule_SID int references Student(Student_ID),
    foreign key(subject_id) references Subject(id)
);

I'd say your choice of primary key is poor for the Parent table. It looks like you're using names. What happens if you've got two seperate couples whose names match up? John & Jane Smith with Alice and Bob for children, under your design, would also be the same John & Jane Smith from the next town over who've got Charlie and Dolores for kids, even though in reality they're completely unrelated.

Names are invariably a bad choice for keys, as names can be repeated. Using something guaranteed to be unique, such as an auto-incrementing integer, would be far safer, and would allow you to keep the J&J Smith from Springfield seperate from the J&J Smith from Shelbyville.