CODEWITHSUNDEEP
sqlforeign-keys
Wasiq Kashkari
Wasiq Kashkari

Reputation: 61

Foreign key on same table

I have a db schema that includes 

Group{name, group.id, parent.id} with key {group.id}

In this schema all parent.id's must either already exist in the group.id column or be null. How can i translate this constraint into SQL while creating the table? Thanks

Upvotes: 2

Views: 80

Answers (1)

user743382
user743382

Reputation:

A regular foreign key should suffice. It won't perform any checks if the field is null. The precise syntax may depend slightly on the SQL dialect, but it would look something like

create table Group_ (
    name varchar(30) not null,
    groupid int not null primary key,
    parentid int null foreign key references Group_ (groupid) )

Upvotes: 2

Related Questions