Oracle员工约束(Oracle Employee constraint)

我还是SQL的新手，我有点卡住了。这是我的脚本：

CREATE TABLE Employee (SSN NUMBER NOT NULL, FNAME VARCHAR(8) , MINIT VARCHAR(1) , LNAME VARCHAR(7) , BDATE DATE , SEX VARCHAR(1) , SALARY NUMBER(8,2) , SUPERSSN NUMBER , DNO NUMBER(2) , CONSTRAINT Employee_PK PRIMARY KEY (SSN), CONSTRAINT Employee_FK1 FOREIGN KEY (SUPERSSN) REFERENCES Employee(SSN)); INSERT INTO Employee (SSN, FNAME, MINIT, LNAME, BDATE, SEX, SALARY, SUPERSSN, DNO) VALUES (123456789, 'John', 'B', 'Smith', '09-JAN-55', 'M', '30000', '333445555', '5'); COMMIT; DESC Employee

正如你所看到的，我试图为SUPERSSN> SSN创建一个约束，但它始终给我违反了完整性约束 - 找不到父键当我删除CONSTRAINT Employee_FK1 FOREIGN KEY（SUPERSSN）REFERENCES Employee（SSN）时，它完美地工作。

但是，如果我保留它并将插入删除到员工脚本中，它也可以正常工作。所以我不确定我做错了什么。

我使用Oracle。

I'm still new in SQL and I'm kinda stuck. Here's my script :

As you can see, I'm trying to create a constraint for SUPERSSN > SSN but it always gives me integrity constraint violated - parent key not found When i remove CONSTRAINT Employee_FK1 FOREIGN KEY (SUPERSSN) REFERENCES Employee(SSN), it works perfectly.

But, if I keep it and remove the insert into employee script instead, it also works fine. So I'm not sure what I'm doing wrong.

I use Oracle.

最满意答案

当你想在列上添加数据时，你必须确保它的父键是数据，例如表SUPERSSN不包含SSN 123456789。

在SSN = 123456789插入一个rescord，它会起作用。

我会给你一个关于2个表格的例子。

餐桌FoodMarket包含Id和employer

表sandwich包含列Id ， Name和DateCreated

现在从逻辑上说，如果市场上不存在三明治你是不是也没有，对吗？所以我们在这个表上创建一个列ID的外键

alter table Sandwish add CONSTRAINT fk_column FOREIGN KEY (id) REFERENCES FoodMarket(id)

所以如果你想插入一个新的三明治，比如说汉堡包，它的ID应该存在于市场上，如果你在三明治表中插入一个id = 10，但是这个值在市场上不存在，它会提高错误，你应该在market上添加第一个 id 10，让你在表三明治中插入id =10 。

When you want to add data on column that its a foreign key you have to make sure it is parent has data, for example the table SUPERSSN doesn't contain SSN 123456789 .

Insert a rescord where SSN = 123456789 and it will work.

I'll give you an example about 2 tables.

table FoodMarket contain Id and employer

And table sandwich contains column Id , Name and DateCreated

Now logically you cannot have a sandwich if it doesn't exist in the market, correct ? So we create on this table a foreign key on one column id

alter table Sandwish add CONSTRAINT fk_column FOREIGN KEY (id) REFERENCES FoodMarket(id)

So if you want to insert a new sandwich, let's say hamburger, its id should exist in the market, if you insert in the sandwich table an in id = 10 but this value = 10 doesn't exist in the market, it will raise error, you should add first id 10 in the market to let you insert id =10 in table sandwiches.

更多推荐