在SQL中检查外键,这样正确吗?
在SQL中检查外键,这样正确吗?
我只需要你的一点帮助,你能不能帮我检查一下我在SQL Server中是否正确地创建了外键?我对SQL还是新手,所以对这个问题表示抱歉。如果一切都正确,我可以开始填充数据了吗?
--创建员工表
CREATE TABLE EMPLOYEE(
[ID] INT IDENTITY(1,1) NOT NULL,
[FIRST_NAME] NVARCHAR(25) NOT NULL,
[LAST_NAME] NVARCHAR(25) NOT NULL,
[EMAIL] nvarchar(60) NOT NULL CONSTRAINT UQ_EMPLOYEE_email UNIQUE,
[PHARMACY_ID] INT NOT NULL,
CONSTRAINT PK_EMPLOYEE_ID PRIMARY KEY (ID));
--创建药房表
CREATE TABLE PHARMACY (
[ID] INT IDENTITY(1, 1) NOT NULL,
[NAME] NVARCHAR(25),
[ADDRESS] NVARCHAR(25) NULL,
[PHONE] nvarchar(24) NULL CHECK(PHONE like '(___)-__-___ __ __'),
[EID] [INT] NOT NULL
CONSTRAINT PK_PHARMACY_ID PRIMARY KEY (ID));
--创建供应商表
CREATE TABLE SUPPLIERS(
[ID] INT IDENTITY(1,1) NOT NULL,
[NAME] NVARCHAR(25),
[ADDRESS] NVARCHAR(25) NULL,
[DRUGSID] [INT] NOT NULL,
CONSTRAINT PK_SUPPLIERS_ID PRIMARY KEY (ID));
--创建药品表
CREATE TABLE DRUGS(
[ID] INT IDENTITY(1,1) NOT NULL,
[NAME] NVARCHAR(25),
[QUANTITY] INT NULL,
[ORDERID] [INT] NULL,
[SUPPLIERID] [INT] NULL,
CONSTRAINT PK_DRUGS_ID PRIMARY KEY(ID));
--创建订单表
CREATE TABLE ORDERS (
[ID] INT IDENTITY(1,1) NOT NULL,
[ONAME] NVARCHAR(25) NOT NULL,
[ODATE] DATETIME,
[OQUANTITY] INT NOT NULL,
[SUPPLIERID] [INT] NOT NULL,
[DRUGID] [INT] NOT NULL,
CONSTRAINT PK_ORDERS_ID PRIMARY KEY(ID));
--创建经理表
CREATE TABLE MANAGER (
[PASSPORTNO] INT IDENTITY(1,1) NOT NULL,
[HOURLYPAY] DECIMAL(5,2) NULL,
[LANGUAGE_SKILLS] NVARCHAR(25) NULL
CONSTRAINT PK_MANAGER_NO PRIMARY KEY (PASSPORTNO));
--创建销售助理表
CREATE TABLE SALES_ASSISTANT (
[PASSPORTNO] INT IDENTITY(1,1) NOT NULL,
[SALARY] DECIMAL(5,2) NULL
CONSTRAINT PK_SALES_ASSISTANT_NO PRIMARY KEY (PASSPORTNO));
--创建外键
ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_PHARMACYID FOREIGN KEY(PHARMACY_ID)
REFERENCES PHARMACY(ID) ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE PHARMACY ADD CONSTRAINT FK_EID FOREIGN KEY(EID)
REFERENCES EMPLOYEE(ID) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE SUPPLIERS ADD CONSTRAINT FK_DRUGID FOREIGN KEY(DRUGSID)
REFERENCES DRUGS(ID) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE DRUGS ADD CONSTRAINT FK_ORDERID FOREIGN KEY(ORDERID)
REFERENCES ORDERS(ID) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE DRUGS ADD CONSTRAINT FK_SUPPLIERS FOREIGN KEY(SUPPLIERID)
REFERENCES SUPPLIERS(ID) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE ORDERS ADD CONSTRAINT FK_SUPPLIERS_OR FOREIGN KEY(SUPPLIERID)
REFERENCES SUPPLIERS(ID) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE ORDERS ADD CONSTRAINT FK_DRUGID_OR FOREIGN KEY(DRUGID)
REFERENCES DRUGS(ID) ON DELETE NO ACTION ON UPDATE NO ACTION
检查SQL中的外键,是不是正确?
在开发数据库应用程序时,经常需要创建外键来确保数据完整性。然而,有时候我们可能会犯一些错误,比如忘记创建外键或者错误地创建了外键。为了检查我们设置的外键是否正确,我们可以使用下面的SQL查询语句来查询:
SELECT KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME ,KCU1.TABLE_NAME AS FK_TABLE_NAME ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
除了检查外键的正确性,我们还应该注意以下几点:
1. 在创建脚本时,确保可以多次运行。所以在创建外键之前,应该先删除已有的外键,或者仅在外键不存在时才创建。同样,对于表也是一样的,不要在表中包含数据的情况下删除表。
2. 在创建外键之前,需要检查所有引用的表是否存在,并且检查引用表是否设置了主键。
3. 如果只需要日期而不需要时间,可以使用`date`类型代替`datetime`类型。
通过以上几点的注意,我们可以确保外键的正确性和数据的完整性。
这是如何在SQL中检查外键的方法,但是数据设计似乎不正确。您不能让一个订单指向一个药物,同时药物也指向一个订单。请阅读关于第三范式(3NF)的资料。一个供应商不只有一种药物。虽然DrugsID可能有一个"S",但它仍然是单数形式。这个数据设计是不正确的。
问题的原因是数据设计不正确,具体表现为订单和药物之间存在双向的外键关系,而实际上应该是单向的。解决方法是重新设计数据结构,遵循第三范式的原则,将订单和药物之间建立正确的关联关系。
以下是重新设计的数据结构示例:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE,
SupplierID INT,
-- Other columns
FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID)
);
CREATE TABLE Drugs (
DrugID INT PRIMARY KEY,
DrugName VARCHAR(255),
-- Other columns
);
CREATE TABLE OrderDrugs (
OrderID INT,
DrugID INT,
Quantity INT,
-- Other columns
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (DrugID) REFERENCES Drugs(DrugID)
);
通过重新设计,订单和药物之间建立了正确的一对多关系,通过OrderDrugs表来连接订单和药物,并且使用单向的外键关系来确保数据的完整性和一致性。这样,就可以正确地检查和操作外键了。