Oracle constraint implementation

-- Constraint (primary key, non-null, unique, check, foreign key, default)
-- Create constraints when creating a table
-- Method 1: Write the constraint directly behind the field
Create Table student
(
Sno int primary key, -- primary key
Sname varchar2 (20) not null, -- not empty
Sex varchar2 (2) Check (sex in ('male', 'female '), -- check (sex = 'male' or sex = 'female '),
Address varchar2 (20) default 'changsha Hunan ', -- default Constraint
Cardid varchar2 (20) Unique not null -- unique
)

Drop table student;

-- Method 2: Write all fields before writing Constraints
Create Table Test
(
Sno int,
Sname varchar2 (20 ),
Constraint pk_test primary key (SNO)
)

Drop table test;

-- Foreign key constraint
-- (When creating a table in Oracle and creating a foreign key constraint at the same time, if you write the constraint directly to the end of a single field, you do not need to add a foreign key)
-- (When creating a table in Oracle and creating a foreign key constraint at the same time, if you write the constraint directly to the end of all fields, you need to add the foreign key)
Create Table score
(
Sno int references student (SNO ),
CNO int,
Grade float,
Constraint pk_score primary key (SNO, CNO ),
Constraint fk_student_score foreign key (CNO) References course (CNO)
)

-- Delete a table
Drop table score;

Create Table Course
(
CNO int primary key, -- course number
Cname varchar2 (20) -- Course name
)
Drop table course;

-- Writing Method in SQL Server
/* Create Table TT1
(
Sno varchar (3) Foreign key references student (SNO ),
AA varchar (20)
)

Create Table TT2
(
Sno varchar (3 ),
AA varchar (20 ),
Constraint fk_student_tt2 foreign key (SNO) References student (SNO)
)*/

-- Add constraints after creating the table
Create Table student
(

Sno int,
Sname varchar2 (20 ),
Sex varchar2 (2 ),
Address varchar2 (20 ),
Cardid varchar2 (20)
)
-- First add the primary key constraint
Alter table student
Add constraint pk_student_sno primary key (SNO)

-- Delete Constraints
Alter table student
Drop constraint pk_student_sno

-- Not null
Alter table student
Modify (sname varchar2 (30) not null)

-- Check Constraints
Alter table student
Add constraint ck_student_sex check (sex = 'male' or sex = 'female ')

-- Default Constraint
Alter table student
Modify (address varchar2 (20) default 'hunan software evaluation Center ')

-- Unique constraint
Alter table student
Add constraint uq_student_cardid unique (cardid)

-- Foreign key constraint
Alter table score
Add constraint fk_student_score foreign key (SNO) References student (SNO)

Select 12*54