Enforce a Foreign-Key Constraint to Columns of Same Table

Enforce a foreign-key constraint to columns of same table

CREATE TABLE TABLE_NAME (
    `empid_number`    int     (  11) NOT NULL auto_increment,   
    `employee`        varchar ( 100) NOT NULL               ,
    `manager_number`  int     (  11) NOT NULL               ,
     PRIMARY KEY  (`empid_number`),
     CONSTRAINT `manager_references_employee`
     FOREIGN KEY (`manager_number`) REFERENCES (`empid_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Hope it helps!

Foreign Key referencing Primary key in the same table

I know of no reason why this, specifically, could provide a benefit - so I'm going to go with option 2 - a mistake.

Of course, if it was different columns in the same table, that would make sense, and as @Jignesh.Raj points out, that would form some kind of hierarchy.

You can even sometimes end up with multiple hierarchies within the same table with such multi-column references:

CREATE TABLE T (
   GroupID int not null,
   ItemID int not null,
   ParentItemID int null,
   constraint PK_T PRIMARY KEY (GroupID,ItemID),
   constraint FK_T_Parent FOREIGN KEY (GroupID,ParentItemID) references T (GroupID,ItemID)
)

In the above, the GroupID column always references itself.

But as I say, with your current table, where both columns only reference themselves, it makes no sense.

Two columns in same table and same foreign key

This SO post implies that removing your ON DELETE RESTRICT clause from the ALTER TABLE statement might solve your problem. Try running this query instead:

ALTER TABLE `invoices`
ADD FOREIGN KEY (`created_by`) REFERENCES `secureap_maind`.`users`(`id`)

I am assuming that the invoices table was created using InnoDB rather than MyISAM, the latter which does not enforce foreign keys.

Can a foreign key refer to a primary key in the same table?

I think the question is a bit confusing.

If you mean "can foreign key 'refer' to a primary key in the same table?", the answer is a firm yes as some replied. For example, in an employee table, a row for an employee may have a column for storing manager's employee number where the manager is also an employee and hence will have a row in the table like a row of any other employee.

If you mean "can column(or set of columns) be a primary key as well as a foreign key in the same table?", the answer, in my view, is a no; it seems meaningless. However, the following definition succeeds in SQL Server!

create table t1(c1 int not null primary key foreign key references t1(c1))

But I think it is meaningless to have such a constraint unless somebody comes up with a practical example.

AmanS, in your example d_id in no circumstance can be a primary key in Employee table. A table can have only one primary key. I hope this clears your doubt. d_id is/can be a primary key only in department table.

Is it possible to reference one column as multiple foreign keys?

No.

That is, you cannot create a foreign key constraint this way. You can however, use a foreign key without a foreign key constraint.

All a foreign key is, is the value of another table's (or another record in the same table) primary key, which can be used in joins. In fact, you could reference fields other than the primary key, if all you need is to use the value for joins.

However, a foreign key constraint tells the database to enforce the rule that for every foreign key value in a table, the referenced table has a record with that as it's primary key. Enforcing that every foreign key in the PDF table had a primary key IN ALL FOUR TABLES won't work for you. So go ahead and use the field to reference other records, but simply do not create any foreign key constraint.

---

Related Topics