| Author |
Topic |
|
sd
Starting Member
6 Posts |
 Posted - 2009-03-03 : 13:53:06
|
| Hi,I am a bit in doubt.Can foreign key in a table be on the same column as the primary key of the table.eg. 'Profile' table has ProfileID as primary key. 'ProfileDesc' table has ProfileID as primary key.Now,is the following correct in ProfileDesc table. CONSTRAINT `fk_ProfileDesc_Profile` FOREIGN KEY (`ProfileID` ) REFERENCES Profile(`ProfileID` )Please let me know the reason too.Thanks,Sd |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-03-03 : 14:35:33
|
| You can do it, but there should rarely be a reason to do it, since it implies a 1 to 1 or 0 relationship. If the only thing it has is a description, then you could do the same thing by just adding the description to the parent table as a nullable column.CODO ERGO SUM |
 |
|
|
sd
Starting Member
6 Posts |
Posted - 2009-03-03 : 19:21:50
|
| Thank you for your replies.I wanted to know the best practice.Is it good to have the primary key as the foreign key.Actually I am creating a database from the design provided to me.If it is not the best practice,I can ask them to make a change on it at this stage.So before I go forward with the database,I wanted to know the best practice of foreign key.Thanks,Sd |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sd
Starting Member
6 Posts |
Posted - 2009-03-03 : 20:28:59
|
| Hi Tara,Below are the DDLs of the two tables.I have not mentioned the other columns as they are not related to these tables and will make the DDL very long.CREATE TABLE Profile(ProfileNumber INT NOT NULL PRIMARY KEY,ProfileID CHAR(12) NULL ,EMailAddress1 VARCHAR(45) NOT NULL ,Gender CHAR(1) NULL ,Age INT NULL ,CONSTRAINT fk_Profile_ProfileDescFOREIGN KEY (ProfileNumber)REFERENCES ProfileDesc(ProfileNumber)ON DELETE NO ACTIONON UPDATE NO ACTION);CREATE TABLE ProfileDesc (ProfileNumber INT NOT NULL PRIMARY KEY,FirstName VARCHAR(20) NULL ,LastName VARCHAR(20) NULL);Here,Parent table is ProfileDesc and child table is Profile.This is just a smaller version of the real tables.I have created small tables with these two scripts I provided here in my test database to check.It has created everything successfully.But I want to know how is it helpful/not helpful in creating the foreign key on primary key of the same table.Thanks & Regards,Sd |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-03-03 : 22:36:31
|
| You posted the FK constraint two different ways, one with Profile as the parent and the second is with ProfileDesc as the parent. You at least need to decide which is which; they can't both be the parent.Either way, it would probably be simpler to make them both one table, since they both have the same primary key. It would just be a matter of making the non-required columns nullable.CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sd
Starting Member
6 Posts |
Posted - 2009-03-04 : 00:46:55
|
| Thank you Michael and Tara.i guess I will ask them if we can have only 1 table.I made these tables in my post shorter by giving only three four fields,but there are many more.Regards,Sd |
|
|
|
|
Foreign Key and Primary Key on the same Column