CODEWITHSUNDEEP
sqldatabaseoracle-database
Taurus22
Taurus22

Reputation: 89

How can I create a table that only allows data to insert if they are allowed

How can i create a table, that allows only to put data in NAME, if the data matches with the data that i want to be allowed in NAME. So like Bla1 or Bla2.

CREATE TABLE Table1 (
    NAME VARCHAR(23)
    NAME has to be one of them: ('Bla1', 'Bla2')
)

Upvotes: 0

Views: 253

Answers (2)

Erik
Erik

Reputation: 3636

The best way to do it is probably to have a second table with all the allowed names in it, and making a FOREIGN KEY from the name field in your Table1 to the name field in that other table. That'll automatically fail any insert queries for which the name is not contained in the list of allowed names.

This has an advantage over things like ENUM and such in that it does not require you to rebuild your table (which is a very expensive operation) every time you want to allow another name and it also allows you to later add additional related info to each name by adding it to the other table.

Here's a great article on why using a foreign key is much better than using enums or other such checks in the table itself: http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/

Upvotes: 5

Jens
Jens

Reputation: 69470

Try this:

CREATE TABLE Table1 (
  name VARCHAR(23) CHECK( name IN ('Bla1','Bla2') )
);

Upvotes: 1

Related Questions