如何在sql中创建外键 [英] how to create foreign key in sql
本文介绍了如何在sql中创建外键的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在创建两个表employee并且artist.employee表创建成功,但是在创建带有外键的艺术家表时出错。
i写了以下查询:
创建 表员工
(
employeeId int 不 null ,
employeeUsername varchar ( 50 )不 null ,
employeePassword varchar ( 50 )不 null ,
employeeName varchar ( 50 )不 null ,
employeeContact int 不 null ,
employeeEmail varchar ( 30 )不 null ,
primary key (employeeId)
);
创建 table 艺术家
(
artistId < span class =code-keyword> int 不 null ,
artistUsername varchar ( 50 )不 null ,
artistPassword varchar ( 50 ) 不 null ,
artistName varchar ( 50 )不 null ,
artistContactno int 不 null ,
artistAddress varchar ( 50 )不 null ,
artistEmail varchar ( 50 )不 null ,
artistDob datetime 不 null ,
artistRegdate datetime 不 null ,
primary key (artistId),
employee_employeeId int 参考员工
);
i收到错误:
外键'employee'在引用表'artist'时引用了无效的列'employee'。
解决方案
创建 表员工
(
employeeId int < span class =code-keyword> not null ,
employeeUsername varchar ( 50 )不 null ,
employeePassword varchar ( 50 )不 null ,
employeeName varchar ( 50 )不 null ,
employeeContact int 不 null ,
employeeEmail varchar ( 30 )不 null ,
约束 PK_Employee PRIMARY KEY (employeeId)
)
创建 表格艺术家
{
artistId INT < span class =code-keyword> NOT NULL CONSTRAINT FK_artist_artistId
< span class =code-keyword> FOREIGN KEY REFERENCES 员工(employeeId),< span class =code-comment> - ON DELETE CASCADE如果你想
artistUsername varchar ( 50 )不 null ,
a rtistPassword varchar ( 50 )不 null ,
artistName varchar ( 50 )不 null ,
artistContactno int 不 null ,
artistAddress varchar ( 50 )不 null ,
artistEmail varchar ( 50 )不 null ,
artistDob datetime not not null ,
artistRegdate datetime 不 null
) ;
制表
创建 表员工
(
employeeId < span class =code-keyword> int 不 null ,
employeeUsername varchar ( 50 )不 null ,
employeePassword varchar ( 50 ) 不 null ,
employeeName varchar ( 50 )不 null ,
employeeContact int 不 null ,
employeeEmail varchar ( 30 )不 null ,
primary key (employeeId)
);
创建 table 艺术家
(
artistId < span class =code-keyword> int 不 null ,
artistUsername varchar ( 50 )不 null ,
artistPassword varchar ( 50 ) 不 null ,
artistName varchar ( 50 )不 null ,
artistContactno int 不 null ,
artistAddress varchar ( 50 )不 null ,
artistEmail varchar ( 50 )不 null ,
artistDob datetime 不 null ,
artistRegdate datetime 不 null ,
主要 密钥(artistId),
employee_employeeId int 不 null 约束 FK_art_emp_Id
FOREIGN KEY REFERENCES 员工(empl oyeeId)
);
代码块已更正,制表
试一试
CREATE TABLE上的SQL FOREIGN KEY约束
I am creating two tables employee and artist.employee table is created successfully but i am getting error while creating artist table with foreign key.
i have written following query:
create table employee
(
employeeId int not null,
employeeUsername varchar(50) not null,
employeePassword varchar(50) not null,
employeeName varchar(50) not null,
employeeContact int not null,
employeeEmail varchar(30) not null,
primary key(employeeId)
);
Create table artist
(
artistId int not null,
artistUsername varchar(50) not null,
artistPassword varchar(50) not null,
artistName varchar(50) not null,
artistContactno int not null,
artistAddress varchar(50) not null,
artistEmail varchar(50) not null,
artistDob datetime not null,
artistRegdate datetime not null,
primary key (artistId),
employee_employeeId int references employee
);
i am getting error as:
Foreign key 'employee' references invalid column 'employee' in referencing table 'artist'.
解决方案
create table employee ( employeeId int not null, employeeUsername varchar(50) not null, employeePassword varchar(50) not null, employeeName varchar(50) not null, employeeContact int not null, employeeEmail varchar(30) not null, Constraint PK_Employee PRIMARY KEY(employeeId) ) Create table artist { artistId INT NOT NULL CONSTRAINT FK_artist_artistId FOREIGN KEY REFERENCES Employee(employeeId), --ON DELETE CASCADE this is optional if you want to artistUsername varchar(50) not null, artistPassword varchar(50) not null, artistName varchar(50) not null, artistContactno int not null, artistAddress varchar(50) not null, artistEmail varchar(50) not null, artistDob datetime not null, artistRegdate datetime not null );
tabulation
create table employee ( employeeId int not null, employeeUsername varchar(50) not null, employeePassword varchar(50) not null, employeeName varchar(50) not null, employeeContact int not null, employeeEmail varchar(30) not null, primary key(employeeId) ); Create table artist ( artistId int not null, artistUsername varchar(50) not null, artistPassword varchar(50) not null, artistName varchar(50) not null, artistContactno int not null, artistAddress varchar(50) not null, artistEmail varchar(50) not null, artistDob datetime not null, artistRegdate datetime not null, primary key (artistId), employee_employeeId int not null constraint FK_art_emp_Id FOREIGN KEY REFERENCES Employee(employeeId) );
Code block corrected, tabulation
try it
SQL FOREIGN KEY Constraint on CREATE TABLE
这篇关于如何在sql中创建外键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文