Sql (Oracle)-- cannot insert value(could be the constraint factor)

oracle direct-path insert example
can foreign key be null in mysql
oracle 12c direct path insert

As you can see in the code. three table have its own primary key. "protectmedalno" and "mastermedalno" are the foreign key of the player table. protectmedalno could not be null. masterdealno could be null. I drop table protector first, then drop master , the last drop player.

There is weak relationship between table player and table master. There is no problem with inserting the value of protector and master. But inserting the value into table player, it will occur: *Cause: A foreign key value has no matching primary key value. *Action: Delete the foreign key or add a matching primary key.

I think that it is a problem with constraint.

insert into player values('01','Joe','101','');
insert into player values('02','Elsa','102','201');

insert into protector values('101','Dragon');
insert into protector values('102','Lion');
insert into master values('201','Fairy')
commits;

It could display the protector table and the master table. But it could not show the player table.

drop table protector;
drop table master;
drop table player;

CREATE TABLE player (
    playno           NUMBER(2) NOT NULL,
    playname         VARCHAR2(30) NOT NULL,
    protectmedalno   CHAR(10) NOT NULL,
    mastermedalno    CHAR(10)
);


ALTER TABLE player ADD CONSTRAINT play_pk PRIMARY KEY ( playno );

CREATE TABLE protector (
    protectmedalno   CHAR(3) NOT NULL,
    protectname      VARCHAR2(30) NOT NULL   
);


ALTER TABLE protector ADD CONSTRAINT protector_pk PRIMARY KEY ( protectmedalno );

CREATE TABLE master (
    mastermedalno   CHAR(3) NOT NULL,
    mastername      VARCHAR2(30) NOT NULL   
);

ALTER TABLE master ADD CONSTRAINT master_pk PRIMARY KEY ( mastermedalno );


ALTER TABLE player
    ADD CONSTRAINT player_protector_fk FOREIGN KEY ( protectmedalno )
        REFERENCES protector ( protectmedalno );

ALTER TABLE player
    ADD CONSTRAINT player_master_fk FOREIGN KEY ( mastermedalno )
        REFERENCES master ( mastermedalno );

Since protector and master are the primary tables, you should populate the records there first. Then, insert into player and refer to those records:

insert into protector values('101','Dragon');
insert into protector values('102','Lion');
insert into master values('201','Fairy');

insert into player values('01','Joe','101','201');   -- refer to master
insert into player values('02','Elsa','102','201');  -- refer to master

Note that I edited the inserts into the player table such that both records refer to a record in the master table which actually exists.

Cannot insert null values to a column defined not - Ask Tom, But getting error ORA=01400 Cannot insert null values. If you explicitly state null, then we will respect that instruction. SQL> create table tmp_test( 2 column1 varchar2(10) default 1 not null, 3 column2 number(10) default 2 not null); Table if we give dafault value as NULL for a column which has Not Null constraint on it ? Oracle SQL tutorials, How to ADD CONSTRAINT FOREIGN KEY, INSERT INTO statement

You first have to insert protector and master, afterwards insert player since player refers to master and protector and values have to be inside there.

Do otherwise round on delete...

insert into protector values('101','Dragon');
insert into protector values('102','Lion');

insert into master values('201','Fairy');

insert into player values('01','Joe','101','');
insert into player values('02','Elsa','102','201');

If you delete first delete from player, then from protector and master.

Oracle Database 12c Performance Tuning Recipes: A Problem-Solution , Tables with certain constraints may not be eligible. Distributed transactions cannot be parallelized. Checks to see whether a hint is specified on INSERT, UPDATE, MERGE, or DELETE statements 2. Here are some other factors that need to be considered 546 Chapter 15 □ exeCuting SQL in paraLLeL Restrictions on  In the year 2042 we can do this as follows: SQL> create or replace assertion sum_pct_is_100 as 2 check(not exists 3 (select 'a violation' 4 from (select p.agreement_id, sum(p.participation_pct) as sum_pct 5 from participant p 6 group by p.agreement_id) x 7 where x.sum_pct <> 100)) 8 deferrable initially deferred 9 / Assertion created.

To insert data in the player table, you need a record in the protector table. This is because of the foreign key restriction. When inserting data in a table that has a foreign key(Which in this case of protector vs player, cannot be null), you have to create the foreign record first.

 1. insert into protector values('101','Dragon');
    2. insert into player values('01','Joe','101','');
    3. insert into protector values('102','Lion');
    4. insert into master values('201','Fairy');
    5. insert into player values('02','Elsa','102','201');
    commits;

I hope this helps, happy debugging :)

FOREIGN KEY Constraints and NULL Values, You can define constraints to enforce business rules on data in your tables. Business Try to insert NULL into DEPARTMENT_ID column: Default values cannot include expressions that refer to a sequence, PL/SQL function, column, LEVEL  Create Check Constraints. 06/28/2017; 2 minutes to read; In this article. APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse You can create a check constraint in a table to specify the data values that are acceptable in one or more columns in SQL Server 2019 (15.x) by using SQL Server Management Studio or Transact-SQL.

You are inserting in the wrong order: you must insert the master and protector first so that when you insert the player it can reference them:

insert into protector values('101','Dragon');
insert into protector values('102','Lion');
insert into master values('201','Fairy');

insert into player values('01','Joe','101',NULL);
insert into player values('02','Elsa','102','201');

Edit: '' is not NULL it's an empty String. To insert null use the explicit NULL jeyword.

Data Integrity, Index Range Scan · Index Unique Scan · Index Skip Scan · Index Clustering Factor SQL> INSERT INTO hr.employees (employee_id, last_name) values (​999, You can only add a column with a NOT NULL constraint if the table does not unique key constraints on multiple columns, you cannot have identical values in  Here it is. We use dbms_lock to serialize access to the table (necessary for a multi-user environment to avoid overlaps by 2 people concurrently in your case above -- IF there are other columns in the table, we might be able to serialize on a much lower level of granularity).

Always include the columns when doing an insert. You should also use single quotes for only string and date constants.

insert into player(playno, playname, protectmedalno, mastermedalno) 
     values(1, 'Joe', '101', '');

I don't think the problem is specifically on player, but you should do this for all your inserts and you'll find the problem.

Conventional and Direct Path Loads, A conventional path load executes SQL INSERT statements to populate tables in an In both conventional path and direct path, you can specify a text value for a Because these constraints cannot be applied to rows loaded on the direct To calculate the amount of storage needed, use a sort factor of 1.0 instead of 1.3. As you can see in the code. three table have its own primary key. "protectmedalno" and "mastermedalno" are the foreign key of the player table. protectmedalno could not be null. masterdealno could be

Defining, Constraining, and Manipulating Your Entities, SQL> alter table employee_example rename constraint SYS_C0010551 to So beware: a NULL value for SALARY that's inserted into the table will be accepted, even ERROR at line 1: ORA-01400: cannot insert NULL into ("SQL_201". Oracle assigns this value to the column if a subsequent INSERT statement omits a value for the column. If you are adding a new column to the table and specify the default value, Oracle inserts the default column value into all rows of the table. The datatype of the default value must match the datatype specified for the column.

CONSTRAINT clause, Table-level CHECK constraints can refer to 0 or more columns in the table. Specifies that this column cannot hold NULL values (constraints of this type are not Note: If you attempt to add a primary key using ALTER TABLE and any of the See "Using SQL standard authorization" and "Privileges on views, triggers, and  Hello I'm a student and let me do the following, I have to do an update, I would like to salary min is between 2000 and 30000, how to do it? Thanks, I hope you can help me.

Unique key across tables - Ask Tom, How can i enforce unique key across multiple tables. Is it possible to restrict, while inserting and updating into these tables, the union of ID values from you cannot. period. nothing preventing someone from using s.currval and s.nextval. psub add constraint psub_pk primary key (id) disable novalidate; Tom: I will read your chapter 7 to clear up some issues on indexes this weekend But let me confirm with you. I have DEPT table with 10 records and EMP table with 1 million records.

Comments
  • try to insert protector and master values before player ones
  • still appear the same error.
  • I think this is cause you are inserting '' in the column of mastermedalno in the player table and it doesn't match with anythiing, try to write null there.
  • Joe do not have a master. I tried to insert the order like what you said , the condition the same. Should I clear the table?or how to clear it ?
  • If you can, just drop everything, create the tables again, then use the correct insertion order. Not sure if Oracle allows NULL foreign keys, but I think the missing foreign key should be NULL, not empty string.
  • I also tried null at the beginning without using the order suggested. null seems could not be used.
  • @YeiBi Foreign keys in Oracle can be NULL, so there must be another problem.