Может ли тот же столбец иметь ограничение первичного ключа и внешнего ключа на другой столбец

Ответ 1

Не должно быть никаких проблем с этим. Рассмотрим следующий пример:

CREATE TABLE table2 (
   id int PRIMARY KEY,
   name varchar(20)
) ENGINE=INNODB;

CREATE TABLE table1 (
   id int PRIMARY KEY, 
   t2_id int, 
   FOREIGN KEY (t2_id) REFERENCES table2 (id)
) ENGINE=INNODB;

INSERT INTO table2 VALUES (1, 'First Row');
INSERT INTO table2 VALUES (2, 'Second Row');

INSERT INTO table1 VALUES (1, 1);
INSERT INTO table1 VALUES (2, 1);
INSERT INTO table1 VALUES (3, 1);
INSERT INTO table1 VALUES (4, 2);

В таблицах теперь есть:

SELECT * FROM table1;
+----+-------+
| id | t2_id |
+----+-------+
|  1 |     1 |
|  2 |     1 |
|  3 |     1 |
|  4 |     2 |
+----+-------+
4 rows in set (0.00 sec)

SELECT * FROM table2;
+----+------------+
| id | name       |
+----+------------+
|  1 | First Row  |
|  2 | Second Row |
+----+------------+
2 rows in set (0.00 sec)

Теперь мы можем успешно удалить строки следующим образом:

DELETE FROM table1 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)

DELETE FROM table1 WHERE t2_id = 2;
Query OK, 1 row affected (0.00 sec)

Однако мы не сможем удалить следующее:

DELETE FROM table2 WHERE id = 1;
ERROR 1451 (23000): A foreign key constraint fails

Если бы мы определили внешний ключ на table1 с опцией CASCADE, мы смогли бы удалить родителя, и все дети будут автоматически удаляться:

CREATE TABLE table2 (
   id int PRIMARY KEY,
   name varchar(20)
) ENGINE=INNODB;

CREATE TABLE table1 (
   id int PRIMARY KEY, 
   t2_id int, 
   FOREIGN KEY (t2_id) REFERENCES table2 (id) ON DELETE CASCADE
) ENGINE=INNODB;

INSERT INTO table2 VALUES (1, 'First Row');
INSERT INTO table2 VALUES (2, 'Second Row');

INSERT INTO table1 VALUES (1, 1);
INSERT INTO table1 VALUES (2, 1);
INSERT INTO table1 VALUES (3, 1);
INSERT INTO table1 VALUES (4, 2);

Если бы мы повторили ранее неудавшийся DELETE, дочерние строки в table1 будут удалены, а также родительская строка в table2:

DELETE FROM table2 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)

SELECT * FROM table1;
+----+-------+
| id | t2_id |
+----+-------+
|  4 |     2 |
+----+-------+
1 row in set (0.00 sec)

SELECT * FROM table2;
+----+------------+
| id | name       |
+----+------------+
|  2 | Second Row |
+----+------------+
1 row in set (0.00 sec)

Ответ 2

----- Присвоение ключа первичного ключа и внешнего ключа в тот же столбец в таблице --------

create table a1
(
id1 int not null primary key 
)

insert into a1 values(1),(2),(3),(4)


create table a2
(
id1 int not null primary key foreign key references a1(id1)

)
insert into a2 values(1),(2),(3)

Ответ 3

Да, это возможно.

Нет, не будет.

P.S. Но вы не сможете удалить данные таблицы2 без удаления соответствующих строк таблицы.

P.P.S. Я реализовал такую ​​структуру в Postgres, но она должна быть похожа на MySQL.